ORA-14460 when doing reorganization and compression

[former_member315656]

Hello all,

I'm doing a tablespace reorganization with activating Oracle compression like it is described in SAP note 1431296 and 646681.

First of all I created the new tablespace with the compression flag enabled.

7 - SQL command (command) ................ [create tablespace PSAPTAB_NEUD extent management l

ocal autoallocate segment space management auto default compress for oltp datafile 'M:\oracle\R

3Q\sapdata2tab_neud_1\tab_neud.data1' size 10000M autoextend on next 10M maxsize 32767M]

After that i started reorganization, first lob2lob convertion and compression:

brspace -u / -f tbreorg -a lob2lob -o sapr3 -s PSAPSTABD -t allsel -n PSAPTAB_NEUD -i PSAPTAB_NEUI -c ctablobind -lc medium  -p 10 -e 10
BR0280I BRSPACE time stamp: 2012-04-19 07.59.23
BR1112I Number of tables selected/skipped for reorganization: 140/19604

BR0301E SQL error -14460 in thread 6 at location tab_onl_reorg-19, SQL statement:
'CREATE TABLE "SAPR3"."CUEX#$"
   (    "MANDT" VARCHAR2(3) DEFAULT '000' NOT NULL ENABLE,
        "KNNUM" VARCHAR2(10) DEFAULT '0000000000' NOT NULL ENABLE,
        "ADZHL" VARCHAR2(4) DEFAULT '0000' NOT NULL ENABLE,
        "KNOBJ" VARCHAR2(18) DEFAULT '000000000000000000' NOT NULL ENABLE,
        "KNCNT" VARCHAR2(4) DEFAULT '0000' NOT NULL ENABLE,
        "KNCOM" VARCHAR2(1) DEFAULT ' ' NOT NULL ENABLE,
        "LKENZ" VARCHAR2(1) DEFAULT ' ' NOT NULL ENABLE,
        "AENNR" VARCHAR2(12) DEFAULT ' ' NOT NULL ENABLE,
        "DATUV" VARCHAR2(8) DEFAULT '00000000' NOT NULL ENABLE,
        "KNBKL" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
        "KNBLK" CLOB NOT NULL ENABLE
   )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP LOGGING
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSAPTAB_NEUD"
LOB ("KNBLK") STORE AS SECUREFILE (
  TABLESPACE "PSAPTAB_NEUD" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
  CACHE COMPRESS MEDIUM '
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified

BR0280I BRSPACE time stamp: 2012-04-19 08.02.23
BR1102I Number of tables reorganized successfully: 130
BR1103E Reorganization failed for 10 table(s)

Can anyone explain me the error message above?

Thanks and Regards,

Wolfgang

[Former Member]

Hi Wolfgang,

Did you downloaded latest brtools on SAP Marketplace?

Best regards,

Orkun Gedik

[former_member315656]

Hi Orkun,

I use brtools 7.20 pl22, I have seen pl 23 is available.

Regards,

wolfgang

[Former Member]

Hi,

Could you execute the SQL statement, manually on the test system?

The problem caused by both "COMPRESS FOR OLTP LOGGING" and "CACHE COMPRESS MEDIUM" statements executed in the same SQL statement.

Let's investigate the issue step by step.

Best regards,

Orkun Gedik

[former_member315656]

Hi,

I have executed the following statemant:

CREATE TABLE "SAPR3"."VTEST10#$"

   (    "FELD1" VARCHAR2(1) DEFAULT ' ' NOT NULL ENABLE,

        "FELD2" VARCHAR2(4) DEFAULT '0000' NOT NULL ENABLE,

        "PAGENO" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

        "TIMESTMP" VARCHAR2(46) DEFAULT ' ' NOT NULL ENABLE,

        "PAGELG" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

        "VARDATA" BLOB NOT NULL ENABLE

   )

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "PSAPCLU_NEUD"

LOB ("VARDATA") STORE AS SECUREFILE (

  TABLESPACE "PSAPCLU_NEUD" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION

  NOCACHE LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE

DEFAULT CELL_FLASH_CACHE DEFAULT))

  CACHE COMPRESS MEDIUM

SQL> @x1

  CACHE COMPRESS MEDIUM

        *

ERROR at line 18:

ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified

After that I tried the statemant without 'CACHE COMPRESS MEDIUM' - and it worked!

I have created the tablespace PSAPCLU_NEUD with compression enabled!

In the above sql-statemant will be table compression and LOB compression enabled in one step.

The whole command is:

brspace -u / -f tbreorg -a lob2lob -o sapr3 -s PSAPCLUD -t allsel -n PSAPCLU_NEUD

-i PSAPCLU_NEUI -c ctablobind -lc medium -l 2 -p 8 -e 16

Regards,

Wolfgang

[Former Member]

Hi,

Try to execute the command, below;

brspace -u / -f tbreorg -o sapr3 -s PSAPTAB_NEUD -t CUEX -n PSAPTAB_NEUD -i PSAPCLU_NEUI -c ctablobind -lc medium  -p 10 -e 10

Then, convert old LOBs to new LOBs.

Best regards,

Orkun Gedik

[Former Member]

Hi,are you at this point of the note 1431926:

2.2 Converting tables with old LOB fields online

I successfully use a script like this:

brspace -u / -c  -f tbreorg -a lob2lob -o sapr3 -s PSAPFACTD,PSAPFACTI -t allsel -n PSAPSR3FACT -c ctablob -lc medium -SCT -p 4 -e 4

Regards

Leo