Oracle Scratchpad

February 6, 2010

Shrink Tablespace

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 5:31 pm BST Feb 6,2010

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]

30 Comments »

  1. Hi,

    Very nice explanation and the script.Waiting for the results of investigation :)

    Comment by Anand — February 6, 2010 @ 6:52 pm BST Feb 6,2010 | Reply

  2. Another great hint by JL.
    going to resize now…

    Comment by ciuoto — February 8, 2010 @ 9:12 am BST Feb 8,2010 | Reply

  3. Jonathan,

    Thanks for another good blog.

    Very good step by step clear instructions on how to take care of the tablespace resize along with the scripts.

    Always enjoy reading your blog.

    Thanks
    Aswath

    Comment by Aswath Rao — February 8, 2010 @ 3:32 pm BST Feb 8,2010 | Reply

  4. Nice one Jonathan!

    And I thought I was the only one who messed about with Extent-lists order-by-block-id to reclaim a few precious GBs (rumoured to cost so little, yet so valuable if you need them).

    Some of it reminds me of those nostalgic puzzles in Oracle 7.1.6, coalescing and looking for free-extents-big-enough to allow growth of the next-extent.

    Btw: Moving to another tablespace also works, if you have some space available. And compression of tables or indexes (even if temporarily, at a possible perf-penalty) can also help.

    Comment by pdv — February 8, 2010 @ 5:04 pm BST Feb 8,2010 | Reply

  5. Jonathan,
    another great hint, but this space problem before to resize datafile can cause excessive IO attivity with Oracle events DB scattered read and DB sequential read?
    thanks a lot
    Abe

    Comment by Abe — February 9, 2010 @ 8:59 am BST Feb 9,2010 | Reply

  6. Hi Jonathan:
    Would this offer any performance benefits?
    This is a kind of reorg of tables and indexes and if I my understanding is right, there is not a lot of benefit in trying to do this. Apart from resizing the size of teh tablespace which would help in reducing the backup times and may be help in execution of certain type of sql, are there any other benefits.

    Thank you
    Kumar

    Comment by Kumar — February 15, 2010 @ 6:20 am BST Feb 15,2010 | Reply

    • Kumar,

      Any physical re-arrangement of data may affect the performance. Rebuilding tables thoughtlessly may make them bigger, rebuilding them carefully will probably make them smaller – but may change the data cluster. Rebuilding indexes may make them bigger or smaller (though smaller more often than bigger).

      Packing things into smaller spaces may mean you can read them faster – but this may also mean that the optimizer arithmetic thinks a tablescan (or index fast full scan) and change of join order is more efficient than an earlier index range scan (and it may, or may not be right).

      Packing things into a smaller space may also increase contention for popular blocks on inserts, updates and deletes.

      Luckily most object rebuilds are a waste of effort (as far as performance goes) – so the probability of a disaster is relatively low, and the risk of trying to pack an entire tablespace may be acceptable.

      As with many things in Oracle, it’s a percentage game. You look for something that probably won’t do much harm – but then look carefully for the cases where there is a threat that needs special treatment.

      Comment by Jonathan Lewis — February 15, 2010 @ 1:54 pm BST Feb 15,2010 | Reply

  7. [...] Jonathan Lewis-Shrink Tablespace [...]

    Pingback by Blogroll Report 05/02/2009 – 12/02/2010 « Coskan’s Approach to Oracle — March 10, 2010 @ 2:28 am BST Mar 10,2010 | Reply

  8. [...] space at the end of the files as this will allow you to reduce the file size. (See, for example, this note on shrinking tablespaces.) Bear in mind, though, that moving objects around can introduce undesirable side effects – [...]

    Pingback by Fragmentation 2 « Oracle Scratchpad — July 24, 2010 @ 9:15 am BST Jul 24,2010 | Reply

  9. how to find the last used block of datafile ?

    Thanks
    Monto

    Comment by monto — November 1, 2010 @ 6:22 pm BST Nov 1,2010 | Reply

    • Monto,

      There is scope for arguing about the exact meaning of “the last used block” in the datafile – but in the report I’ve shown it’s the END_BLOCK of the last extent reported for the file.

      Comment by Jonathan Lewis — November 4, 2010 @ 7:50 am BST Nov 4,2010 | Reply

  10. Can you please provide the sql statements that you used for moving the table numtest from the existing block_id position to the free block_id?

    Thanks in advance

    Comment by jadav — November 11, 2010 @ 5:32 pm BST Nov 11,2010 | Reply

  11. Hi,

    Very good explanation. It really helps me in my env to reclaim space.

    Can I have the explanatio for “undo, temporary or system tablespaces.” Suggest me optimal method for these tablespace too.

    Thanks,
    Suchi

    Comment by suchi — May 25, 2011 @ 5:54 am BST May 25,2011 | Reply

  12. [...] Footnote on how to reclaim space: If you need to reclaim space, you have to start by making sure that the “potential” space is at the end of the object. [...]

    Pingback by Free Space « Oracle Scratchpad — September 19, 2011 @ 9:51 am BST Sep 19,2011 | Reply

  13. This is a great article. However, how would you move LOBSEGMENT types to free up space? I have several LOBSEGMENTS (SYS_LOB0000493391C00006$$) and want to move them to free up space.

    Please advise, and thanks!

    Comment by Landon Statis — December 7, 2011 @ 4:59 pm BST Dec 7,2011 | Reply

    • Landon,

      Moving lob segments is a pain, because they tend to be quite big and moving them is a lot of work.

      If they’re in ASSM tablespaces you may get lucky by shrinking them (shrink space), but this can generate a lot of undo and redo, and although it may free up some extents, the ones that are freed may not be at the critical end of the tablespace. For example:

      alter table test_lobs  modify lob(text_content) (shrink space);
      

      As far as I know, the only alternative is to move the table and its lob segments at the same time. For example:

      alter table test_lobs move 
      lob(text_content) store as (tablespace test_8k_assm);
      

      This command will rebuild the table in the current tablespace, and rebuild the lob segment and lobindex segment in tablespace test_8k_assm.

      Comment by Jonathan Lewis — December 11, 2011 @ 3:15 pm BST Dec 11,2011 | Reply

      • “Moving lob segments is a pain.”

        This is something of an understatement. :-)

        Comment by Christopher Osborne — March 18, 2013 @ 2:16 pm BST Mar 18,2013 | Reply

  14. Good scripts Jonathan thanks.

    One question – I have some blocks that are marked as ‘free’ by your script but rebuilding Index or moving tables into the tablespace does not use them. I have 50% free in the tablespace but cannot move segments into these free blocks so cannot reduce the datafile.

    e.g.

    11 250889 385544 free free

    11 385673 412936 free free

    11 413065 507912 free free

    Any idea why?

    Comment by MF — January 27, 2012 @ 2:01 pm BST Jan 27,2012 | Reply

    • MF,

      You might want to check the recyclebin.
      Objects which have been dropped but kept in the recyclebin appear in dba_free_space as free.

      I’ve added an update to the article to point this out.

      Comment by Jonathan Lewis — January 28, 2012 @ 10:50 am BST Jan 28,2012 | Reply

  15. Hi Jonathan ,

    I tried the above steps , I have my last free_space_block marked as 151672 and my dba_extents gives the maximum database block as 120448 .Now as you suggested I tried to reclaim (151672 -120448)* 8 which comes to around 243 MB – which from the total 1184 MB comes to about 942 MB . So I tried the following :-

    alter database datafile 8 resize 942M

    I get error :- ORA-03297: file contains used data beyond requested RESIZE value

    So I made it

    alter database datafile 8 resize 980M

    Still I get the same error .So for testing purposes I made it

    alter database datafile 8 resize 1184M same error .

    I am confused since dba_extents is not showing anything beyond 120448 block number and dba_free_extents is showing free blocks till 151672 .What could be the reason ?

    Comment by Ranjith — June 27, 2012 @ 5:25 am BST Jun 27,2012 | Reply

    • Ranjith,

      When solving problems you need to examine the evidence with care, and report your observations with extreme care.

      I don’t see any reference to a thing called “free_space_block” in my article – so how am I supposed to interpret your question ?

      I will take a guess (based partly on your later comment that there is a 1MB free extent at the end of the file) that you have some object(s) in the user_recyclebin or dba_recyclebin that you have not yet purged.

      Show us the last few lines of the report for each file in the tablespace, and I may produce another guess.

      You might also tell us which version of Oracle, and check that the block size is 8KB.

      Comment by Jonathan Lewis — July 3, 2012 @ 9:55 pm BST Jul 3,2012 | Reply

  16. Thanks for the article Btw …

    Comment by Ranjith — June 27, 2012 @ 5:25 am BST Jun 27,2012 | Reply

  17. Also I added up the space in dba_extents and dba_free_space for that particular database , it is 1 MB less than the total space shown allocated in dba_data_files for that tablespace .And this 1 MB appears to be at the end of the data file .

    Comment by Ranjith — June 27, 2012 @ 4:51 pm BST Jun 27,2012 | Reply

  18. [...] Shrink Tablespace [...]

    Pingback by Viewing Figures « Oracle Scratchpad — December 10, 2012 @ 10:52 am BST Dec 10,2012 | Reply

  19. Hello Johnathan

    If free space is scattered across a datafile, can we get them all moved towards the end of the file and reclaim space.

    Thank you
    Jonu

    Comment by Jonu Joy — July 9, 2013 @ 6:05 am BST Jul 9,2013 | Reply

    • Jonu Joy,

      Yes, but to do this you have to do things like moving the tables and rebuilding the indexes that are taking up space near the ends of the files. If you’re using locally managed tablespaces this tends to fill up the holes from the start of the files, allowing you to shrink the files bit by bit.

      Unfortunately it can be a long and tedious process, denying the system to the end-users as you do it. It is possible to justify the effort, of course, and then it’s just a case of trying to minimise the time you spend doing it – creating a whole new tablespace and moving lots of things into can be the quickest thing to do.

      Comment by Jonathan Lewis — July 10, 2013 @ 9:02 pm BST Jul 10,2013 | Reply

  20. […] query comes from this post and lists all extents in a given tablespace plus free space. What I want to get is to reduce number […]

    Pingback by start_of_group « Timur Akhmadeev's blog — July 21, 2013 @ 5:02 pm BST Jul 21,2013 | Reply

  21. Jon,
    Thank you so much for this article ..i have one quick question here as how did you move the table from one datafile from other datafile.?

    Comment by Subbu — September 26, 2013 @ 10:32 pm BST Sep 26,2013 | Reply

    • Is there any reason why you haven’t used my proper name ?

      If you read the article carefully you will note that I was surprised that the table ended up where it did – you cannot control which file of a tablespace an object will move to when you move or rebuild it (unless you’ve managed to ensure that there is no alternative space for it to go to).

      Comment by Jonathan Lewis — September 27, 2013 @ 5:55 pm BST Sep 27,2013 | Reply

  22. […] Shrink Tablespace […]

    Pingback by Viewing Figures | Oracle Scratchpad — October 8, 2013 @ 5:34 pm BST Oct 8,2013 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,514 other followers