Alter table move VS Row Shrink

Test Case to understand alter table move and Row Shrink

Create Table:

SQL> create table test ( x number ) segment creation immediate storage ( initial 10M next 10M ) ;

Table created.

SQL> select blocks,extents from dba_segments where segment_name=’TEST’;

BLOCKS EXTENTS
———- ———-
1280 1

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks,extents from dba_segments where segment_name=’TEST’;

BLOCKS EXTENTS
———- ———-
1280 1

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘TEST’;

BLOCKS EMPTY_BLOCKS
———- ————
0 1279

Insert rows :

SQL> insert into test
select rownum
from dual
connect by level <= 100000; 2 3 4

100000 rows created.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks,extents from dba_segments where segment_name=’TEST’;

BLOCKS EXTENTS
———- ———-
1280 1

HWM:

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘TEST’;

BLOCKS EMPTY_BLOCKS
———- ————
155 1124

Delete rows from table:

SQL> delete from test where rownum <= 80000;

80000 rows deleted.

SQL> SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> SQL> select blocks,extents from dba_segments where segment_name=’TEST’;

BLOCKS EXTENTS
———- ———-
1280 1

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘TEST’;

BLOCKS EMPTY_BLOCKS
———- ————
155 1124

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;

USED_BLOCKS
———–
31

Move table:

SQL> alter table test move tablespace users;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘TEST’;

BLOCKS EMPTY_BLOCKS
———- ————
49 1231

Segment size remains same

SQL> select blocks,extents from dba_segments where segment_name=’TEST’;

BLOCKS EXTENTS
———- ———-
1280 3

Shrink table:

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

Segment size in Blocks:

SQL> select blocks,extents from dba_segments where segment_name=’TEST’;

BLOCKS EXTENTS
———- ———-
56 1

HWM:

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘TEST’;

BLOCKS EMPTY_BLOCKS
———- ————
49 7

Reference:

JMartinez comment

http://www.dbforums.com/oracle/1627103-alter-table-move-vs-shrink-space.html

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s