/*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;
/
Ex:- exec SHRINK_TBSP('tablespace_name',shrink_size);
