reorg failed

[Former Member]

Hi,

We have scheduled reorg for BW system on tablespacce PSAPBWP.

We have successfully completed the re org of all the tables accept two tables

/BI0/F0BWTC_C02

CSMCLSMAP

When we have started manually for this tables showing error

ORA-00955: name is already used by an existing object.

Since number of table partition already move from table /BI0/F0BWTC_C02

to new tablesapce hence unable to go for cleanup.

Kindly suggest.

Regards..

[Former Member]

Hi,

What tools are you using for the reorg.

Also whats's the SAP version kernel etc..

Mark

[Former Member]

BRTools

Kernel bersion:

Kernel release 701

Sup.Pkg lvl. 150

ORA-00955: name is already used by an existing object

BR0280I BRSPACE time stamp: 2012-01-13 11.14.50

BR1106E Reorganization of table SAPBWP./BI0/F0BWTC_C02 failed

BR0280I BRSPACE time stamp: 2012-01-13 11.14.50

BR1124I Starting online reorganization of table SAPBWP.CSMCLSMAP ...

BR0280I BRSPACE time stamp: 2012-01-13 11.14.50

BR0301E SQL error -1452 at location tab_onl_reorg-26, SQL statement:

'CREATE UNIQUE INDEX "SAPBWP"."CSMCLSMAP~0#$" ON "SAPBWP"."CSMCLSMAP#$" ("ASC_CLASS", "L_CLASS_1", "L_CLASS_2")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 417792 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "PSAPBWPNEW" '

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

BR0280I BRSPACE time stamp: 2012-01-13 11.14.50

BR1106E Reorganization of table SAPBWP.CSMCLSMAP failed

[Former Member]

Hi,

In trx: db02old => detailed analysis query CSMCLSMAP* what do you get.

Mark

[Former Member]

Hi Mark,

I've checked for table /BI0/F0BWTC_C02 and found all the objects are in old tablespace i.e. PSAPBWP

Owner Object Type Tablespace KBytes Blocks Extents MaxExtents Next (K)

SAPBWP /BI0/F0BWTC_C020000000451 TABLE PART PSAPBWP 64 8 1 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001196 TABLE PART PSAPBWP 2.048 256 17 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001197 TABLE PART PSAPBWP 128 16 2 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001262 TABLE PART PSAPBWP 640 80 10 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001206 TABLE PART PSAPBWP 832 104 13 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001273 TABLE PART PSAPBWP 576 72 9 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001155 TABLE PART PSAPBWP 128 16 2 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001417 TABLE PART PSAPBWP 128 16 2 1- 1.024

SAPBWP /BI0/F0BWTC_C020 TABLE PART PSAPBWP 64 8 1 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001257 TABLE PART PSAPBWP 832 104 13 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001247 TABLE PART PSAPBWP 64 8 1 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001248 TABLE PART PSAPBWP 576 72 9 1- 1.024

SAPBWP /BI0/F0BWTC_C020000001278 TABLE PART PSAPBWP 448 56 7 1- 1.024

Can i go for cleanup as per the oss note 541538 brspace -f tbreorg -t "*" -a cleanup

Regards,

[Former Member]

Yep, go for the cleanup, but just do the tables in question...

If it does not cleanup the index ~0#$ you will need to drop it.

Mark

[Former Member]

I am firing brspace -f tbreorg -t /BI0/F0BWTC_C02 -a cleanup

let's see what the output.

Thanks

[Former Member]

Hi,

I am successfully cleaned data and after that when i m going for delete old tablesapce showing

ORA-14404: partitioned table contains partitions in a different tablespace

& when i tried to reorg that parititioned table again encounter the below issue

BR1111I Reorganization of table partition SAPBWP./BIC/FZREVCUBE./BIC/FZREVCUBE00

00000657 will be skipped

Please help

Regards,

[Former Member]

Hi,

I tried ALTER TABLE "<table_name>" MOVE PARTITION "<partition_name>"

TABLESPACE "<correct_tablespace_name>";

following is the output:

SQL> ALTER TABLE "/BIC/FZREVCUBE" MOVE PARTITION "SAPBWP./BIC/FZREVCUBE000000065

7" tablespace "PSAPBWPNEW";

ALTER TABLE "/BIC/FZREVCUBE" MOVE PARTITION "SAPBWP./BIC/FZREVCUBE0000000657" ta

blespace "PSAPBWPNEW"

*

ERROR at line 1:

ORA-00972: identifier is too long

Please help.....

Regards,

R Mav

[volker_borowski2]

Partition Owner is a seperate string !

wrong: ALTER TABLE "/BIC/FZREVCUBE" MOVE PARTITION "SAPBWP./BIC/FZREVCUBE000000065"

correct: ALTER TABLE "/BIC/FZREVCUBE" MOVE PARTITION "SAPBWP"."/BIC/FZREVCUBE000000065"

V.

[Former Member]

Hi Volker,

No luck...

SQL> ALTER TABLE "SAPBWP"."/BIC/FZREVCUBE" MOVE PARTITION "SAPBWP"."/BIC/FZREVCU

BE0000000657" tablespace "PSAPBWPNEW";

ALTER TABLE "SAPBWP"."/BIC/FZREVCUBE" MOVE PARTITION "SAPBWP"."/BIC/FZREVCUBE000

0000657" tablespace "PSAPBWPNEW"

*

ERROR at line 1:

ORA-14020: this physical attribute may not be specified for a table partition

Regards,

R Mav

[Former Member]

Hi,

I have successfully alter partitioned table by following command

SQL> ALTER TABLE "SAPBWP"."/BIC/FZREVCUBE" MOVE PARTITION "/BIC/FZREVCU

BE0000000657" tablespace "PSAPBWPNEW";

Also done index rebuilding for same.

But during the tablespace deletion as well renaming i encountered below error.

BR0641I Rename of database file 'G:\ORACLE\BWD\SAPDATA1\SYSTEMNEW_1\SYSTEMNEW.DA

TA1' waiting for releasing it by Oracle...

BR0252E Function rename() failed for 'G:\ORACLE\BWD\SAPDATA1\SYSTEMNEW_1\SYSTEMN

EW.DATA1 -> G:\ORACLE\BWD\SAPDATA1\SYSTEMNEW_1\bwp.data1' at location BrFileRena

me-1

Is that warning can i ignore this.....

Please help.

Regards,

R Mav

Edited by: raj mav on Jan 15, 2012 2:25 PM