Table reorg failed

[Former Member]

Hello experts,

we are running oracle Database reorganization with Brtools, we are on oracle 10.2 and brtools 7.1 patch 12, during Database reorganization we are getting issue errors as below, can some one help me please

BR1110W Table SAPP00.MC11VA0HDRSETUP has a LONG (RAW) column

BR1111I Reorganization of table SAPP00.MC11VA0HDRSETUP will be skipped

We have tried the re-org using offline mode, still we get back the same error...

[Former Member]

Hi,

Table having LONGRAW cannot be done through online reorganization.It requires offline reorganization.

Do the following steps for offline reorganization-

1.Export the table

2.Truncate the table

3.Import the table.

Before doing the Reorganization,check the constraints for the table by the following command.

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME from dba_constraints where table_name = 'SMW3_BDOC2';

Commands for doing Reorganization:

Create a folder where you have space

mkdir /oracle/<SID>/sapdata1/reorg

Export table

-

---

brspace -f tbexport -t "TST03" -r yes -i no -c no -g no -e no -u /oracle/<SID>/sapdata1/reorg

15 Minutes

If successfull then only truncate table

-

---

SQL> truncate table sap<SID>.TST03;

commit;

5 mInutes

Import Table

-

---

brspace -f tbimport -y full -r yes -i no -c no -g no -n yes -x /oracle/<SID>/sapdata1/reorg/<somefolder>.edd/expdat.dmp

30Minutes

Update stats and rebuild index

Or You can use the standard BRTOOLS for doing the same...

Thanks and Regards,

[Former Member]

Hi Venkata Nooney,

I followd below instruction for offline reorg.

1)Export table

brspace -f tbexport -t "TST03" -r yes -i no -c no -g no -e no -u /oracle/<SID>/sapdata1/reorg

2)SQL> truncate table sap<SID>.TST03;

3)Import Table

brspace -f tbimport -y full -r yes -i no -c no -g no -n yes -x /oracle/<SID>/sapdata1/reorg/<somefolder>.edd/expdat.dmp

4)Update stats and rebuild index

I have one question on point no (3)

I have created one new Tablespace PSAPSR3700

Question: - How can I move table in newly created tablespace ?

Then what will be the brspace command (point 3) or how can I do by BRTOOLS.

Thanks & Regards

Vinay Patel

[Former Member]

Hi,

1.You can convert the LONGRAW table to LOB table.and then import into the new tablespace by doing the reorganization of tables using the BRSPACE.

OR

2.You can also export the table and create a new table with the same structure of the original table in the new tablespace.

rename the original table in the old tablespace.change the newly created table name to the original table name(in new tablespace) and import the table.

Once the import is successful,check and delete the original table in the old tablespace.

Thanks and Regards,