|
Tip of the Week
Tip for Week of June 1, 2003
Shrink Database Size
This tip comes from Naresh Awasthi, a Senior Oracle DBA at Kyocera Wireless Corporation in San Diego, CA.
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;
/
|
Bookmark
