Shrink Database Size...

Usefull doc i read written by Naresh




This database procedure can be used to shrink tablespaces by passing two values:



1. Tablespace_name



2. Freespace_keep_pct



This database procedure can be used to shrink tablespaces by passing two values:



1. Tablespace_name



2. Freespace_keep_pct



It's specially useful if you are refreshing a dev instance from prod and you don't want to occupy the same size RBS, TEMP, and tablespace as prodution. Just run this procedure as a last step of your refresh process and reclaim all the freespace that can be used for some other purpose.





/*

Procedure : Shrink_tbsp

Owner : SYS

Author : Naresh Awasthi



Note : Set serveroutput on before running this procedure.





*/



create or replace procedure SHRINK_TBSP (p_tablespace_name in varchar2,

p_keep_size_pct number) AUTHID CURRENT_USER is

FILE_SIZE number;

FREE_BYTES number;

FILEID number;



LAST_BLOCKID_USED number;

FREE_BLOCKID number;

FILE_NAME varchar2(2000);

STMT varchar2(2000);

SHRINK_TO number;



cursor FREE_SPACE_CUROR is

select file_id FILEID,max(block_id) FREE_BLOCKID from dba_free_space

where tablespace_name=p_tablespace_name group by file_id,bytes;





BEGIN

FOR fsc_row in FREE_SPACE_CUROR LOOP

select bytes into FREE_BYTES from dba_Free_space where file_id=fsc_row.FILEID and block_id=fsc_row.FREE_BLOCKID;

select bytes,file_name into FILE_SIZE,FILE_NAME from dba_data_files where file_id=fsc_row.FILEID;

select nvl(max(block_id),0) into LAST_BLOCKID_USED from dba_extents where file_id=fsc_row.FILEID;

if LAST_BLOCKID_USED < fsc_row.FREE_BLOCKID THEN

select round((FILE_SIZE - round(FREE_BYTES * (100 - p_keep_size_pct) / 100))/1024/1024) into SHRINK_TO from dual;

STMT := 'alter database datafile '

''''

FILE_NAME

''''

' resize '

SHRINK_TO

'M';

EXECUTE IMMEDIATE STMT;



dbms_output.put_line ('Shrunk '

FILE_NAME

' from

'

FILE_SIZE

' to '

SHRINK_TO

' MB.');

end if;

END LOOP;



EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('Done.');

WHEN OTHERS THEN

dbms_output.put_line('Unhandled Error : '

sqlerrm);

END;
/