Skip to Main Content
  • Questions
  • 10g shrink space with compressed table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Alen.

Asked: December 18, 2004 - 9:01 am UTC

Last updated: July 16, 2012 - 1:52 pm UTC

Version: 10.1.0

Viewed 10K+ times! This question is

You Asked

SQL>
SQL> select tablespace_name, def_tab_compression, segment_space_management
2 from dba_tablespaces;

TABLESPACE_NAME DEF_TAB_ SEGMEN
------------------------------ -------- ------
SYSTEM DISABLED MANUAL
UNDOTBS1 DISABLED MANUAL
SYSAUX DISABLED AUTO
TEMP DISABLED MANUAL
USERS DISABLED AUTO
TEST_TBS DISABLED AUTO

6 rows selected.

SQL> create table t_uncompress
2 tablespace users
3 as
4 select * from all_objects
5 /

Table created.

SQL> create table t_compress
2 compress
3 tablespace users
4 as
5 select * from all_objects
6 /

Table created.

SQL> alter table t_uncompress enable row movement;

Table altered.

SQL> alter table t_compress enable row movement;

Table altered.

SQL> alter table t_uncompress shrink space;

Table altered.

SQL> alter table t_compress shrink space;
alter table t_compress shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


I'm just curios is this an intended behavior ?
The 10g SQL Reference didn't mention any restrictions regarding shrinking compressed tables ...

Thanks.

and Tom said...

I'll file a documentation bug -- this should be documented.

It makes sense though, for compression only works with direct path operations -- if you do "conventional" operations like insert, update, delete, merge -- the data modified is decompressed.

You would use SHRINK on a table that experienced alot of modifications over time -- has white space in it. The use case for shrink just doesn't fit the use case for compress. If you are doing lots of DML on the table, compress doesn't make sense and shrink only makes sense when you have lots of DML. They are sort of mutually exclusive. It is an oversight that it was not documented that way.

With compressed segments, you would want to use "ALTER ... MOVE" in order to "shrink" -- else the table would probably have GROWN (since the shrink is like a 'delete' + 'insert at top of table' -- it is not a direct path operation - that is why it is an "online" thing) if this were permitted. That is, shrinking a compressed table would probably result in a table larger than you started with right before the shrink!




Rating

  (23 ratings)

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

Comments

10g shrink space with compressed table

Alen, December 18, 2004 - 2:36 pm UTC

Thanks Tom,
i thought that was the case, but I wasn't sure since the topic wasn't documented.

Happy holidays

Running Segment Advisor to analyze compressed table

Alen, December 19, 2004 - 2:11 pm UTC

Tom,

there is one more thing I noticed. When I run segment advisor to analyze a compressed table it exits my session with the following error: ORA-03113: end-of-file on communication channel 

SQL> 
SQL> create table t_compress
  2  tablespace users
  3  compress
  4  as
  5  select * from all_objects
  6  /

Table created.

SQL> exec dbms_advisor.create_task('Segment Advisor', 'seg_adv');

PL/SQL procedure successfully completed.

SQL> variable objid number
SQL> 
SQL> exec dbms_advisor.create_object('seg_adv', 'TABLE', user, 'T_COMPRESS', null, null, :objid);

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.set_task_parameter('seg_adv', 'RECOMMEND_ALL', 'TRUE');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('seg_adv');
BEGIN dbms_advisor.execute_task('seg_adv'); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 

Even when I deleted all the rows from the table:

SQL> delete from t_compress;

47277 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_advisor.reset_task('seg_adv');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('seg_adv');
BEGIN dbms_advisor.execute_task('seg_adv'); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 


Thanks, Alen

 

Tom Kyte
December 19, 2004 - 3:08 pm UTC

I repproduced:

ORA-07445: exception encountered: core dump [0961E962] [SIGFPE] [Integer divide by zero] [0x961E962] [] []
Current SQL statement for this session:
SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF( :B1 , :B2 , :B3 , 0, :B4 , 'TRUE', :B5 )) U
----- PL/SQL Call Stack -----
object line object
handle number name
0x6f843508 429 package body SYS.DBMS_SPACE
0x6f843508 452 package body SYS.DBMS_SPACE
0x6f7699b8 1 anonymous block
0x6f8a350c 1037 SYS.WRI$_ADV_OBJSPACE_TREND_T
0x6f9564cc 1198 package body SYS.PRVT_ADVISOR
0x6f96d3e4 190 package body SYS.DBMS_ADVISOR
0x6f8a9d08 1 anonymous block
----- Call Stack Trace -----


was what the trace file showed -- I bugged it, #4077470

getxml producing ora-07445

A reader, February 19, 2005 - 3:32 pm UTC

Hi Tom,

I have the foll piece of code in my proc

tqryctx:= dbms_xmlquery.newcontext(tquery);
dbms_xmlquery.setbindvalue(tqryctx,'pid', pid);
presult := dbms_xmlquery.getxml(tqryctx);

tquery is dynamically formed in my proc.

Upon executing the getxml stmt, I was getting an 'end-of-file on communication channel'. When I checked the alert log I saw the foll

Errors in file c:\oracle\product\10.1.0\db_3\admin\sid1\udump\facexp_ora_1388.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x154650E] [] [] [] []


Tom Kyte
February 19, 2005 - 3:51 pm UTC

3113, 7445 600 => contact support.

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]

Ajay Sinha, May 23, 2005 - 6:42 am UTC

I have encountered with this problem so many times in the last seven days. Can you let me know the reason and solutio of this problem.
Here are the error description -
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x34ED059] [] [] [] []

I am waiting for early response. Plese do me favour.

Tom Kyte
May 23, 2005 - 11:10 am UTC

please contact support.

Shrink Space and Compressed Table

Tai Gan, October 18, 2006 - 1:26 am UTC

Yes. Tom, It is documented that shrink space cannot be specified for a compressed table. Your explanation makes sense in some way. But, not quite if you really want to shrink compressd tables. This will be the case in Oracle V10.2 where compressing an uncompressed table first time will not have space allocated released, i.e. ending up with a lot of empty blocks after compression as it appears that Oracle uses the original size to allocate the compressed size. However, we still get around it by using shrink space. 1. alter table <already compressed table> nocompress 2. alter table <already compressed table> enable row movement. 3. alter table shrink space. This is a way of 'fooling' Oracle restriction. Is this a bug or a valid way of doing thing?

Tom Kyte
October 18, 2006 - 7:54 am UTC

huh?  I don't know what you mean at all here.

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             689
Total Blocks............................             768
Total Bytes.............................       6,291,456
Total MBytes............................               6
Unused Blocks...........................              59
Unused Bytes............................         483,328
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,033
Last Used Block.........................              69

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter table t compress;

Table altered.

ops$tkyte%ORA10GR2> alter table t move;

Table altered.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             206
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              38
Unused Bytes............................         311,296
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,289
Last Used Block.........................              90

PL/SQL procedure successfully completed.


 

10g shrink space with compressed table", version 10.1.0

Tai Gan, November 02, 2006 - 12:18 am UTC

Hi Tom,
Sorry, I was not clear enough.
If you do this:
1. Create table XXX storage(initial 10m)
pctfree 0 nologging as select * from dba_tables where
rownum < 1;
2. alter table XXX move compress;
3. You will find that XXX has the same storage allocation after compression, i.e. 10M. That is to say, the allocation is inherited from the original specification. We can of course change the allocation by issueing for example alter table XXX move compress storage(initial 1m) to get the proper storage after compression. We cannot use shrink space to reduce the storage becasue of compression. BUT, if we disable the compression, i.e. alter table XXX nocompress, then alter table XXX shrink space will work after issueing enable row movement. Of course, XXX remains compressed physically. So, is this a hole in the Oracle?

Tom Kyte
November 02, 2006 - 7:01 am UTC

"of course" pops into my head - you get what you ask for. The goal of compress is utilize as few blocks as possible - which this has done (even with the 10mb initial extent).

You specifically asked for 10mb to be allocated to the segment, we obeyed that request.

You requested also for us to use as few blocks out of that allocation as possible by compressing, we obeyed that request.

Unless and until you ask us to allocate LESS storage, our hands are tied.


This is why I don't use initial, next, pctincrease whatever on the create anymore (only use initial/next/pctincrease on a move with values of 1k 1k 0 - in order to wipe out any outstanding storage parameteres)

Exchange partition

CT VELU, February 23, 2007 - 4:53 pm UTC

Hi Tom
I am following this site and your books for one of requirement in doing exchange partition. I am getting a error message while I try do exchange partition between a partitioned table with primary key (and global index) and a non partitioned table (no index). I tried few different options with the help of your book and this site but not quite successful ..........(either I am getting a column mis match or index mismatch). If I drop the constraint everything works fine.
Here is the script which generates error message.......

drop table my_partitioned;

create table my_partitioned
(run_nbr number(10),
cst_nbr number(10),
itm_nbr number(10),
yr number(4),
mth number(2),
sales number(4))
partition by list (run_nbr)
(
partition part_1 VALUES (1001),
partition part_2 VALUES (1002)
);



create index my_part_indx
on my_partitioned(run_nbr,cst_nbr,itm_nbr,yr,mth)
GLOBAL;


drop table dummy;

create table dummy
as
select *
from my_partitioned
where 1=2;

--create index dummy_index on dummy(run_nbr,cst_nbr,itm_nbr,yr,mth);

insert into my_partitioned (run_nbr,cst_nbr,itm_nbr,yr,mth,sales)
values(1001,5501,3456,2007,01,35);

insert into my_partitioned (run_nbr,cst_nbr,itm_nbr,yr,mth,sales)
values(1001,5501,3457,2007,01,35);

insert into my_partitioned (run_nbr,cst_nbr,itm_nbr,yr,mth,sales)
values(1001,5502,3456,2007,01,35);

insert into my_partitioned (run_nbr,cst_nbr,itm_nbr,yr,mth,sales)
values(1001,5503,3456,2007,01,35);

insert into my_partitioned (run_nbr,cst_nbr,itm_nbr,yr,mth,sales)
values(1001,5504,3456,2007,01,35);

commit;

select count(*)
from my_partitioned
where run_nbr = 1001;

select count(*)
from my_partitioned
where run_nbr = 1002;

select count(*)
from dummy;

ALTER TABLE my_partitioned
EXCHANGE PARTITION
PART_1 WITH TABLE dummy
including indexes
without validation
UPDATE GLOBAL INDEXES;


alter table my_partitioned add constraint
my_partitioned_pk
primary key(run_nbr,cst_nbr,itm_nbr,yr,mth);

-- Error pops up here either way you do the exchange-------
ALTER TABLE my_partitioned
EXCHANGE PARTITION PART_1 WITH TABLE dummy
including indexes
without validation
UPDATE GLOBAL INDEXES;

alter table my_partitioned drop constraint
my_partitioned_pk;

ALTER TABLE my_partitioned
EXCHANGE PARTITION PART_1 WITH TABLE dummy
including indexes
without validation
UPDATE GLOBAL INDEXES;

I tried creating a local unique index and a primary key on the table it works fine, but In the production table I would like to have local bitmap indexes on itm_nbr,yr, mth ( result set will be 2.5 to 5 million rows and 1000 distinct itm_nbr) and global primary key on cst_nbr(this will become a header number),itm_nbr,yr,mth.

Thank you very much for your time.

CT
Tom Kyte
February 26, 2007 - 12:42 pm UTC

you wrote

... I am getting a error message while I try do exchange partition between a partitioned table with primary key (and global index) and a non partitioned table (no index). ....


but you code

INCLUDING INDEXES


either they have the same indexing scheme (and including indexes works) or not.

Sorry I posted in wrong page

CT VELU, February 23, 2007 - 4:55 pm UTC

Hi Tom
I sincerely apologise for psting in the wrong page (This is what happens when we keep open too many AskTom pages). so I am posting it in the write place.
Thanks
CT

unallocated space

trieder, June 23, 2008 - 10:15 am UTC

I have a somehow the same problem as Tai Gan.

I compressed the table partition with 
alter table X_PROD.t_book move partition P_BOOK_2008KW15 compress update indexes;

But the show_space reports 3.7G unused space. Why is this space not deallocated with the alter table move?

SQL>  exec show_space('T_BOOK','X_PROD','TABLE PARTITION','P_BOOK_2008KW15');                                                                
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          99,855
Total Blocks............................         330,624
Total Bytes.............................   5,416,943,616
Total MBytes............................           5,166
Unused Blocks...........................         230,428
Unused Bytes............................   3,775,332,352
Last Used Ext FileId....................              26
Last Used Ext BlockId...................          96,773
Last Used Block.........................           3,42

Tom Kyte
June 23, 2008 - 1:02 pm UTC

you give us nothing useful here.

like the storage characteristics of the segment and tablespace.

An empty table in a tablespace with uniform extents of 1gb would always consume at least one gb.

So, I presume you have large uniform extents and you did this operation in parallel and you have many untrimmable extents out there with unused space.

but, we are just guessing, actually reusing the exact answer given to tai gan!

unalllocate space (II)

trieder, June 24, 2008 - 3:02 am UTC

Sorry I missed to post some information. But as well the original answer does not give me a clear picture why oracle behaves this way.

The tablespace has 63M uniform extents. So after a alter table move. i don't expect more than 63M allocated but unused.
May be if I would do the move table with parallel 2 I could live with max. 126M unused space, but i do the alter table move not in parallel.

select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='T_BOOK_2008KW15';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ -------------- ----------- ---------- --------- ------
T_BOOK_2008KW15 66060288 66060288 LOCAL UNIFORM AUTO


The pct_free is 0 for this partition, it was set automatically. The should no space be wasted.

select table_name,partition_name,pct_free,pct_used,compression from dba_tab_partitions where table_name='T_BOOK';

TABLE_NAME PARTITION_NAME PCT_FREE PCT_USED COMPRESS
------------------------------ ------------------------------ ---------- ---------- --------
T_BOOK P_BOOK_2008KW17 15 DISABLED
T_BOOK P_BOOK_2008KW15 0 ENABLED

Now I tried the stuff Tai Gan did.

Before (its already compressed):
exec show_space('T_BOOK','X_PROD','TABLE PARTITION','P_BOOK_2008KW15');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 99,855
Total Blocks............................ 330,624
Total Bytes............................. 5,416,943,616
Total MBytes............................ 5,166
Unused Blocks........................... 230,428
Unused Bytes............................ 3,775,332,352
Last Used Ext FileId.................... 26
Last Used Ext BlockId................... 96,773
Last Used Block......................... 3,428

-> 3,7G are waisted

alter table X_PROD.t_book modify partition P_BOOK_2008KW15 nocompress ;
alter table x_prod.t_book enable row movement;
alter table x_PROD.t_book modify partition P_BOOK_2008KW15 shrink space;
alter table x_prod.t_book disable row movement;
alter table x_PROD.t_book modify partition P_BOOK_2008KW15 compress ;
-- no indexes got in valid

After:
exec show_space('T_BOOK','X_PROD','TABLE PARTITION','P_BOOK_2008KW15');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 99,855
Total Blocks............................ 100,800
Total Bytes............................. 1,651,507,200
Total MBytes............................ 1,575
Unused Blocks........................... 604
Unused Bytes............................ 9,895,936
Last Used Ext FileId.................... 26
Last Used Ext BlockId................... 96,773
Last Used Block......................... 3,428

PL/SQL procedure successfully completed.

-> now just about 9M are "waisted". But I expected this result after the "alter table move compress".

It looks for me as the table HWM does not get reset with the move for compression. Is that really the expected behavior?
Tom Kyte
June 24, 2008 - 5:29 am UTC


likely, you had a storage clause on the table originally (again, we don't have your creates, we don't really know)

when you did the MOVE, it inherited the original storage.



ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   char(2000)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )<b>
 13  storage ( initial 10m ) tablespace users
 14  /</b>

Table created.

ops$tkyte%ORA10GR2> exec show_space( 'T', user, 'TABLE PARTITION', 'PART1' );
Total MBytes............................              10
Unused Bytes............................      10,452,992

PL/SQL procedure successfully completed.
<b>table starts life at 10mb, with a default storage of 10mb</b>

ops$tkyte%ORA10GR2> alter table t move partition part1 compress;

Table altered.

ops$tkyte%ORA10GR2> exec show_space( 'T', user, 'TABLE PARTITION', 'PART1' );
Total MBytes............................              10
Unused Bytes............................      10,452,992

PL/SQL procedure successfully completed.

<b>partition is in fact compressed but, we have our default storage clause</b>

ops$tkyte%ORA10GR2> alter table t enable row movement;

Table altered.

ops$tkyte%ORA10GR2> alter table t modify partition part1 nocompress;

Table altered.

ops$tkyte%ORA10GR2> alter table t modify partition part1 shrink space;

Table altered.

ops$tkyte%ORA10GR2> exec show_space( 'T', user, 'TABLE PARTITION', 'PART1' );
Total MBytes............................               0
Unused Bytes............................          32,768

PL/SQL procedure successfully completed.

<b>Now, shrinking the segment is one approach, that deallocates space above the high water mark....</b>

ops$tkyte%ORA10GR2> alter table t move partition part1 compress;

Table altered.

ops$tkyte%ORA10GR2> exec show_space( 'T', user, 'TABLE PARTITION', 'PART1' );
Total MBytes............................              10
Unused Bytes............................      10,452,992

PL/SQL procedure successfully completed.

<b>but if you move it, you have the default storage still, so....</b>

ops$tkyte%ORA10GR2> alter table t move partition part1 compress <b>storage( initial 1k next 1k pctincrease 0 minextents 1 );</b>

Table altered.

<b>Give it a new storage clause, since you are using a locally managed tablespace - it'll default to the minimum for that tablespace</b>

ops$tkyte%ORA10GR2> exec show_space( 'T', user, 'TABLE PARTITION', 'PART1' );
Total MBytes............................               0
Unused Bytes............................          40,960

PL/SQL procedure successfully completed.

<b>and it is 'small' all by itself (I use system allocated extents, not uniform)</b>




So basically, it is what I said - your storage clauses, same exact thing that happened to the other guy, identical. In fact, I just copied his example (10mb) right here - same exact thing happening.

mandm, July 19, 2010 - 12:48 pm UTC

So from what i understand from this thread is

1) For a segment space management = Auto, shrinking table is better and Compressing the table
2) For segment space management = Auto we do not have to change the storage parameters when moving table from shrink to compress?

Please correct me if my interpretation is wrong
Tom Kyte
July 19, 2010 - 2:14 pm UTC

(1) could not parse "X is better and Y the Z". ????

shrinking is better than what?

what does compressing the table have to do with shrinking?


2) you do not "move from shrink to compress". You can shrink a table (online reorganize), you can set the compress attribute of a table (does not compress anything, just permits FUTURE operations to be compressed)


You need to be a bit more clear.

query table size against diff dictionaries and get diff size for same table

Pauline, August 20, 2010 - 3:07 pm UTC

Tom,
when we archive history tables by export and then truncate table and then import recent few years data back to the table, we know the table will have unused blocks. So we do
altering table shrink space. After shrinking space, we query same table from dba_tables and dba_segments, the returned results in 1 database make sense -- they are pretty much close, see below

DBACODE@stg1 > select table_name, blocks*16384/1024/1024 tbsize_MB from dba_tables where owner='CORE' and table_name='H_PRIMARY_ACCOUNTS';

TABLE_NAME                      TBSIZE_MB
------------------------------ ----------
H_PRIMARY_ACCOUNTS              190.46875

DBACODE@stg1 > col SEGMENT_NAME for a30
DBACODE@stg1 > select segment_name, bytes/1024/1024  tbsize_MB from dba_segments where owner='CORE' and segment_name='H_PRIMARY_ACCOUNTS';

SEGMENT_NAME                    TBSIZE_MB
------------------------------ ----------
H_PRIMARY_ACCOUNTS                    192

But in another database, we have done same shrink space on the table,query against dba_tables and dba_segments return diff results -- result from dba_tables is same as before shrinking, but result from dba_segments is showing space shrinked

DBACODE@stg2 > select table_name, blocks*16384/1024/1024 tbsize_MB from dba_tables where owner='CORE' and table_name='H_PRIMARY_ACCOUNTS';

TABLE_NAME                      TBSIZE_MB
------------------------------ ----------
H_PRIMARY_ACCOUNTS             1530.73438

DBACODE@stg2 > col SEGMENT_NAME for a30
DBACODE@stg2 > select segment_name, bytes/1024/1024  tbsize_MB from dba_segments where owner='CORE' and segment_name='H_PRIMARY_ACCOUNTS';

SEGMENT_NAME                    TBSIZE_MB
------------------------------ ----------
H_PRIMARY_ACCOUNTS                    187

Don't know why dba_tables still keep big table size in stg2.

The segment_space_management of tablespace where table resides in both database is AUTO.

Thanks




Tom Kyte
August 25, 2010 - 12:40 pm UTC

dba_tables.blocks is maintained during a statistics gathering. You got 'lucky' in the first case (meaning, the table didn't shrink very much so the numbers looked close). You didn't in the second case - the table was large - you gathered stats (so blocks was filled in) and then you shrunk it - and blocks did not change because you did not gather statistics

ops$tkyte%ORA10GR2> create table t enable row movement as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select blocks from dba_tables where table_name = 'T' and owner = user;

    BLOCKS
----------


ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select blocks from dba_tables where table_name = 'T' and owner = user;

    BLOCKS
----------
       713

ops$tkyte%ORA10GR2> truncate table t;

Table truncated.

ops$tkyte%ORA10GR2> select blocks from dba_tables where table_name = 'T' and owner = user;

    BLOCKS
----------
       713

ops$tkyte%ORA10GR2> alter table t shrink space;

Table altered.

ops$tkyte%ORA10GR2> select blocks from dba_tables where table_name = 'T' and owner = user;

    BLOCKS
----------
       713

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select blocks from dba_tables where table_name = 'T' and owner = user;

    BLOCKS
----------
         0




ORA-10635 at ORACLE SQL Developer

PAUL, February 19, 2012 - 8:33 pm UTC

Hello TOM:
i guess i find oracle sql developer bug,while i run the shrink statment at oracle sql developer,then always give me the ora-10635,and i check the table parameter,not have delinquent property,below is my table detailed parameter:
compression:enable(when i change to disable,the result is
same as enable)
column date_type only have three type:varchar2,number and
date
tablespace_space_management is :auto
the index type is :normal
check the dba_dependencies :one view

could you please tell me how to result ora-10635 at oracle sql developer?




Tom Kyte
February 20, 2012 - 2:32 am UTC

ops$tkyte%ORA11GR2> create table t ( x int ) tablespace test;

Table created.

ops$tkyte%ORA11GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


ops$tkyte%ORA11GR2> select tablespace_name from user_segments where segment_name = 'T' and segment_type = 'TABLE';

TABLESPACE_NAME
------------------------------
TEST

ops$tkyte%ORA11GR2> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name = 'TEST';

SEGMEN
------
MANUAL

ops$tkyte%ORA11GR2> 



run those last two queries for me - substituting in YOUR table name and YOUR tablespace name.

ora-10635 at oracle sql developer

PAUL, February 20, 2012 - 3:10 am UTC

SQL> select tablespace_name from user_segments where segment_name ='TB_ORDERHIST' and segment_type = 'TABLE';

TABLESPACE_NAME
------------------------------
TBS_ORDER

SQL> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name = 'TBS_ORDER';

SEGMEN
------
AUTO

Tom Kyte
February 20, 2012 - 6:44 pm UTC

set long 50000
select dbms_metadata.get_ddl( 'TABLE', 'TB_ORDERHIST' ) from dual;


show us the output of that.

ora-10635 at oracle sql developer

PAUL, February 20, 2012 - 7:27 pm UTC

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------

CREATE TABLE "BENESSE"."TB_ORDERHIST"
( "ORDERID" VARCHAR2(16) NOT NULL ENABLE,
"ORDERVIEWID" VARCHAR2(16) NOT NULL ENABLE,
"MAILID" VARCHAR2(20),
"CONTACTID" VARCHAR2(16),
"ADDRESSID" VARCHAR2(16),
"CRUSR" VARCHAR2(10),
"MDUSR" VARCHAR2(10),
"MDDT" DATE,
"PARCELNM" VARCHAR2(20),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"STATUS" VARCHAR2(2),
"ACCOUNT" VARCHAR2(12),
"ORDERTYPE" VARCHAR2(2),
"MAILTYPE" VARCHAR2(2),
"PAYTYPE" VARCHAR2(2),
"CRDT" DATE,
"SENDDT" DATE,
"FBDT" DATE,
"OUTDT" DATE,
"ACCDT" DATE,
"TOTALPRICE" NUMBER(10,2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"MAILPRICE" NUMBER(10,2),
"PRODPRICE" NUMBER(10,2),
"MDISCOUNT" NUMBER(10,2),
"POSTFEE" NUMBER(10,2),
"PDISCOUNT" NUMBER(10,2),
"BILL" VARCHAR2(2),
"PRODID" VARCHAR2(20),
"CRPG" VARCHAR2(10),
"MDPG" VARCHAR2(10),
"MONTHNUMBER" VARCHAR2(2),
"PAYFLAG" VARCHAR2(2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"SUBENTITYID" VARCHAR2(2),
"LINE" VARCHAR2(6),
"FEEDBACK" VARCHAR2(100),
"ORDERPRICE" NUMBER(10,2),
"SENDFLG" VARCHAR2(4),
"ZCSTATUS" VARCHAR2(2),
"BILLPRICE" NUMBER(10,2),
"SIGNDATE" DATE,
"PAYSTATUS" VARCHAR2(2),
"PRODWEIGHT" VARCHAR2(16),
"MAILPRICE1" NUMBER(10,2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"POSTFEE1" NUMBER(10,2),
"PRODVOLUME" VARCHAR2(16),
"PRODLENGTH" VARCHAR2(16),
"SENDTYPE" VARCHAR2(2),
"PRODGRNUMBER" VARCHAR2(4),
"POSTID" VARCHAR2(32),
"ORGID" VARCHAR2(32),
"FLAG" VARCHAR2(2),
"FYEAR" VARCHAR2(8),
"PRODHEIGHT" VARCHAR2(16),
"CHARGE" NUMBER(10,2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"CDISCOUNT" NUMBER(10,2),
"CHARGE1" NUMBER(10,2),
CONSTRAINT "TB_ORDERHIST_PK" PRIMARY KEY ("ORDERID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_BENESSE_IDX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_BENESSE"

Tom Kyte
February 21, 2012 - 7:17 pm UTC

Ok, please explain to me how this create table says "i am in TBS_BENESSE", but above you showed this:


SQL> select tablespace_name from user_segments where segment_name ='TB_ORDERHIST' and segment_type 
= 'TABLE';

TABLESPACE_NAME
------------------------------
TBS_ORDER

SQL> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name = 'TBS_ORDER';

SEGMEN
------
AUTO


???

What tablespace is it actually in - TBS_ORDER or TBS_BENESSE, things are not adding up here.

ora-10635 at oracle sql developer

PAUL, February 21, 2012 - 9:10 pm UTC

TBS_BENESSE IS MY PRODUCT DATABASE
TBS_ORDER IS TEST DATABASE


ONLY DIFFERENT IS NAME,SAME STRUCTURE.
Tom Kyte
February 23, 2012 - 12:16 am UTC

I want you to work ONLY IN THE DATABASE YOU ARE HAVING PROBLEMS.

this will *not* work (you and me talking here) if you go back and forth between two database.


Do everything I asked in the ONE DATABASE, the one having the problem. The database that doesn't have the table having the problem is a database I don't really care to see anything from.


Think about that please - why would you give me output from some other database that is obviously set up and configured entirely differently from the one having the issue? Please don't do that!


(written to you from Shanghai China - I happen to be in the Oracle office right now in that city ;) )

ora-10635 at oracle sql developer

PAUL, February 23, 2012 - 3:12 am UTC

Hello Tom:
i apology to you for my explanation does not clear,is my
mistake,sorry.
i have two database,one for product,one for test.
i use product datebase Rman backup recovery everyday at test database.
yesterday,i want to make test at my test database another tablespace(TBS_BENESSE) and i saw you replied my problem,
but i forget i'm in test datebase and exec your statment and upload,when the date already upload i find it is wrong,i try upload the correct date,but always failed....

below is the correct date:
DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------

CREATE TABLE "BENESSE"."TB_ORDERHIST"
( "ORDERID" VARCHAR2(16) NOT NULL ENABLE,
"ORDERVIEWID" VARCHAR2(16) NOT NULL ENABLE,
"MAILID" VARCHAR2(20),
"CONTACTID" VARCHAR2(16),
"ADDRESSID" VARCHAR2(16),
"CRUSR" VARCHAR2(10),
"MDUSR" VARCHAR2(10),
"MDDT" DATE,
"PARCELNM" VARCHAR2(20),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"STATUS" VARCHAR2(2),
"ACCOUNT" VARCHAR2(12),
"ORDERTYPE" VARCHAR2(2),
"MAILTYPE" VARCHAR2(2),
"PAYTYPE" VARCHAR2(2),
"CRDT" DATE,
"SENDDT" DATE,
"FBDT" DATE,
"OUTDT" DATE,
"ACCDT" DATE,
"TOTALPRICE" NUMBER(10,2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"MAILPRICE" NUMBER(10,2),
"PRODPRICE" NUMBER(10,2),
"MDISCOUNT" NUMBER(10,2),
"POSTFEE" NUMBER(10,2),
"PDISCOUNT" NUMBER(10,2),
"BILL" VARCHAR2(2),
"PRODID" VARCHAR2(20),
"CRPG" VARCHAR2(10),
"MDPG" VARCHAR2(10),
"MONTHNUMBER" VARCHAR2(2),
"PAYFLAG" VARCHAR2(2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"SUBENTITYID" VARCHAR2(2),
"LINE" VARCHAR2(6),
"FEEDBACK" VARCHAR2(100),
"ORDERPRICE" NUMBER(10,2),
"SENDFLG" VARCHAR2(4),
"ZCSTATUS" VARCHAR2(2),
"BILLPRICE" NUMBER(10,2),
"SIGNDATE" DATE,
"PAYSTATUS" VARCHAR2(2),
"PRODWEIGHT" VARCHAR2(16),
"MAILPRICE1" NUMBER(10,2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"POSTFEE1" NUMBER(10,2),
"PRODVOLUME" VARCHAR2(16),
"PRODLENGTH" VARCHAR2(16),
"SENDTYPE" VARCHAR2(2),
"PRODGRNUMBER" VARCHAR2(4),
"POSTID" VARCHAR2(32),
"ORGID" VARCHAR2(32),
"FLAG" VARCHAR2(2),
"FYEAR" VARCHAR2(8),
"PRODHEIGHT" VARCHAR2(16),
"CHARGE" NUMBER(10,2),

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
"CDISCOUNT" NUMBER(10,2),
"CHARGE1" NUMBER(10,2),
CONSTRAINT "TB_ORDERHIST_PK" PRIMARY KEY ("ORDERID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ORDER_IDX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

DBMS_METADATA.GET_DDL('TABLE','TB_ORDERHIST')
--------------------------------------------------------------------------------
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ORDER"


Tom Kyte
February 23, 2012 - 7:59 pm UTC

your table is compressed, compressed tables do not "shrink"

you'll need to use dbms_redefinition or alter table move to do this.


http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3001.htm#i2192484

blocks in user_semgnts vs blocks in user_tables

Madhu, June 12, 2012 - 4:39 am UTC

Hi Tom,
When I try to shrink space after delete, i'm able to see that the blocks in user_segments table got reduced even without gathering statistics.
Further, querying the table, the execution plan uses stats gathered by user_segments or user_tables?
So, do we still need to gather stats for better performance in this case?

Please have a look at below code, I want to use it instead of doing CTAS. Would you recommend this?

SQL> create table t2 as select * from all_objects;

Table created.

SQL> select count(*) from t2;

COUNT(*)
----------
60748

SQL> select blocks from user_tables where table_name = 'T2';

BLOCKS
----------


SQL> select blocks,extents from user_segments where segment_name = 'T2';

BLOCKS EXTENTS
---------- ----------
896 22

SQL> delete from t2 where rownum<30000;

29999 rows deleted.

SQL> select blocks from user_tables where table_name = 'T2';

BLOCKS
----------


SQL> select blocks,extents from user_segments where segment_name = 'T2';

BLOCKS EXTENTS
---------- ----------
896 22

SQL> alter table t2 enable row movement;

Table altered.

SQL> alter table t2 shrink space;

Table altered.

SQL> select blocks from user_tables where table_name = 'T2';

BLOCKS
----------


SQL> select blocks,extents from user_segments where segment_name = 'T2';

BLOCKS EXTENTS
---------- ----------
464 19

Thanks.
Tom Kyte
June 12, 2012 - 7:29 am UTC

well, you probably want to gather statistics after a shrink since things like your clustering_factor on indexes would change, as would things like num_rows (since you just did that nasty delete), avg row size, number of distinct values, etc.

In other words, if you just purged enough rows to even consider a shrink operation, you purged enough rows to almost certainly mandate a re-gather of statistics.

there are two cases to consider here:

a) you are using dynamic sampling and have gathered no stats
b) you are using dbms_stats and have gathered statistics.

The behavior would be different for each case, first no stats - therefore relying on dynamic sampling:

ops$tkyte%ORA11GR2> create table t2 as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select count(*) from t2;

  COUNT(*)
----------
     72865


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   291   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 68214 |   291   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> delete from t2 where rownum<30000;

29999 rows deleted.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t2 enable row movement;

Table altered.

ops$tkyte%ORA11GR2> alter table t2 shrink space;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select count(*) from t2;

  COUNT(*)
----------
     42866


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   171   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 50863 |   171   (1)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off


here the alter table invalidated any open cursor against T2 and caused it to reparse. Since there were no stats in place, we sampled the table (making the hard parse take longer) but we see there is a difference in the plan. The cost is reduced, the estimated rows are reduced.

However, if you had gathered stats (and this would be the definite recommendation - *gather stats*, do not rely entirely on dynamic sampling, you in general will get much better statistics this way, much better - dynamic sampling is definitely awesome:
http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html

but it shouldn't the way you give information to the optimizer all by itself)

<code
ops$tkyte%ORA11GR2> drop table t2;

Table dropped.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t2 as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select count(*) from t2;

COUNT(*)
----------
72865


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 291 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 72865 | 291 (1)| 00:00:04 |
-------------------------------------------------------------------

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from t2 where rownum<30000;

29999 rows deleted.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter table t2 enable row movement;

Table altered.

ops$tkyte%ORA11GR2> alter table t2 shrink space;

Table altered.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select count(*) from t2;

COUNT(*)
----------
42866


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 291 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 72865 | 291 (1)| 00:00:04 |
-------------------------------------------------------------------

ops$tkyte%ORA11GR2> set autotrace off
></code>

then we use the statistics that are there - the raw statistics that were gathered.

shrink space & gather stats

Madhu, June 15, 2012 - 1:11 am UTC

say, i have a 120Gb table which wasn't truncated since it's creation and been inserting new data and deleting old data every 3 months and gather stats.
Now, on this table I'll perform the following operatinos..

1.delete more than 80% of the data
2.alter table t enable row movement
3.alter table t shrink space
4.alter index idx shrink space compact
5.alter index idx shrink space
6.gather stats

Is there anything wrong/missing in the above steps ?
Also, can I perform the above steps on all tables which support shrink?
After these steps, will that table work like a truncated table(though some data exists) ?

Thanks.
Tom Kyte
June 15, 2012 - 7:53 am UTC

1) you shouldn't do that, you should create table as select to keep the less than 20% of the data you want - drop the old table, rename the new (add indexes, constraints, etc)

DELETE is the single most expensive DML statement - if you are doing a delete that hits a lot of the table - you don't want to do it.

export data

Mahesh, June 18, 2012 - 1:42 am UTC

Another approach:
1. export the data I want to keep. ( user query option) 2. 2. truncate the table.
3. Import the exported tdata in step 1. (ignore=y etc)
Proc: No need to do extensive analysis of dependent objects. ( perticulary to get the management agreement for doing the change in production).
Cons: There will be down time.
Need to find the quite time.

Tom Kyte
June 18, 2012 - 8:59 am UTC

taking data OUT and then trying to put it back IN it hazardous to your health and a career limiting move. I would *not* suggest this.


You do need an extensive analysis of dependent objects! What are you talking about?? How do you think foreign keys to this table are going to work out for you for example?


You are doing a massive change in production anyway - where is the savings in "management agreement"?

datafile size after truncating table

Nikhilesh, July 11, 2012 - 3:54 am UTC

Dear Tom,
We are facing space issues for backups so trying to reclaim space from datafiles. But to my surprise even after we truncate tables even though at oracle level it shows exetnts are freeed but at OS level those blocks can't not be compressed as much as they would be compressed when they are empty.

I tried following on windows 7

1) Created a datafile 5 GB size -------- zipped datafile size 7MB
2) Created a table with 2 GB data ---- zipped datafile size 78 MB
3) Deleted the table ----------- zipped datafile size 80MB --- table extents not free
4) Shrunk the table ----------- zipped datafile size 80MB --- table extents are free now
5) Truncated the table ----------- zipped datafile size 80MB --- table extents are free now

So does this mean after truncate even though the extents/blocks are free for ORACLE, they are nor free at OS level? If this is the case we will have to resize the datafiles to reclaim the space. right?

Thanks in advance.
Tom Kyte
July 12, 2012 - 4:55 pm UTC

use rman, rman won't backup those blocks.


a datafile full of tables will compress to the same size as a datafile full of truncated tables. That is, if you copy a 'full' datafile and compress it, and then truncate all of the tables and compress it - it will be about the same size.

It isn't like truncate rewrites the blocks or anything like that.

they are free at the database level, but they still contain all of the same data unless and until they are overwritten by something else!!! truncate doesn't wipe out the data, it just puts it back in the free space.

unused block compression using gzip

Nikhilesh, July 12, 2012 - 7:05 am UTC

Dear Tom,
 I have tried following to check if unused block compression is supported by gzip (Oracle Linux 6).

SQL> create tablespace test
  2  datafile '/u01/app/oracle/oradata/DB11G/test01.dbf' SIZE 5120M
  3  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  4  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  5  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMent auto;

Tablespace created.

SQL> !ls -l test01*
-rw-r----- 1 oracle oinstall 5368717312 Jul 12 14:38 test01.dbf

SQL> !gzip -c test01.dbf > test01_empty.dbf.gz

SQL> !ls -l test01*
-rw-r----- 1 oracle oinstall 5368717312 Jul 12 14:38 test01.dbf
-rw-r--r-- 1 oracle oinstall   11389051 Jul 12 14:42 test01_empty.dbf.gz

SQL> create table reclaim_space tablespace test as(select * from dba_segments);

Table created.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

5933 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

11866 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

23732 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

47464 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

94928 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

189856 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

379712 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

759424 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes) from dba_segments where segment_name='RECLAIM_SPACE';

SUM(BYTES)
----------
 234881024

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

1518848 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

3037696 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ append */ into reclaim_space (select * from reclaim_space);

6075392 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes) from dba_segments where segment_name='RECLAIM_SPACE';

SUM(BYTES)
----------
1879048192

SQL> !gzip -c test01.dbf > test01_after_create_table_1800M.dbf.gz

SQL> !ls -l test01*
-rw-r--r-- 1 oracle oinstall  187996363 Jul 12 14:50 test01_after_create_table_1800M.dbf.gz
-rw-r----- 1 oracle oinstall 5368717312 Jul 12 14:50 test01.dbf
-rw-r--r-- 1 oracle oinstall   11389051 Jul 12 14:42 test01_empty.dbf.gz

SQL> select sum(bytes) from dba_segments where segment_name='RECLAIM_SPACE';

SUM(BYTES)
----------
1879048192

SQL> truncate table reclaim_space;               

Table truncated.

SQL> select sum(bytes) from dba_segments where segment_name='RECLAIM_SPACE';

SUM(BYTES)
----------
     65536

SQL> !gzip -c test01.dbf > test01_after_truncate.dbf.gz

SQL> !ls -l test01*
-rw-r--r-- 1 oracle oinstall  187996363 Jul 12 14:50 test01_after_create_table_1800M.dbf.gz
-rw-r--r-- 1 oracle oinstall  188015892 Jul 12 14:55 test01_after_truncate.dbf.gz
-rw-r----- 1 oracle oinstall 5368717312 Jul 12 14:57 test01.dbf
-rw-r--r-- 1 oracle oinstall   11389051 Jul 12 14:42 test01_empty.dbf.gz

SQL> alter table reclaim_space shrink space;
alter table reclaim_space shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table reclaim_space enable row movement;

Table altered.

SQL> alter table reclaim_space shrink space;

Table altered.

SQL> select sum(bytes) from dba_segments where segment_name='RECLAIM_SPACE';

SUM(BYTES)
----------
     65536

SQL> !gzip -c test01.dbf > test01_after_shrink.dbf.gz

SQL> !ls -l test01*
-rw-r--r-- 1 oracle oinstall  187996363 Jul 12 14:50 test01_after_create_table_1800M.dbf.gz
-rw-r--r-- 1 oracle oinstall  188013288 Jul 12 15:13 test01_after_shrink.dbf.gz
-rw-r--r-- 1 oracle oinstall  188015892 Jul 12 14:55 test01_after_truncate.dbf.gz
-rw-r----- 1 oracle oinstall 5368717312 Jul 12 14:57 test01.dbf
-rw-r--r-- 1 oracle oinstall   11389051 Jul 12 14:42 test01_empty.dbf.gz

Is it OK to assume that once Oracle used a block then even though it appears and is free at oracle level i.e. DBA_SEGMENT shows reduced size of a table after truncate but at OS level and for third party utilities those blocks are not empty. Thats why only rman can skip them i.e. from rel 10.2.

Please comment in a bit detail as this is not documented (or i couldn't find it) :)

Thanks a lot in advance.

Tom Kyte
July 12, 2012 - 6:12 pm UTC

it has nothing to do with skipping a block.


when you create a tablespace - we initialize the space - basically "blank it out". It is EXTREMELY compressable due to that.

then, you fill it up with stuff that doesn't compress nearly as well.

And when you truncate - IT IS STILL THERE - we consider the block free, we'll overwrite it later - but in the datafile - it still has your old data in it.

Since it isn't just "blanked out", it doesn't compress nearly as well anymore.

You are comparing compressing a file full of "blanks" to compressing War and Peace.

Block Compression after shrink

Nikhilesh, July 13, 2012 - 2:01 am UTC

Dear Tom,
Thanks for your reply. It indeed helped to understand truncate. But what about shrink? "Shrink" does actual row movement but still gives same results as of "truncate".

Does this mean "shrink" copy data from one block to another but doesn't blank out old block? Can u please explain how "shrink" works internally?

Thanks in advance.
Tom Kyte
July 16, 2012 - 1:52 pm UTC

it just copies data from one place to another - the deleted data on the old is still all there - we don't zero stuff out, that would be a waste of time.

Once a block is used after the datafile has been initialized - that block will have a bunch of junk on it forever (well, it'll get cleared out when it is reinitialized upon being pulled in under the high water mark of some segment - but that will just be for a short while)

It would be a total waste of time to "zero out" blocks. When you remove them from a segment - they are just there as they were - we don't do anything special to them until we need to reuse them.