Lets say that you need to reduce the size of a datafile to reclaim some space (like in an urgency), then after checking the size and usage of one datafile you issue an ALTER DATABASE DATAFILE RESIZE just to get this error:
ORA-03297: file contains used data beyond requested RESIZE value
You may wonder why you cannot reduce the size of that datafile if there is enough free space, and more likely you cannot do that because some segment (table, index, etc.) is placed beyond the resize value. You have to move that segment with techniques like rebuilding indexes and moving tables, but before trying to resize a datafile you might like to map the tablespace usage with this script: Tablespace_map2.sql
Disclaimer: I DIDN'T WRITE THIS SCRIPT, I just modified it to split the maps by datafile as is stated in the comments at the beginning of the script. I was unable to find the original author so I cannot credit him properly.
After running this script you will get an HTML file with a map usage by datafile of the selected tablespace; this script is very handy if you can only run scripts at a SQL prompt. If you place the cursor over a block you will get information about the owner, segment type and name that block belongs to, or if it's a free block.
More information:
ORA-03297 Tips
Very, very interesting.
ReplyDeleteBut Google says "Workspace not found" on link to script.
Can you put it elsewhere?
I cant find anything similar.
I'm sorry, I deleted the site were that script was placed and I can't find it in my computers, it has been a long time since I used it. I even tried to find it using Google to no avail.
DeleteThis comment has been removed by the author.
ReplyDelete