Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Prince.

Asked: July 17, 2002 - 3:09 pm UTC

Last updated: July 13, 2011 - 1:24 pm UTC


Viewed 10K+ times! This question is

You Asked

Dear Tom,

I have a table which has more than 90% of the rows deleted and never will be used. I would like to reclaim the space. Currently it has 133 extents (LMT with uniform size of 512K). It has data less than 512K.

What is the straight forward way to unallocate all the extents but a few. Also accidently it got 30 initial extents allocated. (Once allocated, can the initial extents be deallocated?)

I am thinking of using the "alter table move..." to achieve the above two (resize the space and reduce the initial extents). Is there any better way?

Also, when I try your show_space procedure, I get the following error (Only if I try to get the space of an object owned by another user)
Note: I am logged in as an user with DBA privs.

DBUSR> exec show_space('PRENTRY','PARTNER_OWNER','TABLE') ;

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "DBUSR.SHOW_SPACE", line 20
ORA-06512: at line 1

Name Null? Type
----------------------------------------- -------- ----------------------------

DBUSR> select * from tab where tname like 'T%';

------------------------------ ------- ----------

DBUSR> exec show_space('DBUSR','TBL_SPA','TABLE') ;
BEGIN show_space('DBUSR','TBL_SPA','TABLE') ; END;

DBUSR> exec show_space('TBL_SPA','DBUSR','TABLE') ;
Free Blocks.............................2
Total Blocks............................32
Total Bytes.............................131072
Unused Blocks...........................16
Unused Bytes............................65536
Last Used Ext FileId....................5
Last Used Ext BlockId...................209
Last Used Block.........................16

PL/SQL procedure successfully completed.

and Tom said...

alter table move is the BEST way to do it. You can specify all new storage parameters at that point if you want.

The owner of show_space (DBUSR) doesn't have DIRECT access to the table in question. You might consider using AUTHID CURRENT_USER if you want to install the procedure in dbusr and use it in other schemas.


  (26 ratings)

Is this answer out of date? If it is, please let us know via a Comment


DBMS_SPACE.UNUSED_SPACE in the standalone mode works.

Prince, July 17, 2002 - 3:52 pm UTC


If I just execute DBMS_SPACE.UNUSED_SPACE as an anonymous block it works fine.

Doesn't the DBMS_SPACE obtain the space info from the dict tables? Why do I need to have the privilege to access the other user's table directly, if that is the case?

Also, once allocated more initial extents (say 5120K on 512K uniform sized LMT), can the unused extents be deallocated, by using deallocate unused?


1 declare
2 segment_owner VARCHAR2(30) default 'PARTNER_OWNER';
3 segment_name VARCHAR2(30) default 'PRENTRY';
4 segment_type VARCHAR2(30) default 'TABLE';
5 total_blocks NUMBER;
6 total_bytes NUMBER;
7 unused_blocks NUMBER;
8 unused_bytes NUMBER;
9 last_used_extent_file_id NUMBER;
10 last_used_extent_block_id NUMBER;
11 last_used_block NUMBER;
12 partition_name VARCHAR2(30) DEFAULT NULL;
13 begin
15 segment_owner ,
16 segment_name ,
17 segment_type ,
18 total_blocks ,
19 total_bytes ,
20 unused_blocks ,
21 unused_bytes ,
22 last_used_extent_file_id ,
23 last_used_extent_block_id ,
24 last_used_block ,
25 partition_name );
26 dbms_output.put_line('total blk : ' || total_blocks );
27 dbms_output.put_line('Unused blk : ' || unused_blocks );
28 dbms_output.put_line('last unused blk : ' || last_used_block );
29* end ;
12:32:36 DBUSR> /
total blk : 17024
Unused blk : 33
last unused blk : 95

PL/SQL procedure successfully completed.

Tom Kyte
July 18, 2002 - 5:31 am UTC


roles are not enabled in the stored procedure -- they are in the anonymous block.  your user has the ability to see the table via a role, not a direct grant.  Use authid current_user to make this a better general purpose solution.

We try not to bypass security in any case, so yes, the owner of the procedure does need access (it is not a simple dictionary read)

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int ) storage (initial 200k);

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from user_extents where
  2  segment_name = 'T';


ops$tkyte@ORA920.US.ORACLE.COM> alter table t deallocate unused;

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from user_extents where
  2  segment_name = 'T';


I got the answer to my second question.

Prince., July 17, 2002 - 4:51 pm UTC


I could reclaim the initail extent, if the extra extents were never used.


I got it now

Prince., July 18, 2002 - 1:19 pm UTC

Regarding the second case, I used "keep 0K" in my "deallocate unused "command.

initial extent

Reader, August 28, 2003 - 12:58 pm UTC

If my table has initial extent say 100M, and i have not inserted any data in it, If I used the command deallocate unused, can I reclaim all the space except a block for block header info from the extent? Thanks.

Tom Kyte
August 29, 2003 - 8:03 am UTC

In a DMT, perhaps:

ops$tkyte@ORA817DEV> create table t ( x int ) tablespace system storage ( initial 10m );
Table created.
ops$tkyte@ORA817DEV> select blocks from dba_extents where segment_name = 'T' and owner = user;
ops$tkyte@ORA817DEV> alter table t deallocate unused keep 0k;
Table altered.
ops$tkyte@ORA817DEV> select blocks from dba_extents where segment_name = 'T' and owner = user;

In an LMT, you will be constrained by the uniform or system allocated extent size as all extents are the same or system managed size. 

what is AUTHID

keyur, July 17, 2006 - 5:06 pm UTC

I have same error I didnt get what is authid? would you please let me know.


Tom Kyte
July 18, 2006 - 7:58 am UTC

it lets you do "invoker rights" instead of "definer rights" in the stored procedure.

</code> <code>

reclaiming space

Mark, July 18, 2006 - 10:38 am UTC

As I've mentioned in other threads, I've been working with the PostgreSQL database lately. We commonly have to perform large merges (on Postgres we're forced to do slow-by-slow update/insert using a loop and if/then) on a table in the million+ rows ballpark.

Under the Postgres hood, an update does a delete followed by an insert, thus leaving behind "dead tuples" and so the space needs to be reclaimed. The VACUUM operation must be run after a large update, to clean up these dead duples, lest performance suffer. So when my large merge is done, and most of the rows happen to be updates, the VACUUM operation takes a whopping 17 hours to reclaim everything. We will soon be throwing hardware at the problem. :(

I hope this isn't an oversimplified question, but does Oracle's update require this kind of space reclamation? i.e. does update do a delete/insert under the hood?

Thanks a lot,

Tom Kyte
July 19, 2006 - 8:10 am UTC

no, it does not.


Mark, July 19, 2006 - 2:05 pm UTC

Thanks Tom, another point for Oracle. :)

It seems their reasoning for implementing update as delete/insert is that their multiversioning system needs the dead tuples. If User1 updates a row and doesn't commit, then User2 queries the same row, User2 reads the dead tuple.

They do not have an undo tablespace.

I'm not sure I can see any pros to this method, aside from (possibly) ease of implementation. The cons I see are: 1) (the big con) having to vacuum up the dead tuples, as noted in last post; 2) cannot manage undo separate from real tables (e.g. separate disks)

database free space

A reader, October 11, 2006 - 6:45 pm UTC

Hi Tom,
Our customer has asked us to free up as much free space from their database because of the amount of money they pay weekly for disk storage amount. Funny - but they are really serious.

SQL> select distinct EXTENT_MANAGEMENT from dba_tablespaces;


SQL> select sum(bytes/1024/1024/1024) from dba_extents;


SQL> select sum(bytes/1024/1024/1024) from dba_data_files;


SQL> select sum(bytes/1024/1024/1024) from dba_free_space ;


Now I know dictionary managed tablespaces more likely than not have fragmentation issues. My questions:-
1) Isen't 95.1073685 GB the actual amount of data in the datafiles? 
2) 21.5 GB is the free space in the datafiles. Is it possible that I do a whole database export and import no more space will be freed? 
3) Is there a way of pinpointing to the customer how much space reclaimation there will be in carrying out various 
exercises for reclaimation??

Thanks as always Tom,
You're the BEST'est.

Tom Kyte
October 11, 2006 - 8:22 pm UTC

1) yes
2) it is entirely possible that you export and import and end up taking MORE space (as you reintroduce pctfree to all segments). It is possible that:
a) you will take MORE space
b) you will take LESS space
c) you will take SAME space

3) have you run maxshrink (search site for this), free space back right now
other than that, look at "alter table T move new_tablespace (using LMT!!! system allocated extents!!!) and alter I rebuild new_tablespace

and dropping old datafile, DO NOT USE EXPORT FOR THIS, PLEASE DON'T

More help plsease.

Aru, February 14, 2007 - 5:26 pm UTC

Hi Tom,
Thanks for you help.

Now I am thinking of not doing exp/imp but the method you suggested above. Will create LMT's and move objects and rebuild indexes.
But :-
The tablespaces in question have got segments that have got pct increase for next extent as 30% or 50% and there are more than 400 of them.
How can I do a 'alter table move' with the old storage parameters ?
Or do I need another approach to this?
Tom Kyte
February 15, 2007 - 9:50 am UTC

you don't want the old storage, you want to WIPE OUT the storage parameters (pctincrease and the like are only meaningful during the first allocation in a locally managed tablespace - ignored after that)

alter table t
move tablespace LMT
storage ( initial 1k next 1k pctincrease 0 minextents 1 );

just use that.


Aru., February 15, 2007 - 3:16 pm UTC

Great !!
Thanks Tom,


Aru, February 18, 2007 - 3:13 pm UTC

Hi Tom,
Above you said:-
"3) have you run maxshrink (search site for this), free space back right now other than that, look at "alter table T move new_tablespace (using LMT!!! system allocated
extents!!!) and alter I rebuild new_tablespace

When one does 'alter table t move' in same tablespace suppose, what really happens? I mean, does the tablespace get created in somewhere "front" of the tablespace and then resize will work (if T was the table that was at the "back" as maxshrink indicated)?
Tom Kyte
February 18, 2007 - 8:13 pm UTC

the alter table T move will

a) try to use any existing free space in the tablespace to copy the existing data (the existing data will exist until the entire segment is copied)

b) extend the tablespace by growing the datafiles if it runs out of space

Where does the space come from?

Robert, March 14, 2007 - 3:19 pm UTC


I am rebuilding all global indexes on a partitioned table after repartitioning (splitting partitions) it.
After the rebuild some of the indexes have shrunk almost by half! (e.g from 13G to 8G).

Where is this 'free space' coming from?


Tom Kyte
March 15, 2007 - 7:26 am UTC

hard to say for definite as we don't know the entire history of this index, but...

these are all guesses

o you are rebuilding the index and reintroducing the original pctfree of 10% (by default). You are taking blocks that did 60/40 splits (so one is 40% free and the other is 60% free) and squishing them down

o you did parallel operations, they each allocate their own extents, there was lots of extra left over in each extent. when you rebuilt, you did not do parallel, hence the left over space was modest

o you over allocated the index space in the first place, then when you rebuilt, you did not.

Reclaim Disk Space

Parag, November 26, 2008 - 12:15 pm UTC

Hi Tom,

Its always been very enlightening reading ur posts.
I have a 3TB OLTP Critical Production Database and my PRIMARY requirement is to release space at disk level.
DB -
I have some Huge tablespaces(~400GB) which are almost 90% used-up.
Q1.) What is the best way to do it?
If Reorg then -
a.Alter Table T move,alter index I rebuild?(On Bg Tables & Indexes).Does alter guarantee reduction of HWM to used-up point if used-up point is less than HWM?Does the above guarantee space reclaimation?After that shrink datafiles?
b.Export/Import at Schema Level?
c.) Any other best method recommended?
Q2.) How can i approximate beforehand on the amount of space i can reclaim if i do a reorg?
Your response is deeply appreciated.
Tom Kyte
November 28, 2008 - 4:27 pm UTC

you should read this one for sure then:

q1) what is the best way to do it?

that isn't a question really - are you asking

"what is the best way to reorg"?

If so, most all of the time the answer is:

"by not doing it, you don't need to"

You give almost no information here - all of the useful information is for sure missing.

So, this huge tablespace is 90% full. Why do you believe you need to reorganize anything. What is the goal, what are you HOPING to achieve and WHY do you think a reorganization of the data will accomplish that?

and bear in mind, I am very sure that after you provide that, I will have more questions and then more and then more...

Reclaim Disk Space

Parag, November 26, 2008 - 12:31 pm UTC

Hi Tom,

Just further update on my above query.
My System Tablespace is DMT.All Other Tablespaces are LMT.


Parag, November 28, 2008 - 6:06 pm UTC

Hi Tom,

I'm sorry i cudn't explain my question properly.My Motive is to reclaim space at disk level.
Q1. I meant what is the best way to reclaim space at disk level?
I am basically looking for methods/options i have to reclaim space at disk level-
a.) First Reorg and then Shrink Datafiles?
b.)Exp/Imp. at schema level?
c.)ANY OTHER best method to reclaim space at disk level which you suggest?

Details -
My System Tablespace is DMT.All Other Tablespaces are LMT.

Below are my TOP 5 tablespae details-
Schema Tblsp Phy.Size(GB)LogSize(GB)Free(GB) Used (%)
A D 170 166 4 97.99
I 250 245 5 98.5
B D 309 306 7 98.95
I 595 591 4 99.42
C D 234 179 55 76.54
I 271 158 113 58.34
D D 122 66 56 54.43
I 37 15 22 42.47
D D 333 328 5 98.3
I 113 107 6 94.55

D- Data Tablespace
I - index Tablespace
Q2.) Does reorg guarantee the lowering of HWM to the used point(in case table/index is not used till HWM at that point of time)?I mean, if it lowers the HWM,then we can do reorg which would lower the HWM and then shrink datafiles to the new lowered HWM and reclaim space at disk level.
Q3.) Does Reorg guarantee improved performance and in which scenario?
Q4.) How to calculate the amount of Disk Space i can reclaim after reorg(IF i go for a reorg)?

Thanks Tom as always
Tom Kyte
December 01, 2008 - 5:51 am UTC

they only question that I can answer definitively is q3)

q3) of course not. one of three things will happen after any change. 1) it will not affect performance at all. 2) things will go faster. 3) things will go slower.

ok, maybe two of these

q2) no, it does not. It might make somethings GROW in size. Yes, you read that right - you could take a segment - reorganize it and discover that it is larger afterwards. Why? Because you might take a very densely packed table or index - which is very full - reorganize it and re-introduce the pctfree - making the segment larger than it used to be.

q3) one thing you might do is search this site for maxshrink and see if you can shrink existing datafiles (if you are on 10g, you might want to purge your recycle bin first! but make sure you know what that means...)

q1) there is no best way

if there were a single best way, we would not have invented or implemented the others.

you would have to go segment by segment in these tablespaces to see the 'percentage of free space' to see if they are candidates for 'shrinking' or not.

You could use your knowledge - why do you think you will get lots of free space back? If you mostly insert and update - you won't. If you insert just a little more than you delete - you will not.

You can either use the segment advisor in Enterprise manager to find segments that might be candidates for a 'reorg'

Or you can use show_space to see how "compact" your segments are:

set define off

create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    l_segment_space_mgmt        varchar2(255);
    l_unformatted_blocks number;
    l_unformatted_bytes number;
    l_fs1_blocks number; l_fs1_bytes number;
    l_fs2_blocks number; l_fs2_bytes number;
    l_fs3_blocks number; l_fs3_bytes number;
    l_fs4_blocks number; l_fs4_bytes number;
    l_full_blocks number; l_full_bytes number;

 -- inline procedure to print out numbers nicely formatted
 -- with a simple label
    procedure p( p_label in varchar2, p_num in number )
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              to_char(p_num,'999,999,999,999') );
   -- this query is executed dynamically in order to allow this procedure
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   -- via a role as is customary.
   -- NOTE: at runtime, the invoker MUST have access to these two
   -- views!
   -- this query determines if the object is a ASSM object or not
      execute immediate 
          'select ts.segment_space_management
             from dba_segments seg, dba_tablespaces ts
            where seg.segment_name      = :p_segname
              and (:p_partition is null or 
                  seg.partition_name = :p_partition)
              and seg.owner = :p_owner
              and seg.tablespace_name = ts.tablespace_name'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner;
       when too_many_rows then
          ( 'This must be a partitioned table, use p_partition => ');

   -- if the object is in an ASSM tablespace, we must use this API
   -- call to get space information, else we use the FREE_BLOCKS
   -- API for the user managed segments
   if l_segment_space_mgmt = 'AUTO'
     ( p_owner, p_segname, p_type, l_unformatted_blocks,
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
     p( 'Unformatted Blocks ', l_unformatted_blocks );
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
     p( 'Full Blocks        ', l_full_blocks );
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);
     p( 'Free Blocks', l_free_blks );
  end if;

  -- and then the unused space API call to get the rest of the 
  -- information
  ( segment_owner     => p_owner,
    segment_name      => p_segname,
    segment_type      => p_type,
    partition_name    => p_partition,
    total_blocks      => l_total_blocks,
    total_bytes       => l_total_bytes,
    unused_blocks     => l_unused_blocks,
    unused_bytes      => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,

    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
set define on

now if the goal is to ultimately shrink datafiles - there is no 'best way' to do that in place.

If you used alter table t shrink space/shrink space compact - you'll reorganize the tables, but you'll not free up space at the end of the datafile, rather you'll take a datafile that looked like this (each 'word' is an extent in the following string - from the first extent of a datafile to the last)


so, that file has extents for tables T1-T6 and three free extents at the end (assuming locally managed uniform for ease of example) If you resized the file, you could make it three extents smaller. Now, lets assume you shrank each table - and each table fit in one extent, then:


You now have nine free extents in the file, but if you shrank the file, you would only still get three - since t6 is "at the end"

So, what you would need to do to reclaim "as much as you can" would be to MOVE the segements to a new, empty tablespace - that would pack them densely and allow you to DROP the old tablespace.

The odds that this exercise is even worth considering (the entire "lets get disk space back") are so so so so very close to 0% that I would strongly encourage you to find something else to do :)

Think about it - for how many hours or days would this space be 'reclaimed' and freed up before the database just wanted it back again.

Parag, December 05, 2008 - 7:10 am UTC

Hi Tom,

Thanks for your valuable inputs.
If we go for the option - "MOVE the segments of concerned Tablespace to a new, empty tablespace - that would pack them densely and allow you to DROP the old tablespace.",-
Q1.) Kindly let me know the steps involved in the same.
Q2.) Can we do the above method both for Custom as well as standard Schemas?
Q3.) Do we need to move the segments back to the OLD Tablespace from the New Tablespace,considering any dependency of Application or otherwise?Is the answer same for Standard as well as custom Schemas?
Q4.)How do we calculate the amount of space i can reclaim by following this method?Before we go for this method, we need an approximation of the amount of space we would reclaim by following this method.Ball Park would be fine.

Thanks Tom
Tom Kyte
December 09, 2008 - 12:08 pm UTC

q1) alter table T move tablespace <xxxx>; alter index I rebuild tablespace <xxxx>;

q2) what is a 'custom' schema and what is a 'standard' schema

q3) no, if the tablespace names need to be the same after as before, you can alter the tablespace and rename it.

q4) it could be more or less or the same. You'd probably want to gather statistics and then you can look at them - it'll tell you how "dense" things are. Or you can use dbms_space - to review the free space in an object - procedurally

Parag, December 10, 2008 - 4:01 pm UTC

Hi Tom,

Thanks for your valuable inputs.
Kindly help me in understanding the output of Show_space in order to infer whether a particular table is a candidate for reorg or not in terms of releasing space.
Eg -
Below is the show_space output for a ~27 GB Table

SQL> exec show_space ('ATABLE','AUSER','TABLE');
Free Blocks.............................        5
Total Blocks............................       3,528,448
Total Bytes.............................  28,905,046,016
Total MBytes............................   27,566
Unused Blocks...........................      412
Unused Bytes............................       3,375,104
Last Used Ext FileId....................    2,628
Last Used Ext BlockId...................  116,233
Last Used Block.........................      100

As we can see from the above output,there are only 5 blocks on free list,so not much space can be released,hence not beneficial if we do reorg for this table in terms of releasing space.Is this line of thinking correct?Is this the only thing we can infer from the show_space output in terms of going for a reorg for that segment for releasing space?

My Basic Motive is how to decide and infer from the output of show_space of a segment,whether that segment is candidate for reorg in terms of benefit in releasing space?

Also, i get below error in trying to execute show_space for a table of particular owner .Working fine for tables of other owners-

ORA-01403: no data found
ORA-06512: at "USER.SHOW_SPACE", line 44
ORA-06512: at line 1

What does the error mean and how to resolve this error?

Your help is greatly appreciated.

Thanks Tom

Tom Kyte
December 10, 2008 - 4:35 pm UTC

... As we can see from the above output,there are only 5 blocks on free list,so not
much space can be released,hence not beneficial if we do reorg for this table
in terms of releasing space. ...

well, my script only shows the FIRST free list, if you have multiple free lists, you'll want to tweak that code - but yes, if only five blocks were on the free lists

and you know what - what if this table said "I have 100,000 blocks on the freelist".

would that be a reason to reorg?

*probably not* - why not?

table is big
reorg will consume many resources
and the free space will probably be reused by the segment itself (meaning, you do not permanently reclaim space)

You should incorporate into your logic you KNOWLEDGE of how the tables are used, without that - you'll end up doing tons of work for nothing.

you have the code, what is on line 44 of the code you are using? For my personal copy it is:

 44        execute immediate
 45            'select ts.segment_space_management
 46               from dba_segments seg, dba_tablespaces ts
 47              where seg.segment_name      = :p_segname
 48                and (:p_partition is null or
 49                    seg.partition_name = :p_partition)
 50                and seg.owner = :p_owner
 51                and seg.tablespace_name = ts.tablespace_name'
 52               into l_segment_space_mgmt
 53              using p_segname, p_partition, p_partition, p_owner;
 54     exception
 55         when too_many_rows then
 56            dbms_output.put_line
 57            ( 'This must be a partitioned table, use p_partition => ');
 58            return;
 59     end;

if that is getting "no data found", then the object information you passed in was wrong.

Parag, December 12, 2008 - 3:46 pm UTC

Hi Tom,

How do we find out if we have multiple free lists?
And as you said..."well, my script only shows the FIRST free list, if you have multiple free lists, you'll want to tweak that code " to tweak this code?

Tom Kyte
December 12, 2008 - 4:19 pm UTC

query the dictionary - user_segments has a column "freelist.

You have the source code to show_space, you see the calls, you have the documentation, you have the ability to read the documentation and see what the calls I'm making do - therefore, you have everything you need to do what you want.

       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);

that would be the API you'd be interested in, see the freelist_group_id?

in fact, read about dbms_space if you have access to AWR and such (separately licensed packs from enterprise manager), there is a API in there that pretty much does this

Parag, December 17, 2008 - 2:45 am UTC

Hi Tom,

Thanks for your really useful updates,analysed and reached a conclusion that we cant reclaim much Disk Space.
I have a slightly different requirement now-
1.)I want to now know the tables which would benefit most by reorg in terms of PERFORMANCE.How do i go about this?
2.)How do we estimate time for complete reorg activity(alter table move,index rebuild,etc.)?Ball Park would be fine.

Thanks Tom as always
Tom Kyte
December 29, 2008 - 11:50 am UTC

1) this is a hugely complex question.

In general:

a) if you index access this, none really - a reorg would do nothing - IN GENERAL. The reason - you read an index to get a rowid, you take the rowid and read that file/block. It matters not really where that file/block are.

b) if you full scan this, and given that you said "we would not make the segment smaller", a reorg would not make the full scan faster.

(close your eyes, envision the MECHANICS - what has to happen - then you can many times answer these sorts of questions yourself...)

Now, to make the answer more complex.....

there a reorg of the disorganized table, using dbms_redefinition with the "order_by" parameter to sort the table by object_name into the organized table - would help THAT particular query (where object_name like ?). If you place data that is queried together on as few blocks as possible, you'll decrease the work we need to perform to retrieve that data.

However, remember - if you sort the table by OBJECT_NAME, you have probably "un-sorted" the table by CREATE_DATE. A table can be sorted one way really - so a reorg to help Peter might seriously and utterly damage Paul.

2) it completely depends. How fast is your IO, are there other users contending for this IO. How are you set for CPU. Can you use parallel? Is nologging an option.

And then - once you figure out all of the parameters - you benchmark. That is the only way I know to do it.

Parag, January 16, 2009 - 9:41 am UTC

Thanks Tom for your valuable inputs!


Table partitions are allocating too many extents than they should

Lisa, March 19, 2009 - 2:23 pm UTC

Hi, Tom,

 I have a partitioned that's created in a LMT tablespace with uniform size 128M, one of the partition (ER_P_2008_12_31) has only 13 rows, they are all inserted into the table via direct path load SQL*Loader, NO records in this table has been updated or deleted since they are loaded, but this single partition has allocated 5 extents! with 128M each, that's 600M for 13 rows!! Can you tell me why?

Here is the tablespace definition:
SQL> select * from dba_tablespaces where tablespace_name = 'LRG_DATA01';

------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ --------
----------- ---
LRG_DATA01 16384 134217728 134217728 1 2147483645 0 134217728 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED

And here is the partition information from dba_segments:
SQL> select PARTITION_NAME,SEGMENT_TYPE,tablespace_name ,initial_extent, min_extents, extents from dba_segments where segment_name = 'EMI_RAW';
ER_P_2008_12_31 TABLE PARTITION LRG_DATA01 134217728 1 5

Here is the information about the partition from dba_tab_partitions:
SQL> select TABLE_NAME,PARTITION_NAME,tablespace_name,pct_free,pct_used,num_rows,blocks, empty_blocks, avg_space, avg_row_len,last_analyzed from dba_tab_partitions where partition_name = 'ER_P_2008_12_31';

------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------ ---------- ----------- --------------
EMI_RAW ER_P_2008_12_31 LRG_DATA01 2 13 40926 34 15317 268 20090319102936

As you can see, with only 13 rows, and average row len 268, even if each row takes one block, they should all fit into the initial extent! In this same database, I see the same issue with other partitions too. But my test database doesn't have this problem, the only difference is, the tablespace has a uniform size of 4M in dev, instead of 128M extent size. Am I running into a bug???

My database version is:
Oracle Database 10g Enterprise Edition Release - 64bit Production

SQL> show parameters db_block_size

------------------------------------ ----------- ------------------------------
db_block_size integer 16384

Thank you very much for your help!! This is a new production database, and application support is loading up history data quickly. Since it's taking ten times the space as we estimated, we are running out of space quickly!


Tom Kyte
March 23, 2009 - 10:11 am UTC

you probably do not want uniform sizes

you probably want system allocated extents and the power of extent trimming

and please do not loose sleep over having lots of extents - it is really "ok"

space usage

VS, September 15, 2009 - 10:01 am UTC

Dear Tom,

good day to you, I need your help on below.

using dbms_space.space_usage,i found that one of our tables has around 3k block in the category fs4_blocks (Number of blocks that has at least 75 to 100% free space),there are block in category fs2_blocks and fs3_blocks also.

This table is subjected to inserts and deletes on daily basis, would it be wise to user "alter table move" on a weekly/daily basis to dealloacte unused block and avoid high number of blocks allocated to the table which affects full table scans, any other method you think might be helpful.

Thanking you for your time on this.

Tom Kyte
September 15, 2009 - 10:12 am UTC

why is this perceived to be a problem to you?

your inserts will use this space the next time you insert.

3,000 is just a number, it means nothing without context. If your table is 3,010 blocks - 3,000 is big. If your table is 300,000 blocks, 3,000 is tiny.

And do you actually full scan this table frequently?

and since you are using ASSM (automatic segment space management) I would not consider alter table t move at all - that would require index rebuilds and would be offline. alter table t shrink space compact; alter table t shrink space - maybe that, but only if there was a ACTUAL PROBLEM that needed to be solved.

thanks for the help

VS, September 15, 2009 - 10:45 am UTC

Dear Tom,

thanks for your help and time for the query above, what will be the best way to find out that the blocks get used by inserts, I mean how can I monitor this, out of 13k blocks alloacted to the table 3k are in the category of 75% to 100% free.

Some of the queries go for a full scan and it cant' be avoided unless business agrees to restrict data with some conditions that is in discussions, but till then I thought of using alter table move or as per your suggestion alter table shrink space after enabling row movement to reduce blocks read for full table scan.

Thanks for your time again.

Tom Kyte
September 15, 2009 - 11:00 am UTC

... what will be the best way to
find out that the blocks get used by inserts, ...

you know how big the table is today.
come back next month, next year, whatever.
is the table noticeable larger?
if not, it must be using the space.

but you still haven't even told us if 3,000 is

a) teeny tiny, noise, nothing to think about
b) big, huge, 90% of the existing table size

thanks for the quick response

VS, September 15, 2009 - 11:06 am UTC

Dear Tom,

thanks a lot for you time and quick response, but I mentioned in previous post that out of 13k blocks allocated to the table 3k is in 75% to 100% free category which makes it 23% of allocated blocks, should i just ignore this or should i monitor the growth.

Thanks once again.

Tom Kyte
September 15, 2009 - 11:35 am UTC

well, say they are about 20% full - so, you'll turn 4/5 blocks into one (say four into one). So, you'll go from 10k + 3k to 10k + 3k/4 or 10,750 blocks.

You'll be about 80% of original size.

So, you would

a) measure before shrink
b) gather information about the queries that full scan this table (number of IOs, CPU used)

c) shrink

d) measure after shrink (to verify)
e) gather information about the queries that full scan (to verify they do significantly less work)

f) repeat d and e for days on end to see if how OFTEN you want to do (c) - if at all (d & e might show to you "not worth it", or they might show to you "it goes right back where it was after 12 hours, not worth it)

reorg ?

A reader, July 06, 2011 - 11:03 am UTC

Looking at the post above,I am unable to draw conclusion if I need to reorg my table .I deleted several unwanted rows and thought I could reduce the space table occupied within its tablespace.I would need to perhaps do alter table t shrink space but have never done this command before and would like to know how I may be able to estimate time for this action.

Tom Kyte
July 08, 2011 - 11:51 am UTC

a delete will never give space back for use for other segments. A delete simply frees up space in a segment for further reuse in that segment.

If you just deleted a large percentage of your table

And you want to be able to use that space for another segment (table/index/etc)

THEN - you can consider a reorg

ELSE - you probably do not need to consider a reorg (unless you full scan that segment frequently)

there is no estimate for the time to complete the alter table t shrink space - what you probably want to do is alter table t shrink space COMPACT - that'll be 100% online (no blocking) - so it doesn't really matter how long it takes (it will purely be a function of the size of the table - how long to full scan it - plus the number of rows that actually need to move from the bottom to the top).

After you do the compact, the shrink space command is very fast (very little to do) and will only lock the table for the short duration it needs to move the high water mark.

thanks Tom

A reader, July 11, 2011 - 8:48 am UTC

Thanks for your reply.I plan to take dump of my database and import on another host. I guess the size of export dump will be smaller now (by the amount I will be able to shrink the table).That is only advantage if any . Upon import on new server ,the table newly created would be using space efficently unlike the table from source(from where I deleted large percentage of data.
Tom Kyte
July 13, 2011 - 1:24 pm UTC

this will NOT affect the size of an export (data pump or otherwise) by any amount. Export/datapump read rows and write rows to a file. if you have a table that has 1000gb allocated to it - but zero rows - it will consume no space in an export.

And it would not affect the imported table structure either, importing loads rows.

this would only make sense if you were TRANSPORTING the data and you could shrink the datafiles (not just the table)

In other words, it doesn't make sense for you.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library