Tablespace reorg

[Former Member]

Hi,

I want to reorg my database. I am working on ECC6 ,oracle 10g & AIX operating System.I have reorg tablespaces using the option successfully.

brspace -f tbreorg -s psapddicd -t "*" -p 4

But table have long field can not reorg with this option. And if i try to change the LONG field to LOB field.Than it will impact on performance . Beacuse we have no option to convert LOB field to LONG field after reorg.

Thanks

[JPReyes]

Follow SAP Note 646681, that explain how to reorg tablespaces with LONG fields

Regards

Juan

[Former Member]

hi Jaun

Thanks for reply .

I have alredy read the note 646681

4. Creating DDL statements for tables with LONG fields
----------------------------------------------------------
brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_tab
brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
28.01.2009 Page 3 of 11
Note 646681 - Reorganizing tables with BRSPACE
-n <new_tsp> -d only_ind
brspace -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_dep
The old tablespaces still only contain tables with LONG fields after a
successful online reorganization.
Caution: The DDL script for the dependent objects that was generated in the
last step consists, for SAP databases, entirely or almost entirely of
comments.
5. Exporting data from tables with LONG fields
--------------------------------------------------
brspace -f tbexport -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-r yes -i no -c no -g no -e no
Indexes, constraints, grants or triggers are not exported for the tables
with LONG fields. The DDL statements for these objects were already created
in the previous step.

But i do not understand . I have also tried with new tablespace . It terminates after 22 tables reorg. The size of the new table space was 8 gb more than the tablespace which i want to reorg.

Thanks

[JPReyes]

You are doing something wrong.. follow the steps in Section II of the note. You might find the new tablespace bigger because the indexes are been moved to it instead of a separate tablespace. to avoid that use switch -i <index_tablespace>

Regards

Juan

[Former Member]

Hi,

Thanks for your helplful answer. I have one query .I am reorganizing a tablespace which is 60GB size & free space in tablespace is near about 15 GB . Now can you tell me the aprox size of new tablespace.When i start the reorg i assign 60 GB size for the new tablespace but i got error.

thanks

[Former Member]

Hi,

I have start again with the following option

1:- Create a new tablespace using the command

brspace -f tscreate -t PSAPPRD700new -s 20G -a yes -m 20G
-i 1024M -l PSAPPRD700

I have increass the size of new tablespace up to 75 GB, its 30 Gb bigger than old tablespace.

2:- After that i start the online reorg using

brspace -f tbreorg -s PSAPPRD700 -t "*" -n PSAPPRD700new -p 4

But after that i get the following error

BR0301W SQL error -12091 at location BrReorgCheck-5, SQL statement:
'BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE (UNAME => '"SAPPRD"', TNAME => '"AAB_ID_PROPT"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK); END;'
ORA-12091: cannot online redefine table "SAPPRD"."AAB_ID_PROPT" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
BR1111I Reorganization of table SAPPRD.AAB_ID_PROPT will be skipped

Now can you please suggest me some solution .

I am performing reorg on oracle 10g

Thanks & Regards

Rudra