Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 23, 2010 at 03:24 PM

    Hello Florian,

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

    I have not performed any compression operations on Oracle 11g R2 with brspace until yet .. but this error seems to be very obvious.

    It seems like SAP is still not using the procedure DBMS_REDEFINITION.COPY_TABLE_DEPENDENT to create the indexes (and NOT NULL constraints) on Oracle 11g R2. No idea why, i can only think of one case (create a DDL file before reorganisation to change the DDL parameters through the reorganisation in some kind of ways).

    So in your case it seems like SAP is creating a wrong SQL for creating the index on the interim table.

    You can try to create the DDL file first and correct the parameters and after that you can try to run the reorganisation again.

    Please check sapnote #646681 (Remark 5) for more information about the procedure for "creating the DDL first .. and then do the reorg with edited parameters).

    Regards

    Stefan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 24, 2010 at 08:54 AM

    Hi,

    Is 6 mandatory or can you leave it blank so it will use a default.

    Checking the error on the Oracle website it has to do with the DDL where it has INSTANCES 0, where it is suppose to be an integer greater than zero. Would be interesting to know what default it will use.

    Can you login to the database and post the result of running the command below:

    show parameter instance

    Add comment
    10|10000 characters needed characters exceeded