resize datafile and enable autoextend


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

1 comment:

  1. Great Kumar. It is really awesome. It helped me a lot.

    ReplyDelete