Resize your Oracle datafiles down to the minimum without ORA-03297


set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
— get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
— join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
— join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible=’YES’ and maxbytes>=bytes
then — we generate resize statements only if autoextensible can grow back to current size
‘/* reclaim ‘||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||’M from ‘||to_char(ceil(bytes/1024/1024),999999)||’M */ ‘
||’alter database datafile ”’||file_name||”’ resize ‘||ceil(hwm_bytes/1024/1024)||’M;’
else — generate only a comment when autoextensible is off
‘/* reclaim ‘||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||’M from ‘||to_char(ceil(bytes/1024/1024),999999)
||’M after setting autoextensible maxsize higher than current size for file ‘
|| file_name||’ */’
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 — resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc

Below is the o/p of this query 

—————————————–

/* reclaim   26235M from   30000M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tstlog_idx01.dbf */

/* reclaim   25056M from   32767M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_data21.dbf */

 

/* reclaim   11934M from   32767M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_data19.dbf */

/* reclaim    9361M from   28287M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_cons_idx03.dbf */

/* reclaim    8831M from   32767M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_sample_idx29.dbf */

 

/* reclaim    5111M from    5120M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_gening_lob01.dbf */

/* reclaim    4626M from    7981M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_cons_data04.dbf */

/* reclaim    4370M from   30255M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_cons_data03.dbf */

 

/* reclaim    3974M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx08.dbf */

/* reclaim    3937M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx10.dbf */

/* reclaim    3825M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx05.dbf */

/* reclaim    3786M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx02.dbf */

/* reclaim    3727M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx01.dbf */

/* reclaim    3654M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx07.dbf */

/* reclaim    3644M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx03.dbf */

/* reclaim    3610M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_idx04.dbf */

/* reclaim    2990M from   20480M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_gening_idx01.dbf */

/* reclaim    2872M from   30720M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_gening_data01.dbf */

/* reclaim    2852M from    3072M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_gening_data02.dbf */

/* reclaim    1738M from   11088M */ alter database datafile ‘/db1/DATA01/test_db1/test_db1_ccc_data01.dbf’ resize 9351M;

/* reclaim    1608M from    5120M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tstlog_data01.dbf */

/* reclaim    1464M from    9164M */ alter database datafile ‘/db1/DATA01/test_db1/test_db1_ccc_index01.dbf’ resize 7701M;

/* reclaim    1023M from    1024M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_cons_lob01.dbf */

/* reclaim    1022M from    1024M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_sample_lob01.dbf */

/* reclaim     695M from     700M */ alter database datafile ‘/db1/DATA01/test_db1/test_db1_system01.dbf’ resize 5M;

/* reclaim     372M from   20480M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_undotbs101.dbf */

/* reclaim     331M from    2591M */ alter database datafile ‘/db1/DATA01/test_db1/test_db1_sysaux01.dbf’ resize 2261M;

/* reclaim     203M from   32767M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_tst_data17.dbf */

/* reclaim      95M from     100M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_users01.dbf */

/* reclaim      61M from   32767M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_sample_idx27.dbf */

/* reclaim      20M from   32767M after setting autoextensible maxsize higher than current size for file /db1/DATA01/test_db1/test_db1_sample_data34.dbf */

A few remarks about This query:

  • this query  generate the resize statements only for datafiles which are autoextensible. This is because I want to be sure that the datafiles can grow back to their original size if needed.
  • When datafile is not autoextensible, or maxsize is not higher than the current size, It only generate a comment.

There is one more command we can use to find the reclaim the space, by resizing the data file.

SQL> select ‘alter database datafile ”’||ds.file_name||”’ resize ‘||round(ds.total_size – fs.free_space +1)||’M;’ Final
from
(select sum(bytes/1024/1024) free_space,file_id from dba_free_space where tablespace_name=’&TBS’ group by file_id) fs,
2 3 4 (select file_id,file_name,bytes/1024/1024 total_size from dba_data_files where tablespace_name=’&TBS’)ds
where fs.file_id=ds.file_id order by round(ds.total_size – fs.free_space +1)
/
5 6 Enter value for tbs: TBS1_IDX
old 3: (select sum(bytes/1024/1024) free_space,file_id from dba_free_space where tablespace_name=’&TBS’ group by file_id) fs,
new 3: (select sum(bytes/1024/1024) free_space,file_id from dba_free_space where tablespace_name=’TBS1_IDX’ group by file_id) fs,
Enter value for tbs: TBS1_IDX
old 4: (select file_id,file_name,bytes/1024/1024 total_size from dba_data_files where tablespace_name=’&TBS’)ds
new 4: (select file_id,file_name,bytes/1024/1024 total_size from dba_data_files where tablespace_name=’TBS1_IDX’)ds

FINAL
————————————————————————–
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx01.dbf’ resize 15128M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx07.dbf’ resize 15672M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx08.dbf’ resize 17245M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx09.dbf’ resize 18987M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx10.dbf’ resize 20920M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx11.dbf’ resize 23039M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx04.dbf’ resize 24349M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx12.dbf’ resize 25364M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx06.dbf’ resize 27396M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx30.dbf’ resize 27586M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx03.dbf’ resize 27903M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx13.dbf’ resize 27954M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx02.dbf’ resize 30489M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx14.dbf’ resize 30782M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx05.dbf’ resize 32466M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx29.dbf’ resize 32706M;
alter database datafile ‘/db1/DATA01/test_db1/test_db1_tbs1_idx28.dbf’ resize 32721M;

17 rows selected.

 

References: http://blog.dbi-services.com/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s