A small experiment about alter table move

A small experiment about alter table move.
SQL> Create Table Test
2
3 select * From dba_objects
4 where 1 = 0;

Table created.

SQL> select extents, segment_name from dba_segments where
2 segment_name = 'test' and wner = 'sys ';

Extents segment_name
------------------------------
1 Test

SQL> insert into test
2 select * From dba_objects;

16603 rows created.

SQL> select extents, segment_name from dba_segments where
2 segment_name = 'test' and wner = 'sys ';

Extents segment_name
------------------------------
8 Test

SQL> Delete from test;

16603 rows deleted.

SQL> select extents, segment_name from dba_segments where
2 segment_name = 'test' and wner = 'sys ';

Extents segment_name
------------------------------
8 Test

SQL> ALTER TABLE test move;

Table altered.

SQL> select extents, segment_name from dba_segments where
2 segment_name = 'test' and wner = 'sys ';

Extents segment_name
------------------------------
1 Test

SQL> insert into test
2 select * From dba_objects;

16603 rows created.

SQL> select bytes, extents, segment_name from dba_segments
2 Where segment_name = 'test' and wner = 'sys ';

Bytes extents segment_name
----------------------------------------
2424832 8 Test

SQL> set autotrace traceonly
SQL> select * from test;

16603 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement ptimizer = choose
1 0 Table Access (full) of 'test'

Statistics
----------------------------------------------------------
0 recursive cballs
4 dB block gets
1315 consistent gets
0 physical reads
0 redo size
1711517 bytes sent via SQL * Net to client
89944 bytes encoded ed via SQL * Net From Client
1108 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
16603 rows processed

SQL> Delete from test;

16603 rows deleted.

Execution Plan
----------------------------------------------------------
0 Delete statement ptimizer = choose
1 0 Delete of 'test'
2 1 Table Access (full) of 'test'

Statistics
----------------------------------------------------------
118 recursive cballs
18207 db block gets
263 consistent gets
0 physical reads
6745400 redo size
648 bytes sent via SQL * Net to client
445 bytes encoded ed via SQL * Net From Client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
16603 rows processed

SQL> select * from test;

No rows selected

Execution Plan
----------------------------------------------------------
0 SELECT statement ptimizer = choose
1 0 Table Access (full) of 'test'

Statistics
----------------------------------------------------------
0 recursive cballs
4 dB block gets
229 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL * Net to client
277 bytes encoded ed via SQL * Net From Client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
0 rows processed

SQL> ALTER TABLE test move;

Table altered.

SQL> select * from test;

No rows selected

Execution Plan
----------------------------------------------------------
0 SELECT statement ptimizer = choose
1 0 Table Access (full) of 'test'

Statistics
----------------------------------------------------------
0 recursive cballs
4 dB block gets
0 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL * Net to client
277 bytes encoded ed via SQL * Net From Client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
0 rows processed

SQL>

Conclusion: alter table... move not only pulls the hwm back, but also recycles the extent

Alter table move

In Oracle9i, the table space needs to be reduced after the data of a large table is deleted,

You can use alter table tabname move (tablespace tbs_name ),

Note: At this time, you must rebuild the index. Because after moving, the rowid of the data changes.