Skip to end of metadata
Go to start of metadata

Definition of Reorganizations

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 .

Syntax :-
brspace -f tbreorg -s <Tablespace_Name> -t "*" -p 4

Enter -C

Enter C


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.







  • No labels