Question Re: Table reorg failed in Technology Q&A

Table reorg failed

Former Member — Tue, 25 Aug 2009 05:18:00 GMT

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...

Re: Table reorg failed

Former Member — Tue, 25 Aug 2009 05:36:23 GMT

SAP note 646681 - Reorganizing tables with BRSPACE

Either special offline reorganization or convert LONG RAW to BLOB (SAP note 835552) and then reorg online

Re: Table reorg failed

Former Member — Tue, 25 Aug 2009 10:26:25 GMT

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,

Re: Table reorg failed

anindya_bose — Tue, 25 Aug 2009 11:48:21 GMT

BRSPACE skips tables with LONG RAW field. SO you can not reorganize tables with LONG RAW field with BRTOOLS.

Either you can change LONG RAW to LOB ( LONG LOG Migration, note number has been provided in the postings above). But this SAP notes also say that changing to LOB might cause performance problem. or you can simply do export/import method.

1. Brtools~~->Segment Management~~->Export Tables--->Tablename, Tablespace names,dump directory etc have to be specified.

2. Then Truncate the table....from Oracle level by command "Truncate table <tablename>;

you can see now the count is zero.

3. In the third step, import table from its dump file. BRTOOLS has this option also.

Check the link below for more information.

Best Regards

Anindya

Edited by: Anindya Bose on Aug 25, 2009 5:55 PM

Re: Table reorg failed

Former Member — Tue, 25 Aug 2009 11:59:13 GMT

2 possibilities:

1. export OFFLINE

2. convert to BLOB online (or offline)

SAP note 646681 explains both options.

Re: Table reorg failed

Former Member — Wed, 26 Aug 2009 01:34:21 GMT

First of all, thanks for all the useful advise!

Does that mean for me to re-org the table using offline mode, i have to perform the import/export steps first?

If yes for above, why is the method 're-org in offline mode' not working if the import/export steps is not perform?

Appreciate the replies, thanks again!

Re: Table reorg failed

anindya_bose — Wed, 26 Aug 2009 13:03:47 GMT

Mostly two methods are being used for reorganization

One is Online reorganization.

A) Brtools~~->Segment Management~~->Reorganize Tables ( other options are self explanatory)

Other is Exp/IMPOrt

Steps 1) Brtools~~->Segment Management~~->export tables ( it will crate a dump file expdat.dmp)

Steps 2) from oracle level you have to truncate the table ..Command " truncate table <tablename>;"

Then run "select count(*) from SAP_CHEMA.TABLENAME; " It should show zero entries

Steps 3) Brtools~~>Segment Management~~->Import Table ( it will ask for the dump file path)

Please read my previous post and the notes I meantioned there before starting.

Best Regards

Anindya

Re: Table reorg failed

Former Member — Wed, 26 Aug 2009 14:24:50 GMT

reorg in offline mode... SAP must be down to start this.

please provide exact error message you get.

Re: Table reorg failed

Former Member — Thu, 27 Aug 2009 12:03:51 GMT

Hi,

Does that mean for me to re-org the table using offline mode, i have to perform the import/export steps first? ->Yes,Do the reorganization using offline method.For doing the reorganization,export the tables and import the tables accordingly.

If yes for above, why is the method 're-org in offline mode' not working if the import/export steps is not perform?->Reorg in offline method means export/import method.

Thanks and Regards,

Re: Table reorg failed

Former Member — Fri, 28 Aug 2009 14:42:36 GMT

The term "offline reorganization" is a little bit confusing. I assume you mean "-m offline" when you speak about offline reorganization. This is the ALTER TABLE MOVE approach that cannot handle LONG and LONG RAW columns. Traditionally we speak about EXP / IMP ("-f tbexport", "-f tbimport") when saying "offline reorganization". This reorganization approach is able to handle LONG and LONG RAW columns.

Re: Table reorg failed

Madhusudank — Sun, 30 Aug 2009 03:36:57 GMT

Note 646681 - Reorganizing tables with BRSPACE

Read this note and follow the steps given in the part III.

III. Converting the LONG fields into LOB fields online as of Oracle 10g

========================================================

You can then continue with reorganizaing tables with 'BRSPACE ONLINE'

Hope this will help.

Re: Table reorg failed

Former Member — Thu, 22 Apr 2010 11:55:39 GMT

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

Re: Table reorg failed

Former Member — Fri, 23 Apr 2010 17:09:06 GMT

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.

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,