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.
\nAutoextend was not an option due to a bug I encountered on 10gR2 RAC on ASM and AIX.<\/p>\n
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).<\/p>\n
\r\nSELECT 'alter database datafile '''||f.file_name||\r\n''' resize '||round(ceil(bytes\/1024\/1024\/75*t.pct_used+50),-2)||'M;'\r\n FROM\r\n dba_data_files f, (\r\n SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,\r\n round((a.total-nvl(b.free,0))*100\/a.total) pct_used\r\n FROM (SELECT tablespace_name,round(sum(bytes)\/1024\/1024) free, max(bytes) maxfree\r\n FROM dba_free_space GROUP BY tablespace_name) b,\r\n (SELECT tablespace_name,decode(round(sum(bytes)\/1024\/1024),0,1,round(sum(bytes)\/1024\/1024)) total\r\n FROM dba_data_files GROUP BY tablespace_name) a\r\n WHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name\r\n AND t.pct_used>80;\r\n <\/pre>\n Prior to extend it’s possible to show how much space is required to do this mass resizing:<\/p>\n
\r\n SELECT sum(mb_new-mb_old) FROM (\r\n 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\r\n dba_data_files f, (\r\n SELECT a.tablespace_name tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,\r\n round((a.total-nvl(b.free,0))*100\/a.total) pct_used\r\nFROM (SELECT tablespace_name,round(sum(bytes)\/1024\/1024) free, max(bytes) maxfree\r\n FROM dba_free_space GROUP BY tablespace_name) b,\r\n (SELECT tablespace_name,decode(round(sum(bytes)\/1024\/1024),0,1,round(sum(bytes)\/1024\/1024)) total\r\n FROM dba_data_files GROUP BY tablespace_name) a\r\nWHERE a.tablespace_name = b.tablespace_name (+)) t WHERE t.tablespace_name=f.tablespace_name\r\nAND t.pct_used>80);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 … Continue reading