on 08-25-2009 6:18 AM
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
B)
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
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,
2 possibilities:
1. export OFFLINE
2. convert to BLOB online (or offline)
SAP note 646681 explains both options.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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,
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.