This was first published on https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297 (2014-09-08)
Republishing here for new followers. The content is related to the the versions available at the publication date
Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent.
But if you try to get lower, you will get:
ORA-03297: file contains used data beyond requested RESIZE value
So, how do you find this minimum value, which is the datafile’s high water mark? You have the brute solution: try a value. If it passes, then try a lower value. If it failed, then try a higher one. Or there is the smart solution: find the datafile high water mark.
You can query DBA_EXTENTS to know that. But did you try on a database with a lot of datafiles? It runs forever. Because DBA_EXTENTS is doing a lot of joins that you don’t need here. So my query directly reads SYS.X$KTFBUE which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.
Note that the query may take a few minutes when you have a lot of tables, because the information is on disk, in each segment header, in the bitmaps used by LMT tablepaces. And you have to read all of them.
Here is my query:
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 /and here is a sample output:
/* reclaim 3986M from 5169M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs1_o9pfojva_.dbf' resize 1183M; /* reclaim 3275M from 15864M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_apcpy_o5pfojni_.dbf' resize 12589M; /* reclaim 2998M from 3655M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_qt_oepfok3n_.dbf' resize 657M; /* reclaim 2066M from 2250M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs2_olpfokc9_.dbf' resize 185M; /* reclaim 896M from 4000M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_ocpfok3n_.dbf' resize 3105M;You get directly the resize statements, with the reclaimable space in comments.
A few remarks about my query:
Note that I’m using that query for quite a long time. I even think that it was my first contribution to Oracle community on the web, about 9 years ago, in the dba-village website. Since then my contribution has grown to forums, blogs, articles, presentations, … and tweets. Sharing is probably addictive
Thanks to #QueryScope @SQLdep here is the query visualisation: Nothing difficult here, isn’t it?
this is great stuff Frank!
Thanks! Leruo
Hello. I have a tablespace with 4 datafiles. The name is mail_ts.dbf. It had grown to over 90gb. I reviewed the application owner and discovered I could truncate a table. This reduced the size to 4GB. The resize will not work due o the high water mark. How can I resize the mail_ts.dbf to 4GB and use only one datafile?
Hi,
Basically, you need to move (table move or index rebuild) extents that are above the free space. You can check which table/index it is from dba_extents.
Or much simple – but need 2x space – just move everything to another tablespace. This is the only solution if you want to reduce the number of datafiles rather than reduce their size but keep all. Regards, Franck.
Great script Franck! Just saved me a ton of ball ache. Cheers Ralph
Very usefull information! Thank you so much!
I have a doubt, the following queries (1 and 2) should return the same information?
1) select max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue where ktfbuefno = 8;
2)select max(block_id + blocks-1) hwm_blocks from dba_extents where file_id=8;
I am asking that because I have seen several places saying that the second one could be used to find the datafile HWM but I can’t resize my datafiles based on it (ORA-03297). From x$ktfbue I found a realistic number but unfortunatelly I dont’t know how to map it to database objects so i can move/shrink then to release free space and reduce fragmentation.
Hi Mark, DBA_EXTENTS doesn’t show extents from tables in recyclebin. That may be the reason for the difference with querying directly x$ktfbue. The mapping to objects is done by ktfbuesegfno,ktfbuesegbno which maps to HEADER_FILE, HEADER_BLOCKS in DBA_SEGMENTS. Regards, Franck.
Hi Franck,
I had a sql use to reduce some datafile and i loose it.
I find your page and really, it s so excellent. Simple, quick and efficient, i like it.
Thanks a lot Laurent for your feedback. A good way to avoid to loose your sql is to put them in a blog post Regards, Franck.
Hi Frank,
Your query is excellent, but it only checks for datafiles which are autoextend on. I tried to modify it for all datafiles but it still returning for autoextend on datafiles.
Can you help me modify your query for all datafiles?
Regards, Rahul
Hi Rhaul, Thanks for your feedback. It’s on purpose that I generate resize statements only for autoextensible files that can reach back their original size, to avoid the risk of errors or monitoring alerts. The statements are commented out when datafile is not autoextensible. So you should see them but commented out. Regards, Franck
Thanks for the quick response Frank, There are around 663 datafiles in my database.I want to resize all the datafiles which autoexend off as there are they have too much space. My plan is to do not resize the datafiles which are autoextend on.
I am trying to modify your query for this, but still unable to fetch the commands for the datafiles which are autoextend off.
Can you help me out with modifying your code?
Regards, Rahul
Hi Frank, I was able to modify your query for autoextend off files. Also, this one will exclude UNDO tablespace datafiles which we should not resize.
=========
Hi Rahul, Thanks for sharing. Regards, Franck.
Incredible Thanks for sharing
J
Hi,
Many thanks for sharing this! It’s saved my a lot of time!.
regards, Syaifuddin
Thank you, Frank! I’ve left my headache!
Excellent! I have seen several versions of similar scripts in the past but this is the most elegantly efficient of them
This is indeed commendable work Frank. Something not many people would share that easily.
Thankyou!
Thanks for sharing this SQL. It performed very well even in a very huge database.
Thanks Frank !
I made some changes to your query :
1.Add the same type of resizing for temporary files (based on sys.x$KTSSO) , mainly for 10g where we do not have the convenient alter tablespace temp shrink space; 2.Wrap the whole thing in a pl/sql
Have a good dayAmer
Thanks Amer. You take the responsibility for the ‘execute immediate’ (I prefer to have people copy/paste so that they have a look at it) Regards, Franck.
Amazing script, worked like a charm. Hats off to Frank!
Thanks once again, Frank.
Beautiful script Franck. Just what the doctor ordered. Thanks a ton.
Works like a charm and a very useful script. Many thanks Franck.
Nice Script. Very useful. Thank you very much.