Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Narendra.

Asked: January 01, 2006 - 11:42 pm UTC

Last updated: June 29, 2012 - 9:25 am UTC

Version: 9.2.0.1

Viewed 50K+ times! This question is

You Asked

Hi,

A very happy & prosperous new year!!!
I want to reduce the size of USERS datafile. I had created a table with 50000000 records in USERS tablespace. So the size of datafile is around 4.5 GB. I dropped the table. However this had no affect on datafile. I tried taking datafile/tablespace offline and bring it back online. Still no change. I tried shutting and restarting ORACLE instance. Still no change.
When I tried to manually reduce the datafile size (using OEM), it allowed me to reduce the size by only few KBs. Any subsequent attempt to reduce the size resulted in failure saying it needs the space.
How do I reduce the size of USERS datafile so that it is only as big as size of database objects and their corresponding data ?

Thanks

and Tom said...

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:153612348067

The only thing that'll make a file "shrink" is to alter the database and shrink the datafile - datafiles will NOT shrink on their own due to "offline/online" or anything like that.

But in your case - if OEM cannot shrink it further, neither will my script.  A file contains extents of segments.  You have an extent for some allocated object out there.

Say you have tables T1 and T2 in that tablespace.  

T1 is represented by X
T2 is represented by Y
free space in the tablespace is represented by f

You created T1 and T2, your datafile in that tablespace might look like this:


XYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

you could shrink that file now and get rid of all of the f's.  But now table T1 grows and we have:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXfff

Now, you can shrink that file and get rid of just three f's (rest of the file is full of data).  Now, table T2 runs out of room and extends:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXYff

Now, if you shrank the file, you would lose two f's of space.  However, you drop table T1 - the datafile looks like this:

fYfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffYff

You can STILL only shrink two f's away at the end - there is a Y extent way out there and we cannot shrink over it.  What you can do is:


alter table t2 MOVE and hopefully the datafile will end up looking like this:

YfYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

and you can shrink the file (or just move t2 into another tablespace all together)

see
https://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html <code>
for "What's at the end of a file"


Here is an example showing what I tried to draw above:

ops$tkyte@ORA9IR2> create tablespace shrink_me
2 datafile '/tmp/shrink_me.dbf' size 704k
3 segment space management manual
4 uniform size 64k
5 /

Tablespace created.

we have exactly 10 64k extents we can use. (the 11th 64k block of space is used by Oracle to manage these locally managed tablespaces in the datafile)


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t1 ( x int, a char(2000) default 'a', b char(2000) default 'b', c char(2000) default 'c') tablespace shrink_me;

Table created.

ops$tkyte@ORA9IR2> create table t2 ( x int, a char(2000) default 'a', b char(2000) default 'b', c char(2000) default 'c') tablespace shrink_me;

Table created.

Each row in these tables will consume a block (8 rows/extent - but don't forget the first block is borrowed by Oracle to manage space in the segment...)

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 88 free

so, we have the starting scenario - T1 has an extent, T2 has one and the rest of the file is "free space", now lets fill up t1:


ops$tkyte@ORA9IR2> insert into t1 (x) select rownum from all_objects where rownum <= 56;

56 rows created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 32 T1
13 33 40 T1
13 41 48 T1
13 49 56 T1
13 57 64 T1
13 65 72 T1
13 73 80 T1
13 81 88 free

10 rows selected.

we have the middle scenario - if we dropped T1 now, all of the T1's would become free space and we could shrink the file, however:


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 (x) select rownum from all_objects where rownum <= 8;

8 rows created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T1
13 17 24 T2
13 25 32 T1
13 33 40 T1
13 41 48 T1
13 49 56 T1
13 57 64 T1
13 65 72 T1
13 73 80 T1
13 81 88 T2

10 rows selected.

Now the entire tablespace is full - no more free space - but we drop t1 and get LOTS of free space:

ops$tkyte@ORA9IR2> drop table t1;

Table dropped.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 free
13 17 24 T2
13 25 80 free
13 81 88 T2

the tablespace is 80% empty - but we cannot shrink it - we cannot shrink it at all, let alone to 300k - because the LAST EXTENT is taken by T2

ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;
alter database datafile '/tmp/shrink_me.dbf' resize 300k
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


but lets move T2 around...

ops$tkyte@ORA9IR2> alter table t2 move;

Table altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T2
13 17 24 free
13 25 32 T2
13 33 88 free

now we have lots of free space at the end of the file and we can resize:

ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;

Database altered.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name
2 from dba_extents
3 where tablespace_name = 'SHRINK_ME'
4 union all
5 select file_id, block_id, block_id+blocks-1, 'free'
6 from dba_free_space
7 where tablespace_name = 'SHRINK_ME'
8 order by file_id, first_block
9 /

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME
---------- ----------- ---------- ------------------------------
13 9 16 T2
13 17 24 free
13 25 32 T2


don't forget, if you move a table, you have to then rebuild the indexes


Rating

  (29 ratings)

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

Comments

A reader, January 02, 2006 - 3:30 pm UTC


Narendra, January 02, 2006 - 11:31 pm UTC

Tom,

Thanks for the detailed reply. What I tried is I moved all tables that I had created to another tablespace. New tablespace is now showing size of 245 MB. The only 2 tables that are part of USERS tablespace are RUN_STATS & PLAN_TABLE. But still USERS tablespace is showing the size of 4 GB ( usage being 2% ).
Are you trying to say that I am not able to shrink datafile since either of the remaining tables (or both) might be occupying the extents at the end? In such scenario, will REORGANIZE feature of 10g help?

Tom Kyte
January 03, 2006 - 7:26 am UTC

In order for a file to shrink - ever - you need to shrink it.

The first link I gave above is to a script called "maxshrink", which when run will

a) show you how small each file can become
b) produce the alter database statements you can execute to accomplish that.

The second link I gave above is to an article showing how to find out what object in a tablespace might be preventing you from shrinking the file.

And if they are at the end, I showed you how to use alter table T move; to move them to the front!

Even if you were to DROP run_stats and plan_table - users would still be 4gb, it will persist in being 4gb until and unless you shrink the file using the alter database command.

Excellent answer

kamran, January 03, 2006 - 12:07 am UTC


Reclaim space

Carlos SS, January 03, 2006 - 6:03 am UTC


What about indexes

Geoff, August 25, 2008 - 10:03 pm UTC

The datafiles I'm wanting to shrink look a lot like the example you've given, only they have about 10 different indexes that are filling up the tail of the datafile. Am I best off dropping and recreating these indexes/rebuilding/something else?

Thanks
Tom Kyte
August 26, 2008 - 9:20 pm UTC

hard to say, because I know *nothing* about you or your real requirements.

You need to move those extents out of there if you are serious about shrinking the file.

You want the "best" shrink? Move everything out of that tablespace and drop it (rename new to old if you want afterwards)....

rebuild these 10 online into a new tablespace and (assuming these were the only 10 things there) drop it.

alter table shrink

Virgile Vanoli, December 17, 2008 - 5:26 am UTC

Hi Tom,
I did the following test:
create table virgile as select 1 type, t.* from dba_objects t;
insert into virgile select 2 type, t.* from dba_objects t;
insert into virgile select 3 type, t.* from dba_objects t;
insert into virgile select 4 type, t.* from dba_objects t;
delete from virgile where type=2;
At the end of this, I have 26 extents and my table is 25% empty.
Then I executed the alter table shrink.
I was expecting that the system would have :
1. deleted the empty extents in the middle of the table.
2. moved rows from the end of the table to the remaining free space in the middle
3. shrunk the table

In reality, the system did step 2 and 3 only.

Is there a way to tell oracle to delete an extent ?
If not, why is this option missing ?
Is there a way to move a single row without dropping and recreating the row ?

Thanks in advance for your help and your brillant presentation in Paris where I was
Tom Kyte
December 29, 2008 - 12:21 pm UTC

your example is really lacking. The alter table shrink space command would fail (row movement is not enabled) and I have NO CLUE what command you actually executed there.


the extents in the "middle" are NOT EMPTY - not free. They used to contain data and you deleted that data - but they are still fully formatted blocks.


The shrink command in effect would read the table from the "bottom up" and delete the last row and move it to the "top" of the table. So it 'compacts' the table by making it top heavy. Once this is done, it redraws the high water mark - deallocating at the bottom of the table.

You'll never take out the middle, the middle will be repopulated from the bottom and the bottom can be taken off.


You did tell Oracle to "delete an extent", we moved the data from the bottom to the top and then deallocated the extents...

Thanks

Virgile Vanoli, January 13, 2009 - 2:44 am UTC

Thanks for your detailed answer.

Virgile

A reader, July 13, 2009 - 7:40 am UTC

Hi Sir
system@XE> create table test tablespace test1 as select * from dba_objects;

Table created.


system@XE> analyze table test compute statistics;

Table analyzed.

system@XE> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
  2  from user_tables where table_name='TEST';

 Ever Used Never Used Total rows
---------- ---------- ----------
       172         84      12576


system@XE> delete from test where owner='SYS' or owner='SYSTEM';

7107 rows deleted.

system@XE> commit;

Commit complete.

system@XE> analyze table test compute statistics;

Table analyzed.

system@XE> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
  2  from user_tables where table_name='TEST';

 Ever Used Never Used Total rows
---------- ---------- ----------
       172         84       5469



system@XE> alter table test move ;

Table altered.

system@XE>  analyze table test compute statistics;

Table analyzed.

system@XE> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
  2  from user_tables where table_name='TEST';

 Ever Used Never Used Total rows
---------- ---------- ----------
        79          1       5469


Instead of "alter table move" If I use "alter table test shrink space":


 Ever Used Never Used Total rows
---------- ---------- ----------
        65          7       5469





1-)What I want to ask is, Does both shrink and alter table move, reclaim space above HWM?
2-)Why does the "Ever Used" part different between shrink and alter table move?Does that mean, shrink is more powerful





Tom Kyte
July 14, 2009 - 6:02 pm UTC

a couple of blocks like that is "nothing"

depends on the type of tablespace and the extent management.

shrink space will read the table from the bottom up and create weird sized extents in a system allocated extent tablespace. alter table T move will allocate "normal" sized extents.

I would not call it more 'powerful'

more online sure.

A reader, July 17, 2009 - 10:39 pm UTC

Thanks sir,
Does both shrink and alter table move, reclaim space above HWM? (give the space above HWM to tablespace)

Tom Kyte
July 24, 2009 - 9:02 am UTC

maybe.


it depends. alter table move might make the table LARGER after the move than it was before. depending on how you use shrink, it could just 'compact' space - not actually redraw the hwm and release space.

both can, neither has to.

Reclaim unused space

Ifti, July 30, 2009 - 10:38 am UTC

Hi Tom,

We have a tablespace of about 535GB and it is used only about 170GB. The reason is purging a table of about 365GB with a long raw column. How can we reclaim the unused space. We are using Oracle 9i and have already done exp/imp for the huge table after purge and is now about 60GB. The database size is about 715GB and it is 24/7. The application is owned by SAP.

Thanks,
Tom Kyte
August 03, 2009 - 5:27 pm UTC

... with a long raw column. ...

ouch.

You have to probably move everything out of that tablespace into a new one (set it to be small with an autoextend on - it'll end up being 100% full when you are done and as compact as possible)

then drop the old and rename new to old name.


You cannot do much with a long raw short of export/import it - so you would export it, drop the old table, create a new one in the new tablespace and import with ignore=y. Other objects can be online redefined into this tablespace - or indexes if they are in there can be rebuilt online into this new tablespace, but moving the long raw is going to be offline.

Thanks

Ifti, August 04, 2009 - 11:46 am UTC

Thank you very much Tom.

Scofield, December 19, 2009 - 9:04 pm UTC

Dear Tom

I know that "alter table move" reclaims space below hwm.

Supose I have blocks in freelist or bitmap.
Some of these blocks are entirely empty, some of them has data(since the data is below pctused they are in freelist or bitmap).

When I issue alter table move, what happens to the blocks in freelist or bitmap which has data in it?
Tom Kyte
December 20, 2009 - 9:09 am UTC

... I know that "alter table move" reclaims space below hwm. ...

technically, alter table move creates a new segment with its own HWM (high water mark). This high water mark will be the lowest it could be for that new segment.


This is a new segment, built from scratch. The old blocks in freelists/whatever - not relevant. It is a brand new table after the MOVE.

Scofield, January 11, 2010 - 1:36 am UTC

Hi sir;

Supose I have blocks in freelist or bitmap.
Some of these blocks are entirely empty, some of them has data.

When I issue "alter table move",
If the block is entirely empty, oracle will try to reclaim that space to tablespace.

Whatif the block is not entirely empty?
Does oracle move the rows from one block to another to make the block empty and then reclaim the space?


Tom Kyte
January 18, 2010 - 6:13 am UTC

the first part about the freelists isn't relevant. We don't process blocks on the freelist "special" with alter table T move.

Think of alter table T move as shorthand for:

create table new_table as select * from old_table;
<create indexes, grants, triggers, constraints, etc on new_table>
drop table old_table;
rename old_table to new_table;


we just read the data from old, write to new in a "compact" form and continue on. It won't do anything special because a block is (or is not) on a free list. It reads all blocks, finds data, creates new compact (full) blocks, writes them.

A reader, February 20, 2010 - 5:14 pm UTC

Hi Tom;

Suppose this is my extent map:

b->block

b1b2b3b4-b5b6b7b8-b9b10b11b12

Lets say
b5 is entirely empty, b6 is also in freelist but has some rows in it.


Once I issue alter table move:
Oracle`ll move b5 to at the end of segment.
For b6, the number of rows doesnt change and oracle doesnt do anything special on b6.

Am I right?

Tom Kyte
February 25, 2010 - 3:40 am UTC

alter table t move does NOT MOVE ANY BLOCKS, it moves data.


it reads the existing data, builds blocks from it, writes it.


the state of b5 and b6 after the move would DEPEND.

suppose b1-b4 were EMPTY before the move. Suppose b7-b12 were empty before the move.

Then, after the move - the table would just have ONE BLOCK with data on it, b1. That would be the data it found on block 6.


Don't over think this, pretend the alter table t move is simply:


for x in ( select * from old)
loop
   insert into new
end loop
drop old
rename new to old


A reader, February 27, 2010 - 11:58 pm UTC

Thanks sir

1-)
Suppose I have below extent map.

b1 has 3 rows (in freelist)
b2 is full
b3 is empty
b4 has 7 rows


When I issue alter table move:
Oracle skips b3
read data from b1 and b4 and put into new b1
and read data from b2 and put into new b2.
Is that right?

2-)Before performing shrink, we have to enable row movement.
Why dont we have to enable row movement prior to alter table move. (since rowids will change)


Tom Kyte
March 01, 2010 - 11:57 am UTC

when you issue the alter table t move, oracle will full scan from START to FINISH.

you'll end up with b1 being full (with 3 rows from old b1 probably and some rows from old b2), and b2 being full (with some rows from old b2 probably and some rows from b4 - which we'll read AFTER we read b3 - we don't (cannot) skip b3 - we just obviously find NO DATA on it). block 3 in the new table may or may not have data on it, depending on whether the data in old b1+b2+b3+b4 fits on one or two blocks (not sure - insufficient data).

stop trying to overanalyze this - I've responded to lots of very very very VERY similar questions in the last few days. Just pretend it all takes place like this:


for x in (select * from old_table)
loop
   insert into new_table;
end loop;
drop table old_table;
rename new_table to old_table;


(oh wait, I just wrote that above didn't I :))


stop thinking "blocks", start thinking "we are RELOADING THE TABLE"


we don't need to enable row movement because you are rebuilding the entire table (and will be rebuilding all of the indexes). It is assumed you obviously know (it says "move" after all) that the table is being MOVED - reorganized. It is not an in place thing (in the same segment thing) like flashback table, update of a partition key, alter table t shrink space compact - none of them create a new segment - this does.

A reader, March 12, 2010 - 8:02 pm UTC

Hi sir;

I know that in 10g there is segment advisor,which tells you how much space will be reclaimed to tablespace once we issue alter table move.

How about in 9i? How can I determine wheter table needs to be reorganized?
Tom Kyte
March 15, 2010 - 9:24 am UTC

look at the statistics after a gather stats - lots in there such as average row length, number of allocated blocks in use or use dbms_space to review what's on the freelist and how much is on the freelists (bitmapped freelists with automatic segment space management or true freelists otherwise).


Alexander, November 08, 2010 - 11:52 am UTC

Hi Tom,

I am working on duplicating a production database to a reporting db. Once upon a time a developer changed a batch job and blew out a bunch of space, which I now can't reclaim. As a result, I'm copying about 30GB of unused, allocated space. Is there a way I can find out what objects are a the edges of the datafiles, so that I can "alter whatever move"?

I am using your script to determine that there is little space left to reclaim. There's 27GB free, but:
 Smallest
                                                                                      Size     Size  Savings
TABLESPACE_NAME                FILE_NAME                                             Poss.     (mb)     (mb)
------------------------------ -------------------------------------------------- -------- -------- --------
SVC_CLOB                       +DATA/ocp25p/datafile/svc_clob_03.dbf                 5,076    6,000      924
SVC_CLOB                       +DATA/ocp25p/datafile/svc_clob_01.dbf                14,216   15,000      784
SVC_CLOB                       +DATA/ocp25p/datafile/svc_clob_02.dbf                18,450   19,000      550
                                                                                                    --------
sum                                                                                                    2,258


Tom Kyte
November 08, 2010 - 12:15 pm UTC

just find the max block id by file and report on those segments - they'll be the "ones furthest out" in a file


select owner, segment_name, tablespace_name, file_id
from (select e.*, max(block_id) over (partition by file_id) max_block
from dba_extents e)
where block_id = max_block
/


Alexander, November 08, 2010 - 3:27 pm UTC

That's a humdinger of a query, been running for two hours, even after I included the tablespace I'm concerned with.
Tom Kyte
November 08, 2010 - 3:42 pm UTC

it might run a bit faster to materialize dba_extents into temp

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select e.*, rownum r
  5    from dba_extents e
  6  )
  7  select owner, segment_name, tablespace_name, file_id
  8  from (select e.*, max(block_id) over (partition by file_id) max_block
  9        from data e)
 10  where block_id = max_block
 11  /

OWNER                          SEGMENT_NAME
------------------------------ ------------------------------
TABLESPACE_NAME                   FILE_ID
------------------------------ ----------
SYS                            I_H_OBJ#_COL#
SYSTEM                                  1

SYSMAN                         MGMT_METRICS_1DAY_PK
SYSAUX                                  2

SYS                            _SYSSMU5_538557934$
UNDOTBS1                                3

OPS$TKYTE                      DISORGANIZED
USERS                                   4

SH                             FW_PSC_S_MV_WD_BIX
EXAMPLE                                 5

FB_DEMO                        T0_IDX
TEST_DROP                               9


6 rows selected.

Alexander, November 09, 2010 - 9:48 am UTC

Must be amateur hour over here, what am I doing wrong?

(x223kdc:oracle)> sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Nov 9 10:37:39 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> set pagesize 9999
SQL> set linesize 150
SQL> with data
  2      as
  3      (
  4      select e.*, rownum r
  5        from dba_extents e
  6      )
    select owner, segment_name, tablespace_name, file_id
  7    8      from (select e.*, max(block_id) over (partition by file_id) max_block
  9            from data e)
 10     where block_id = max_block
 11  /

OWNER                          SEGMENT_NAME                   TABLESPACE_NAME                   FILE_ID
------------------------------ ------------------------------ ------------------------------ ----------
SYS                            UTL_RECOMP_SORT_IDX1           SYSTEM                                  1
SYS                            _SYSSMU5$                      UNDOTBS1                                2
SYS                            WRH$_SEG_STAT                  SYSAUX                                  3
ORACLE                         EMBARCADERO_EXPLAIN_PLAN       USERS                                   4
SYS                            _SYSSMU19$                     UNDOTBS2                                5
SERVICE                        INCIDENTSM1                    SVC_DATA                                6
SERVICE                        WORKM1_8                       SVC_INDX                                7
SERVICE                        SYS_LOB0000930699C00001$$      SVC_CLOB                                8
REPMAN                         AQ$_STREAMS_APPLY_QT_P         REPMAN_DATA                             9
RAUKS                          RA_REPORT_TABLE                RIGHTA_DATA                            24
SERVICE                        PROBSUMMARYM1                  SVC_DATA                               25
SERVICE                        SQLMAPOPTIONSM1                SVC_DATA                               26

12 rows selected.

SQL> SELECT OWNER
     , TABLE_NAME
     , SUBSTR(COLUMN_NAME,1,25) "COLUMN"
     , SEGMENT_NAME
     , INDEX_NAME
  FROM DBA_LOBS WHERE OWNER = 'SERVICE' and SEGMENT_NAME = 'SYS_LOB0000930699C00001$$'  2    3    4    5    6
  7  /

OWNER      TABLE_NAME                     COLUMN                    SEGMENT_NAME                   INDEX_NAME
---------- ------------------------------ ------------------------- ------------------------------ ------------------------------
SERVICE    GLOBALLISTSM1                  DESCRIPTOR                SYS_LOB0000930699C00001$$      SYS_IL0000930699C00001$$

1 row selected.

SQL> alter table service.globallistsm1 modify lob (SYS_LOB0000930699C00001$$) (shrink space);
alter table service.globallistsm1 modify lob (SYS_LOB0000930699C00001$$) (shrink space)
                                              *
ERROR at line 1:
ORA-00904: "SYS_LOB0000930699C00001$$": invalid identifier


Is this the right approach to "reorganizing" the segments so they are moved from the edge of the data files?
Tom Kyte
November 11, 2010 - 9:17 am UTC

ops$tkyte%ORA10GR2> create table t ( x int, y clob );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t modify lob(y) (shrink space);

Table altered.


you reference the actual lob column name - not the segment name there.


Now, that will shrink the segment - but if the segment is already "shrunk", you need to MOVE IT

ops$tkyte%ORA10GR2> alter table t move lob(y) store as ( tablespace users );

Table altered.

Alexander, November 11, 2010 - 9:42 am UTC

Ok thanks, that's the syntax I was looking for, to move the lob. However, this surprised me and is concerning:

SQL> alter table service.probsummarym1 move lob(closing_comments) store as ( tablespace svc_clob);
alter table service.probsummarym1 move lob(closing_comments) store as ( tablespace svc_clob)
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 27 in tablespace SVC_DATA


Why is it doing anything with the table? I was looking to move the individual lobs because I don't want to lock up the table for long in production. But it's clearly trying to move something with the table itself (the table is stored in SVC_DATA).
Tom Kyte
November 11, 2010 - 3:10 pm UTC

to move a lob involves moving the table.

You'd have to use dbms_redefinition to do this online instead of alter move.

Dead link

Mike, May 11, 2011 - 11:02 am UTC

The link to the 04-sep technical article no longer works. Is there a new location for the article?

Thanks

shrink segements in datafile

Chris, January 27, 2012 - 8:40 am UTC

Hi Tom,

suppose table before insert rows,looks like that

b - free block with no data

bbbbbbbbbbbbbbbbbbbbbbbbbbbbb

and now insert for example 1000 rows, and table looks like that:

ddddddddddddddbbbbbbbbbbbbbbb

where d - block with data

at now HWM is between b and d on picture, like that:

| - HWM

dddddddddddddd|bbbbbbbbbbbbbbb

at now, we delete for example 500 rows, and now picture look like that:

dddddddbbbbbbb|bbbbbbbbbbbbbbbb

my question is:

if we want insert any additional rows to table,
how will be table look now?

like that?

ddddddbbbbbbdddd|bbbbbbbbbbbbbb

or

ddddddddddbbbbbb|bbbbbbbbbbbb

and if we want shrink table, how will be table look after shrink?

second question is:

if we get data file like that

XYXXXXXXXYYZZZZXXXfffffffffffffff

X is table T1
Y is table T2
Z is table T3
f - free space

and we delete 1000rows from table T1 (X) but not all rows from table T1. And under HWM are free blocks for table T1.
And now, we shrink table T1 (for example command alter table shrink...), and what happens with free space in table T1 after shrunk table T1 ? how will data fille look like now?
Tom Kyte
January 31, 2012 - 4:58 pm UTC

... if we want insert any additional rows to table,
how will be table look now? ...

it depends, it could look anyway - we'll just reuse the blocks below the high water mark before we start using them above.

If you are using freelists, one outcome could happen.
If you are using freelists and freelist groups, another.
If you are using automatic segment space management, yet another.
If you are doing things in a single session using any of the above - one set of outcomes would be observed.
If you are doing things in many different sessions using any of the above - another set of outcomes could be observed.

it depends on lots of factors, it will not be entirely predicable.




If you SHRINK the table, then it will move all of the rows to the "top" of the table, it would leave all of the blocks that do not contain rows at the "bottom of the table".




as for your last question, just replace X with f. Shrinking one table does not move data in any other tables. If you shrink T1 and release its free space - just replace X with f

shrink datafile

nick, January 27, 2012 - 1:40 pm UTC

Hi,

we have data file like this:

XYXXXXXXXXXXXXXXXXXYfffff

X - table T1

Y - table T2

f - free space

No we drop table T1, file looks like

fYfffffffffffffffffYffff

and now, we want create new table T3 (Z)

how data file will be look after create table T3 ?
where oracle allocate space for table T3 ?
like that?

fYfffffffffffffffffYZfff

or like that

ZYfffffffffffffffffYffff

XYZffffffffffffffffYffff

Thank You for help.
Tom Kyte
January 31, 2012 - 5:02 pm UTC

how data file will be look after create table T3 ?


indeterminate, it depends - on too many factors to make this be an entirely predicable event.



Table Reorganisation

Kev B, June 25, 2012 - 5:23 am UTC

Hi Tom,

When I am reorganising tables within our database I use the following method:

ALTER TABLE <name> ENABLE ROW MOVEMENT;
ALTER TABLE <name> SHRINK SPACE CASCADE;
ALTER TABLE <name> DISABLE ROW MOVEMENT;

To my understanding, these commands will free up unsed space within the table and also reorganise dependent objects such as indexes.

My colleague performs the following actions:

ALTER TABLE <name> MOVE;
ALTER INDEX <idx_name> REBUILD;

I haven't been able to ascertain whether or not one technique might be preferrable over the other. The aim is to free-up unused space and reorganise the data within a table along with its dependencies.

I would be grateful for any light yuo can shed on this.

Kind regards,

Kev

Tom Kyte
June 25, 2012 - 9:52 am UTC

shrinking is an online operation - normal activity continues.

moving is an offline operation - activity against the affect object(s) is put on hold until you complete all of the rebuilds

shrinking works in place, doesn't require 2x the storage.
moving/rebuild requires 2x the storage.

If you have enterprise edition, shrink is available to you - if you don't, you won't.

I'd prefer in order...

a) to not do this at all. If you are purging data and that is why you are doing this, find another way to purge (partitioning) so you don't have to reorganize stuff.

b) use shrink - online, less work, less storage

c) use move

Purging

Kev B, June 26, 2012 - 3:43 am UTC

Hi Tom,

Many thanks for your response!

I would like to point out though that the reason we are doing this is not because we are looking to purge data, rather there is a lot of data movement within certain tables in the form of DELETEs, UPDATEs and INSERTs.

The tablespaces in which these tables are located are locally-managed and although that significantly reduces the level of fragmentation within these tables, it is still felt that performance gains are attainable by performing these reorganisations after mass-DML operations.

The tables we are reorganising are very basic, comprising of pure text and no LOBs or external sources but with row counts in the 10s of millions.

Would you deem our assessment to be reasonable or would you still regard reorganisation to be unnecessary?

Kind regards,

Kev
Tom Kyte
June 26, 2012 - 7:59 am UTC

you are reclaiming space for a very very very short period of time.

"felt" - feelings do not count in technology.
"facts" - facts do.

what are you doing to prove that this probably unnecessary work is doing you more good than harm (yes, a reorganization can negatively impact performance as well as positively affect it)

So, what numbers, what facts do you have to support your feelings that this is a good thing? I'm not saying they do not exist, just wondering if you've ever done the due diligence to back up this feeling

Facts

Kev B, June 28, 2012 - 2:39 am UTC

Hi again Tom,

I see your point about facts. One fact is that a great amount of data could be moved, or a very small amount (and not always in the same tables) and to that end we have HWMs that will not accurately reflect the amount of data in the database.

To that end, and because the amount of space that could even "temporarily" be saved (and whats more, to ensure that enough storage space is available without having large tables only partially filled with data) it was decided to perform these reorgs and shrinks.

Which takes me on to another point that you raised. You intimated that performing reorganisations could cause more problems than they solve although I am at a loss to understand why. I would be grateful if you could explain that statement.

And that being the case, what foreseeable reason could there possibly be for using these reorg tools in a production environment?

My interest is picqued!

Many thanks and kind regards,

Kev
Tom Kyte
June 28, 2012 - 9:54 am UTC

we have HWMs that will not accurately reflect the amount of data in the
database.


and that may or may not matter at all.

temporary space savings is not space savings at all. It is just making more work for us next week. You are spending lots of time/energy/effort to deallocate space that we *just have to reallocate again*.

Look at our temporary tablespace - we went to great pains to *not shrink it* - because it takes a lot of effort to reallocate that space.

Same with your tables.

Still all about feelings here, no facts yet.



see
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

A reader, June 28, 2012 - 11:38 am UTC

"You are spending lots of time/energy/effort to deallocate space that we *just have to reallocate again*."


contributing to global warming? :)

Thanks

Tom Kyte
June 29, 2012 - 9:25 am UTC

indeed, that is another way of looking at it :)

Thank you!

KevB, July 04, 2012 - 2:55 am UTC

Hi Tom!

Well, try as I might, I couldn't fault the logic behind your reasoning and even looked at a large amount of tables to assess whether or not reorganisation was really necessary and the answer was a resounding no.

From now on we are removing regular reorgaisation from our list of regular tasks.

Many thanks for your input!

Regards,

Kev

SUBPARTITION FREE UP SPACE

Deepak, November 30, 2013 - 3:18 pm UTC

Hi Tom,

We have the following scenario....

1> Table T1 is a RANGE-HASH partitioned table. Partition is based on date. One partition per day.
2> At any point the table will have 100 partitions
2> We have moving window partition maintenance scheme. i.e., After 100 days the oldest partition is deleted and every day a new partition is added.
3> Insertions mostly happen on the first 10 partitions.
4> 90% of the data in the partitions which are older than 10 days are deleted and only few rows per partition is retained.
5> Therefore at any point in time only 10 paritions will be packed with data and remaining partitions will have a lots of empty space.
6> No insert happens on the oldest 90 partitions.
7> All teh data should be available for read at any point in time.

The requirement is to free up the space (from the segments) immediately after the rows are deleted from older partitions. This way more space will be available for creating other segments.

What would be the ideal strategy to achieve this.

Would any of the following be a good solution?

A>"alter table .... shrink"
B> Create a table and exchange with the partitions?


Please advice.

Would like to THANK you for the immense help that you are extending to the Oracle community.