Reorganization in a database context is the recreation or cleanup of one or several segments.
Prerequisite of Table Reorganization
1.) Tablespace Size :
Firstly check Tablespace size because during this process oracle use extra space for this process.In order to check the same please run the below sql command or
use brtool for the same .
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",decode(ceil (tsf.free_mb), NULL,0,ceil(tsf.free_mb))
"free MB",decode(100 – ceil(tsf.free_mb/ tsu.used_mb*100), NULL, 100,100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**' ,sum(bytes)/1024/1024 used_mb from dba_temp_files group by tablespace_name) tsu,(select tablespace_name, sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name) tsf where tsu.tablespace_name = tsf.tablespace_name (+) order by 4
If tablespace %USED less than 70 that we can easy Reorganization tables without any space management. During this activity check tablespace size regularly.Otherwise we monitor table space space (Add new datafiles, Autoextened On)
2.) Backup Verification
Please do verify that the full data base back up is in place including the archive log of the time before the reorg start as we require that for a restore if some problem occur .
You can check the same /oracle/SID/sapbackup/*.anf file here .
3.) Space Check
Regularly check /oracle/SID/saparch size because during this activity frequently archive files generated
4.) Check reorg log
Regularly check table reorganization log /oracle/SID/sapreorg :cat /oracle/SID/sapreorg/*.tbr
Following Step perform for Table Reorganization
1.) Login to the system with user ora<sid> .
2.) Execute the below command for the reorganization on a single table space .
brspace -f tbreorg -s <Tablespace_Name> -t "*" -p 4
Now your reorganization process completed
Post Installation Step :
1.) Create new statistics for the reorganized tables:
Syntax of the comand is : brconnect -u / -c -f stats -t <Tablespace_Name> -f collect -p 4
Now statistics for the reorganized tables completed
Problem Occur During Reorganization:
If Tablespace Full then it will give error
Skip Table (LONG (RAW) column)
Ora-12091 error occur during Reorganization.
follow this step to drop the '#$' materialized views
Check through this query which tables already generated materialized views.
select master,log_table from dba_snapshot_logs;
Using this we can drop the these materialized views
brspace -f tbreorg -t "*" -a cleanup
After that again reorganize this table.