Shrink Tablespace

Here’s an example of a theme that appears on the OTN database forum from time to time (I haven’t included a link to it because it’s just one example of many similar questions):

“I have a user tablespace allocated for 3-4 schemas. As I urgently needed space on hard disk I had to remove one of the schema(drop user). Now this tablespace is shown as 70% filled. I want to reduce allocated space to it.”

The immediate answer is simple – alter database datafile ‘xxx’ resize NNN; – except there are two problems with this solution. First, how do you work out a suitable value for NNN, and secondly what do you do when every value you try for NNN gives you Oracle error: “ORA-03297: file contains used data beyond requested RESIZE value”.

The answer to both problems can be resolved through views dba_extents and dba_free_space. If you have some free space in a tablespace it can only be “reclaimed” if it is at the end of the data files (a point I made quite recently in a more generic comment on free space). dba_free_space will tell you where the space is, dba_extents will tell you what’s in the file after the free space. I’ve published a simple script at this URL to demonstrate the principle. The following text is a sample from the output, showing the last few rows reported for one file, and the rows reported for the second file of a small tablespace I created for demonstration purposes.

FILE_ID    BLOCK_ID   END_BLOCK OWNER      SEGMENT_NAME    SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
      5       2,185       2,312 TEST_USER  PARENT          TABLE
              2,313       2,440 TEST_USER  PARENT          TABLE
              2,441       2,568 TEST_USER  PARENT          TABLE
              2,569       2,696 TEST_USER  PARENT          TABLE
              2,697       2,824 TEST_USER  PARENT          TABLE
              2,825       2,952 TEST_USER  PARENT          TABLE
              2,953       3,080 TEST_USER  PARENT          TABLE
              3,081       3,208 TEST_USER  PARENT          TABLE
              3,209       3,336 TEST_USER  PARENT          TABLE
              3,337      13,448 free       free
             13,449      13,576 TEST_USER  GGP_PK          INDEX
             13,577      13,704 TEST_USER  GP_PK           INDEX
             13,705      13,832 TEST_USER  P_PK            INDEX
             13,833      15,112 free       free
             15,113      15,240 TEST_USER  NUMTEST         TABLE
             15,241      32,648 free       free

      6           9         136 TEST_USER  GENERATOR       TABLE
                137       8,072 free       free

As you can see, I could shrink file six all the way down to block 136, and file five down to block 15,240. If I move table numtest it will (probably) go into the large gap starting in file five at block 3,337 (though in the latest versions of Oracle it might move to file 6 – my test is only on 10.2.0.3) and I will be able to shrink the file down to block 13,832. However, if I also rebuild the three indexes ggp_pk, gp_pk and p_pk, they too will (probably) move into the big gap, and I will be able to shrink file five right down to somewhere near block 4,000. I’ll demonstrate in two steps, first moving the table and then moving the indexes; and after each change I’ll show just the last few lines of the report again.

FILE_ID    BLOCK_ID   END_BLOCK OWNER      SEGMENT_NAME    SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
      5       2,185       2,312 TEST_USER  PARENT          TABLE
              2,313       2,440 TEST_USER  PARENT          TABLE
              2,441       2,568 TEST_USER  PARENT          TABLE
              2,569       2,696 TEST_USER  PARENT          TABLE
              2,697       2,824 TEST_USER  PARENT          TABLE
              2,825       2,952 TEST_USER  PARENT          TABLE
              2,953       3,080 TEST_USER  PARENT          TABLE
              3,081       3,208 TEST_USER  PARENT          TABLE
              3,209       3,336 TEST_USER  PARENT          TABLE
              3,337      13,448 free       free
             13,449      13,576 TEST_USER  GGP_PK          INDEX
             13,577      13,704 TEST_USER  GP_PK           INDEX
             13,705      13,832 TEST_USER  P_PK            INDEX
             13,833      32,648 free       free

      6           9         136 TEST_USER  GENERATOR       TABLE
                137         264 TEST_USER  NUMTEST         TABLE
                265       8,072 free       free

After the first step, table numtest is now in file six – my prediction of filling the gap in file five was wrong; Oracle changed the code for segment allocation some time ago to make sure that you don’t end up with the first extent of every segment in the first file of a tablespace – but that change must have appeared a little earlier than I remembered. (If I had wanted to make sure my prediction was correct I could have resized file six to its minimum before I did the move – or I could have gone back and retro-fitted my prediction, of course.)

FILE_ID    BLOCK_ID   END_BLOCK OWNER      SEGMENT_NAME    SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- -----------------
      5       2,185       2,312 TEST_USER  PARENT          TABLE
              2,313       2,440 TEST_USER  PARENT          TABLE
              2,441       2,568 TEST_USER  PARENT          TABLE
              2,569       2,696 TEST_USER  PARENT          TABLE
              2,697       2,824 TEST_USER  PARENT          TABLE
              2,825       2,952 TEST_USER  PARENT          TABLE
              2,953       3,080 TEST_USER  PARENT          TABLE
              3,081       3,208 TEST_USER  PARENT          TABLE
              3,209       3,336 TEST_USER  PARENT          TABLE
              3,337      32,648 free       free

      6           9         136 TEST_USER  GENERATOR       TABLE
                137         264 TEST_USER  NUMTEST         TABLE
                265         392 TEST_USER  P_PK            INDEX
                393         520 TEST_USER  GP_PK           INDEX
                521         648 TEST_USER  GGP_PK          INDEX
                649       8,072 free       free

When I rebuilt the indexes p_pk, gp_pk and ggp_pk (in that order) they all moved to file six, allowing me to reduce the size of file five dramatically. In passing, this type of moving and rebuilding tends to work very well in locally managed tablespaces (LMTs) because an object that is recreated tends to use up the empty space from the start of the file. The same is not true of dictionary managed tablespaces (DMTs) which use up whichever free extents happen to be cached in the dictionary cache (v$rowcache) under dc_free_extents, and then the free extents of the right size that happen to appear first in a query against sys.fet$.

All that remains now is to resize the data files. This tablespace is using an 8KB block size, so checking the last used block in each file I’ll multiply by 8192 to get the new size of the data files. Here’s a quick cut-n-paste followed by one last listing from my script to show that there are no longer any free blocks at the end of the files:

SQL> alter database datafile 'C:\ORACLE\ORADATA\D10G\TEST_8K_B.DBF' resize 5308416;

Database altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\D10G\TEST_8K.DBF' resize 27328512;

Database altered.

FILE_ID    BLOCK_ID   END_BLOCK OWNER      SEGMENT_NAME    SEGMENT_TYPE
------- ----------- ----------- ---------- --------------- ------------------
      5       2,185       2,312 TEST_USER  PARENT          TABLE
              2,313       2,440 TEST_USER  PARENT          TABLE
              2,441       2,568 TEST_USER  PARENT          TABLE
              2,569       2,696 TEST_USER  PARENT          TABLE
              2,697       2,824 TEST_USER  PARENT          TABLE
              2,825       2,952 TEST_USER  PARENT          TABLE
              2,953       3,080 TEST_USER  PARENT          TABLE
              3,081       3,208 TEST_USER  PARENT          TABLE
              3,209       3,336 TEST_USER  PARENT          TABLE

      6           9         136 TEST_USER  GENERATOR       TABLE
                137         264 TEST_USER  NUMTEST         TABLE
                265         392 TEST_USER  P_PK            INDEX
                393         520 TEST_USER  GP_PK           INDEX
                521         648 TEST_USER  GGP_PK          INDEX

To be investigated: what if you’ve use the dbms_space_admin.tablespace_migrate_to_local() procedure to convert a dictionary manged tablespace (DMT) to a locally managed tablespace (LMT) and Oracle has had to create the space management bitmap near the end of the file. Is there a way to see the map as an extent (would it show up as segment of type “SPACE HEADER”), and does dbms_space_admin.tablespace_relocate_bitmaps() work as advertised ?

Footnote:  These notes don’t apply to the undo,or temporary tablespaces, and generally you can’t apply the technique to the system tablespace and shouldn’t apply the technique to the sysaux tablespace because the objects you have to move are likely to be critical to the operation of the database.

Update 28th Jan 2012: If you drop an object and the recyclebin is enabled then you can get a contradictory result. The space appears as free space (in dba_free_space) in the report, but you won’t be able to shrink the data file to use that  ”not quite free” space until you purge the recyclebin manually. A pointer to the fact that you’re hitting this issue is that you may see two (or more) consecutive lines of the report showing “free” – each item in the recyclebin shows up as a separate “free” extent.

[Further reading on Fragmentation]