CREATE TABLE SCOTT.TEST_SHRINK
CREATE TABLE SCOTT.TEST_SHRINK AS SELECT * FROM ALL_OBJECTS;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
COMMIT;
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);
FIND OUT TABLE FULL SIZE ,ACTUAL SIZE AND PERCENTAGE OF FRAGMENTATION
set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)", (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from dba_tables a,
dba_segments b
where a.table_name = b.segment_name
and a.owner = b.owner
and a.table_name = 'TEST_SHRINK'
and a.owner='SCOTT';
TAKE TABLE COUNT
select count(1) from scott.TEST_SHRINK;
select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';
DELETE THE DUPLICATE ROWS FROM THE TABLE
DELETE FROM SCOTT.TEST_SHRINK A WHERE ROWID > (
SELECT min(rowid) FROM SCOTT.TEST_SHRINK B
WHERE A.OBJECT_ID = B.OBJECT_ID);
select count(1) from scott.TEST_SHRINK;
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);
select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';
FIND OUT TABLE FULL SIZE ,ACTUAL SIZE AND PERCENTAGE OF FRAGMENTATION
----------------------Use above script
BEFORE TABLE SIZE REPORT
OWNER TABLE_NAME Size(G) Actual(G) LAST ANAL Diff(G) % Frag
------------------------------ ------------------------------ ---------- ---------- --------- ---------- ----------
SCOTT TEST_SHRINK 2.94244385 .023890277 09-FEB-16 2 99.1880804
CHECK ROW MOVEMENT ENABLE FOR A TABLE TEST_SHRINK
Select owner,table_name, ROW_MOVEMENT, TABLESPACE_NAME from dba_tables where owner=’SCOTT’ and Table_name =’TEST_SHRINK’;
ALTER TABLE SCOTT.TEST_SHRINK Enable row Movement;
CHECK TABLESPACE USED FREE SPACE BEFORE DOING SHRINK
col "NAME" format a30
set lines 140
set pages 1000
select a.tablespace_name "NAME",
round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
round(a.freespace,1) "FREE_SPACE_MB",
round(b.totalspace) "TOTAL_SPACE_MB",
round(100 * (a.freespace / b.totalspace)) "%_FREE"
from
( select tablespace_name,
sum(bytes)/1024/1024 TotalSpace
from dba_data_files
group by tablespace_name) b,
( select tablespace_name,
sum(bytes)/1024/1024 FreeSpace
from dba_free_space
group by tablespace_name) a
where b.tablespace_name = a.tablespace_name(+)
order by 1;
ALTER TABLE SCOTT.TEST_SHRINK SHRINK SPACE;
select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);
FIND OUT TABLE FULL SIZE ,ACTUAL SIZE AND PERCENTAGE OF FRAGMENTATION
DISABLE ROW MOVEMENT
ALTER TABLE SCOTT.TEST_SHRINK DISABLE row Movement;