Thursday, June 23, 2011

Mapping tablespace usage in Oracle

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


  1. Very, very interesting.
    But Google says "Workspace not found" on link to script.
    Can you put it elsewhere?
    I cant find anything similar.

    1. 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.

  2. This comment has been removed by the author.