Skip to Content
0
Former Member
Jun 23, 2010 at 02:31 PM

ORA-12815 while reorg/compression of tables without LONG and LOB with 11g

109 Views

Hello fellows,

I am in the luxury situation that I got a copy of our production R/3 environment that was left over from a project and is no more required by any of our developers.

As we are still on oracle 9.2.0.7 I upgraded this copy to 11.2 in a two step process (from 9i to 10g to 11g).

I got myself the SAP dbatools 7.20(3) and the Note 1431296 - LOB conversion and table compression with BRSPACE 7.20.

I started with some small tablespaces but after a while I thought I'd like to try to reorg/compress the worst of all tablespaces...PSAPPOOLD with ~15.000 tables.

I first converted tables with LONG fields online that can be compressed, than the onse that can not be compressed, than I reorged the tables that contain old LOB fields online. With these different executions of the brspace commands that are also mentioned in the above note I managed to move ~ 3.000 tables without any issues.

But now I started with the biggest bunch of tables, the compression of tables without LONG and LOB fields online.

This is the command I used:

brspace -u / -p reorgEXCL.tab -f tbreorg -a reorg -o sapr3 -s PSAPPOOLD -t allsel -n psapreorg -i psapreorgi -c ctab -SCT

...after a few checks that are performed by brspace, I end up in the screen

Options for reorganization of tables (which is still nothing I wouldn't have expected)

1 * Reorganization action (action) ............ [reorg]

2 - Reorganization mode (mode) ................ [online]

3 - Create DDL statements (ddl) ............... [yes]

4 ~ New destination tablespace (newts) ........ [PSAPREORG]

5 ~ Separate index tablespace (indts) ......... [PSAPREORGI]

6 - Parallel threads (parallel) ............... [1]

7 ~ Table/index parallel degree (degree) ...... []

8 ~ Category of initial extent size (initial) . []

9 ~ Sort by fields of index (sortind) ......... []

10 # Index for IOT conversion (iotind) ......... [FIRST]

11 - Compression action (compress) ............. [none]

12 # LOB compression degree (lobcompr) ......... [medium]

13 # Index compression method (indcompr) ....... [ora_proc]

But independent of what I enter in point 6 and 7, I always end up with below erros during the reorg/compression of the outstanding tables:

Just one sample, but the issue is always the same.

BR0301E SQL error -12815 in thread 2 at location tab_onl_reorg-26, SQL statement:

'CREATE UNIQUE INDEX "SAPR3"."RTXTF_____0#$" ON "SAPR3"."RTXTF#$" ("MANDT", "APPLCLASS", "TEXT_NAME", "TEXT_TYPE", "FROM_LINE",

"FROM_POS")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 1662976 NEXT 655360 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "PSAPREORGI" PARALLEL ( INSTANCES 0) '

ORA-12815: value for INSTANCES must be greater than 0

Just in case, here it the OBJECT DDL:

CREATE UNIQUE INDEX "SAPR3"."RTXTF_____0"

ON "SAPR3"."RTXTF" ("MANDT", "APPLCLASS", "TEXT_NAME",

"TEXT_TYPE", "FROM_LINE", "FROM_POS")

TABLESPACE "PSAPPOOLI" PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE ( INITIAL 1624K NEXT 640K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)

LOGGING

Perhaps someone already gained some experience on the compression with brspace and can give me a hint.

Many thanks

Florian