Table Reorg - How to do - commands

1. archive destination (sufficient space) - 20GB - 22GB (screen shot)
bdf | grep saparch (SID)


2. Tablespace PSAPEVO - object - SPACE should be same as table size - (20%). (screen shot)

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
, sum(bytes_used + bytes_free)/1024/1024 used_mb
from v$temp_space_header group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name union all
select tablespace_name,sum(bytes_free)/1024/1024 free_mb from v$temp_space_header group by tablespace_name ) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;

3. Collect table statistics

brconnect -c -u / -f stats -t /BIC/B0000032000 -f collect -p 2

4. Checking TOP 20 Fregmented tables.

SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *
(1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE /
100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE 'SAP%' AND
PARTITIONED = 'NO' AND Table_name in ('/BIC/B0000034000
','/BIC/B0000582000',
',
(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;

5. Getting Table & DB size

select sum(bytes)/1024/1024/1024 from dba_segments;

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=();