Oracle Scratchpad

Tablespace HWM

A simple script to list all the used extents and free space in a tablespace, ordered by file id and block id. You have to add your own page,  column and break settings. This could be a useful driving query for code to reduce the file sizes of a tablespace after taking some action that you hope will free up some space in the tablespace.

See the pingback in comment #1 for an article about using this script. Note that any query against dba_extents is fairly expensive when the tablespace is locally managed because of the implementation of the x$ktfbue stucture that represents the used extents: you will see a TT lock, a recursive query against seg$ and (possibly) a physical disk read (of the segment header block) for each segment in the tablespace.

rem
rem Script:  ts_hwm.sql
rem Author:  Jonathan Lewis
rem Dated:  Feb 2010
rem Purpose:
rem
rem Last tested
rem  11.2.0.3
rem  11.1.0.7
rem  10.2.0.3
rem Not tested
rem  10.2.0.5
rem   9.2.0.8
rem   8.1.7.4
rem Not relevant
rem
rem Notes:
rem Quick and dirty to list extents in a tablespace
rem in file and block order.
rem
rem For LMTs, expect to acquire one TT lock per segment
rem in the tablespace, and to query seg$ once for each
rem segment in the tablespace.  This is a side effect of
rem the mechanism invoked by accessing x$ktfbue. Also
rem assume that you will do one phsyical block read per
rem segment (reading the segment header block for the
rem extent map) as this is also part of the implementation
rem of x$ktfbue.
rem
rem Watch out for objects in the recyclebin - they will show
rem up as FREE in dba_free_space, but will stop you from
rem resizing the tablespace until you purge them. Depending
rem on version of Oracle you may get some clues about this
rem because each "free" extent in the recyclebin is reported
rem as a separate extent by dba_free_space.
rem

define m_tablespace = 'TEST_8K'

select
	file_id,
	block_id,
	block_id + blocks - 1	end_block,
	owner,
	segment_name,
	partition_name,
	segment_type
from
	dba_extents
where
	tablespace_name = '&m_tablespace'
union all
select
	file_id,
	block_id,
	block_id + blocks - 1	end_block,
	'free'			owner,
	'free'			segment_name,
	null			partition_name,
	null			segment_type
from
	dba_free_space
where
	tablespace_name = '&m_tablespace'
order by
	1,2
/

14 Comments

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

    Pingback by Shrink Tablespace « Oracle Scratchpad — February 6, 2010 @ 5:32 pm BST Feb 6,2010

  2. [...] an article with a script to count the number of rows in each partition of a partitioned table tablespace usage: a script to list the extents and free space chunks in a tablespace in file and block [...]

    Pingback by Simple scripts « Oracle Scratchpad — February 6, 2010 @ 5:43 pm BST Feb 6,2010

  3. Hi,
    I have got a table of size 47gb and segment advisor shows that I’ll to relcaim unused space upto 5gb. I did enable row movement and then shrink space compact, and shrink space, it only release couple of MB’s When I run dbms_space.space_usage, the output shows:
    Unformatted Blocks = 0
    Blocks with 00-25% free space = 25
    Blocks with 26-50% free space = 25
    Blocks with 51-75% free space = 30
    Blocks with 76-100% free space = 6142
    Full Blocks = 6263406

    PL/SQL procedure successfully completed.

    But still I am unable to reclaim the unused space. Please help me, it seems like some problem at the level of how row data is distributed across the extents.

    regards
    Raj

    Comment by Rajkumar Patel — September 24, 2010 @ 10:22 am BST Sep 24,2010

    • At first sight the figures don’t appear to be self-consistent. But don’t forget you can still set PCTFREE for tables in an ASSM tablespace, and the default is 10%.

      The shrink command basically deletes and re-inserts rows, and row-insertion obeys PCTFREE – so it’s not really surprising that you show 5GB of free space on a table that is approaching 50GB.

      If you’ve got a backup copy of the database you could test this hypothesis by setting PCTFREE to zero and seeing what happens if you shrink space compact again. Don’t do it on production – it may generate a HUGE amount of undo and redo, and the impact on subsequent queries could be catastrophic.

      (Of course, without being familiar with the code used by the segment space advisor we can’t tell whether it’s already allowed for things like PCTFREE and INITRANS – but you could create a few extreme scenarios to check this.)

      Comment by Jonathan Lewis — September 25, 2010 @ 9:00 am BST Sep 25,2010

    • Hello Jonathan ,
      What solution you may suggest if the Shrink operation is not the best solution?
      Moving the data into another tablespace and then drop the original tablespace ?

      Thank you for your help,
      Wissem

      Comment by orawiss — September 25, 2010 @ 9:16 am BST Sep 25,2010

    • If you run the shrink command with cascade it will reclaim the free space.

      Regards,
      Nooresh

      Comment by Nooresh Bajracharya — January 4, 2011 @ 3:29 pm BST Jan 4,2011

      • Nooresh,

        Could you clarify what you mean – as far as I recall the “cascade” option applies when shrinking tables and directs Oracle to shrink dependent objects. It’s not relevant to the question from Rajkumar.

        A late thought on his question though is whether the shrink command did anything, or whether it was silently ignored. There are several restrictions on it, and it’s possible that some restrictions don’t report an error. From the 10.2 SQL Reference manual:

        Restrictions on the shrink_clause The shrink_clause is subject to the following restrictions:

          - You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
          - Segment shrink is not supported for tables with function-based indexes or bitmapjoin indexes.
          - This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
          - You cannot specify this clause for a compressed table.
          - You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

        Comment by Jonathan Lewis — January 5, 2011 @ 10:12 am BST Jan 5,2011

        • My initial thought was reclaming the unused space and when i ran “shrink space compact” i found that it delete and reinsert the rows but it doesn’t reclaim the unused space. At the same time if i use the “cascade” option it will reclaim the spaces.

          SQL> exec dbms_stats.gather_table_stats('NOORESH','G1');
          
          PL/SQL procedure successfully completed.
          
          SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='G1';
          
              BLOCKS EMPTY_BLOCKS   NUM_ROWS
          ---------- ------------ ----------
                7068            0     508592
          
          SQL> select blocks, extents from dba_segments where segment_name='G1';
          
              BLOCKS    EXTENTS
          ---------- ----------
                7168         71
          
          SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from G1;
          
          used blocks
          -----------
                 6999
          
          SQL> DELETE FROM G1 WHERE ROWNUM  select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from G1;
          
          used blocks
          -----------
                 4127
          
          SQL> select blocks, extents from dba_segments where segment_name='G1';
          
              BLOCKS    EXTENTS
          ---------- ----------
                7168         71
          
          SQL> ALTER TABLE G1 ENABLE ROW MOVEMENT;
          
          Table altered.
          
          SQL> ALTER TABLE G1 SHRINK SPACE COMPACT;
          
          Table altered.
          
          SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from G1;
          
          used blocks
          -----------
                 4129
          
          SQL> exec dbms_stats.gather_table_stats('NOORESH','G1');
          
          PL/SQL procedure successfully completed.
          
          SQL> select blocks, extents from dba_segments where segment_name='G1';
          
              BLOCKS    EXTENTS
          ---------- ----------
                7168         71
          
          ----- While using Cascade Option
          
          SQL> TRUNCATE TABLE G1;
          
          Table truncated.
          
          SQL> insert into g1 select * from  g1
          
          816560 rows created.
          
          SQL> exec dbms_stats.gather_table_stats('NOORESH','G1');
          
          PL/SQL procedure successfully completed.
          
          SQL> select blocks, extents from dba_segments where segment_name='G1';
          
              BLOCKS    EXTENTS
          ---------- ----------
               23552         94
          
          SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from G1;
          
          used blocks
          -----------
                22383
          
          SQL> delete from g1 where rownum  commit;
          
          Commit complete.
          
          SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from G1;
          
          used blocks
          -----------
                15531
          
          SQL> alter table g1 shrink space cascade;
          
          Table altered.
          
          SQL> exec dbms_stats.gather_table_stats('NOORESH','G1');
          
          PL/SQL procedure successfully completed.
          
          SQL> select blocks, extents from dba_segments where segment_name='G1';
          
              BLOCKS    EXTENTS
          ---------- ----------
               15704         87
          

          Comment by Nooresh Bajracharya — January 5, 2011 @ 3:58 pm BST Jan 5,2011

        • Nooresh,

          Thanks for coming back on this.
          “shrink space compact” moves the rows from the end of the table to the beginning of the table, but doesn’t drop the highwater mark. “shrink space” drops the highwater mark after compacting. (This also requires a lock to be taken on the table, of course).

          When you used “shrink space cascade” you were explicitly requesting that oracle tidy up the indexes as well, but implicitly (because there was no compact) telling it to drop the highwater mark.

          Comment by Jonathan Lewis — January 5, 2011 @ 9:31 pm BST Jan 5,2011

  4. Thank you for the Clarification.

    Regards,
    Nooresh

    Comment by Nooresh Bajracharya — January 5, 2011 @ 9:36 pm BST Jan 5,2011

  5. [...] other way to confirm is to use Jonathan Lewis’s query from this post to list all the used extents and free space in a [...]

    Pingback by OraExplorer » RMAN Duplicate Failed – Need to Format Corrupt Block Not Part of Any Segment — January 12, 2011 @ 3:56 am BST Jan 12,2011

  6. Thanks for this post. I am trying to write a script to shrink un-used files in a particular Table Space to 50MB. The file system is ASM shared storage on exadata environment. If you could share your thoughts with a sample script, it would be grateful.

    Appreciate your help.

    Comment by Hersch — November 2, 2011 @ 9:37 pm BST Nov 2,2011

    • Hersch,

      I don’t understand where the problem might be with this – unless Exadata does something strange.

      The basic command for each file would be something like:

      alter database datafile '+DATA/linux/datafile/fpw_idx.277.766339579' resize 200M;
      

      If this works you need only do something like:

      begin
          for r in (
              select  file_name
              from    dba_data_files
              where   tablespace_name = 'FPW_IDX'
          ) loop
              execute immediate
                  'alter database datafile ''' || r.file_name || ''' resize 50M';
          end loop;
      end;
      /
      

      Obviously you may want to add an exception handler to trap and report error ORA-03297 (file contains used data beyond requested RESIZE value)

      Comment by Jonathan Lewis — November 13, 2011 @ 3:37 pm BST Nov 13,2011


RSS feed for comments on this post.

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers