Thursday, November 17, 2016

Reorg of Table



Reorg of BALDAT

Pre-requisites "

1) Take the DB02 snap shot of BALDAT size,


2) To check total size of the table:

select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='<yourtablename>';


3) To see count of rows for specfic table:

      select count(*) from  sapr3."BALDAT";


4) To see indexes for Specific tables:

select TABLE_NAME,Index_name,UNIQUENESS from all_indexes where TABLE_NAME
like'%BALDAT%';


Fragmentation of tables to check:

SQL> select table_name,round((blocks*8),2)||'kb' "size" from dba_tables where table_name = 'BALDAT';

TABLE_NAME                     size
------------------------------ ------------------------------------------
BALDAT                         40941440kb




Actual data in table:

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from dba_tables where table_name = 'BIG1';

TABLE_NAME                     size
------------------------------ ------------------------------------------
BALDAT                          30604.2kb


Note: Fragmentation size - Actual size = XXXXXXX Kb is wasted space in table

You will achieve this much of space after reorg.


Below is the command to reorg online:

brspace -u / -f tbreorg -s PSAPTABD -t BALDAT -p 5 -e 5


++++++++++++++++++++++++++++++++++

Missing stats to collect:
brconnect -p init<SID>.sap -1 E -u / -f stats -t missing -c 30 -p 1 -f collect

-----------------------------------------------------------
Table clean-up command:
brspace -p init<SID>.sap -s 20 -l E -u / -f tbreorg -a cleanup -s PSAPTABD -t BALDAT


1 comment: