Recently I needed to extend many datafiles on a database with more than 500 tablespaces because a lot of tablespaces were reaching the critical threshold.
Autoextend was not an option due to a bug I encountered on 10gR2 RAC on ASM and AIX.
The solution was the following script: it generates statements to autoextend datafiles with usage over a defined threshold (the “80” in the where clause) to low down the percentage below another defined threshold (the “75” in the select clause).
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT 'alter database datafile '''||f.file_name|| ''' resize '||round(ceil(bytes/1024/1024/75*t.pct_used+50),-2)||'M;' FROM dba_data_files f, ( SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free, round((a.total-nvl(b.free,0))*100/a.total) pct_used FROM (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree FROM dba_free_space GROUP BY tablespace_name) b, (SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total FROM dba_data_files GROUP BY tablespace_name) a WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name AND t.pct_used>80; |
Prior to extend it’s possible to show how much space is required to do this mass resizing:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT sum(mb_new-mb_old) FROM ( SELECT t.tablespace_name, f.file_name, bytes/1024/1024 mb_old, round(ceil(bytes/1024/1024/75*t.pct_used+50),-2) mb_new FROM dba_data_files f, ( SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free, round((a.total-nvl(b.free,0))*100/a.total) pct_used FROM (SELECT tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree FROM dba_free_space GROUP BY tablespace_name) b, (SELECT tablespace_name,decode(round(sum(bytes)/1024/1024),0,1,round(sum(bytes)/1024/1024)) total FROM dba_data_files GROUP BY tablespace_name) a WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name AND t.pct_used>80); |