set pagesize 0
set linesize 300
spool resize_df.sql
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
and a.tablespace_name not like 'UNDO%'
and a.tablespace_name not like 'SYS%'
order by a.tablespace_name,a.file_name);
-- these are risky commands in PROD . Please use it with care.
-- edit resize_df.sql and keep only the SQL commands
-- enable spool before running resize_df.sql
spool resize_df.lst
@resize_df.sql
-- this may run for some time based on the size of the files
--If any errors, they are written to resize_df.lst
--It is also advisable to run this as background job
select 'alter database datafile ' || '''' || file_name || '''' || ' autoextend on maxsize
unlimited;' from dba_data_files where tablespace_name not like 'UNDO%'
and tablespace_name not like 'SYS%';
credits : http://www.tiplib.com/282/script-generate-sql-enable-autoextend
http://fahdmirza.blogspot.sg/2010/10/script-to-resize-oracle-data-files.html
Great Kumar. It is really awesome. It helped me a lot.
ReplyDelete