Brspace use internally Oracle DBMS_REDEFINITION. If you have SAP with Oracle, this is a very fast way to reorganize object in Oracle Database.
In this example we will organize simultaneously S562,MLPPF and MLCRP tables.
Important : If you want to reorganize various tables and indexes, these must reside in same tablespace.
1- Tables reorganization.
brspace -p /oracle/PRD/102_64/dbs/initPRD.sap -c force -s 20 -l E -f tbreorg -a reorg -s PSAPSR3 -o SAPSR3 -t "S562,MLPPF,MLCRP" -n PSAPSR3 -e 16 -p 16 -m online
* /oracle/PRD/102_64/dbs/initPRD.sap : SAP Parameter file
* PSAPSR3 : Source tablespace
* SAPSR3 : Table owner
* PSAPSR3 : Destiny tablespace.
* -e 16 -p 16 -m : It indicates how many parallel processes that will perform the operation,in this case are 16.
* online : It indicates that the reorganization of the tables will be made ONLINE
2- After tables reorganization you will need to rebuild the S562,MLPPF and MLCRP indexes tables .
brspace -p /oracle/PRD/102_64/dbs/initPRD.sap -c force -s 20 -l E -f idrebuild -a rebuild -s PSAPSR3 -o SAPSR3 -i "S562~0,S562~Z01,MLPPF~0,MLCRP~0" -c cind -ic all_col -p 16 -m online
* /oracle/PRD/102_64/dbs/initPRD.sap : SAP Parameter file
* PSAPSR3 : Source tablespace
* SAPSR3 : Index owner
* -p 16 : It indicates how many parallel processes that will perform the operation,in this case are 16.
3- The last step consists in run the tables statistic :
exec dbms_stats.gather_table_stats(ownname =>'SAPSR3',tabname =>'S562',estimate_percent => 10 ,cascade => TRUE,method_opt => 'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',degree =>16);
exec dbms_stats.gather_table_stats(ownname =>'SAPSR3',tabname =>'MLPPF',estimate_percent => 10 ,cascade => TRUE,method_opt => 'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',degree =>16);
exec dbms_stats.gather_table_stats(ownname =>'SAPSR3',tabname =>'MLCRP',estimate_percent => 10 ,cascade => TRUE,method_opt => 'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',degree =>16);
Comments