Difference between ALTER TABLE MOVE and SHRINK

SQL> analyze table swap.emps2 compute statistics;

Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS,num_rows,AVG_ROW_LEN,ROW_MOVEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name=’EMPS2′;

TABLE_ TABLESP     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN ROW_MOVE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

—— ——- ———- ———— ———- ———– ——– ————– ———– ———– ———–

EMPS2  MYTBS         2267           37     219136          71 DISABLED        1048576     1048576           1  2147483645

SQL> select OWNER,SEGMENT_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name=’EMPS2′;

OWNE SEGME      BYTES     BLOCKS    EXTENTS

—- —– ———- ———- ———-

SWAP EMPS2   18874368       2304         18

SQL> declare

2

3     l_fs1_bytes number;

4     l_fs2_bytes number;

5     l_fs3_bytes number;

6     l_fs4_bytes number;

7     l_fs1_blocks number;

8     l_fs2_blocks number;

9     l_fs3_blocks number;

10     l_fs4_blocks number;

11     l_full_bytes number;

12     l_full_blocks number;

13     l_unformatted_bytes number;

14     l_unformatted_blocks number;

15  begin

16     dbms_space.space_usage(

17        segment_owner      => ‘SWAP’,

18        segment_name       => ‘EMPS2′,

19        segment_type       => ‘TABLE’,

20        fs1_bytes          => l_fs1_bytes,

21        fs1_blocks         => l_fs1_blocks,

22        fs2_bytes          => l_fs2_bytes,

23        fs2_blocks         => l_fs2_blocks,

24        fs3_bytes          => l_fs3_bytes,

25        fs3_blocks         => l_fs3_blocks,

26        fs4_bytes          => l_fs4_bytes,

27        fs4_blocks         => l_fs4_blocks,

28        full_bytes         => l_full_bytes,

29        full_blocks        => l_full_blocks,

30        unformatted_blocks => l_unformatted_blocks,

31        unformatted_bytes  => l_unformatted_bytes

32     );

33     dbms_output.put_line(‘ FS1 Blocks = ‘||l_fs1_blocks||’ Bytes = ‘||l_fs1_bytes);

34     dbms_output.put_line(‘ FS2 Blocks = ‘||l_fs2_blocks||’ Bytes = ‘||l_fs2_bytes);

35     dbms_output.put_line(‘ FS3 Blocks = ‘||l_fs3_blocks||’ Bytes = ‘||l_fs3_bytes);

36     dbms_output.put_line(‘ FS4 Blocks = ‘||l_fs4_blocks||’ Bytes = ‘||l_fs4_bytes);

37     dbms_output.put_line(‘Full Blocks = ‘||l_full_blocks||’ Bytes = ‘||l_full_bytes);

38  end;

39  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> /

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 0 Bytes = 0

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 0 Bytes = 0

Full Blocks = 2229 Bytes = 18259968   –ALL BLCKS FULL BETWEEN 75% to 100%

PL/SQL procedure successfully completed.

SQL> delete from swap.emps2

2  where employee_id in(101,111,121,131,141,151,161,171,181,191,201,109,119,129,139,149,159,169,179,189,105,115,125,135,145,165,155,175,185,195,205);

63488 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table swap.emps2 compute statistics;

Table analyzed.

SQL> select OWNER,SEGMENT_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name=’EMPS2′;

OWNE SEGME      BYTES     BLOCKS    EXTENTS

—- —– ———- ———- ———-

SWAP EMPS2   18874368       2304         18

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS,num_rows,AVG_ROW_LEN,ROW_MOVEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name=’EMPS2′;

TABLE_ TABLESP     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN ROW_MOVE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

—— ——- ———- ———— ———- ———– ——– ————– ———– ———– ———–

EMPS2  MYTBS         2267           37 155648          71 DISABLED        1048576     1048576           1  2147483645

After executing pl\sql proc

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 2229 Bytes = 18259968  –ALL BLKS 25% to 50% full

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 0 Bytes = 0

Full Blocks = 0 Bytes = 0

PL/SQL procedure successfully completed.

SQL> create table swap.emps3 as select * from swap.emps2;

Table created.

A new table emps3 is created and its export backup is taken

SQL>  alter table swap.emps2 shrink space;

alter table swap.emps2 shrink space

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table swap.emps2 enable row movement;

Table altered.

SQL> alter table swap.emps2 shrink space;

Table altered.

SQL> analyze table swap.emps2 compute statistics;

Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS,num_rows,AVG_ROW_LEN,ROW_MOVEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name=’EMPS2′;

TABLE_ TABLESP     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN ROW_MOVE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

—— ——- ———- ———— ———- ———– ——– ————– ———– ———– ———–

EMPS2  MYTBS         1424          112     155648          71 ENABLED         1048576     1048576           1  2147483645

SQL> select OWNER,SEGMENT_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name=’EMPS2′;

OWNE SEGME      BYTES     BLOCKS    EXTENTS

—- —– ———- ———- ———-

SWAP EMPS2   12582912       1536         12

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 2 Bytes = 16384

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 0 Bytes = 0

Full Blocks = 1422 Bytes = 11649024

SQL>  alter table swap.emps2 rename to emps2_1;

Table altered.

Emps2 export backup is imported

SQL> analyze table swap.emps2 compute statistics;

Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS,num_rows,AVG_ROW_LEN,ROW_MOVEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name=’EMPS2′;

TABLE_ TABLESP     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN ROW_MOVE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

—— ——- ———- ———— ———- ———– ——– ————– ———– ———– ———–

EMPS2  MYTBS         1636          668     155648          71 DISABLED       18874368     1048576           1  2147483645

SQL> select OWNER,SEGMENT_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name=’EMPS2′;

OWNE SEGME      BYTES     BLOCKS    EXTENTS

—- —– ———- ———- ———-

SWAP EMPS2   18874368       2304         18

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 0 Bytes = 0

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 27 Bytes = 221184

Full Blocks = 1577 Bytes = 12918784

PL/SQL procedure successfully completed.

SQL> alter table swap.emps2 move;

Table altered.

SQL> analyze table swap.emps2 compute statistics;

Table analyzed.

SQL>

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS,num_rows,AVG_ROW_LEN,ROW_MOVEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name=’EMPS2′;

TABLE_ TABLESP     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN ROW_MOVE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

—— ——- ———- ———— ———- ———– ——– ————– ———– ———– ———–

EMPS2  MYTBS         1612          692     155648          71 DISABLED       18874368     1048576           1  2147483645

SQL>

SQL> select OWNER,SEGMENT_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name=’EMPS2′;

OWNE SEGME      BYTES     BLOCKS    EXTENTS

—- —– ———- ———- ———-

SWAP EMPS2   18874368       2304         18

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 0 Bytes = 0

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 0 Bytes = 0

Full Blocks = 1584 Bytes = 12976128

PL/SQL procedure successfully completed.

SQL> alter table swap.emps2 enable row movement;

Table altered.

SQL> alter table swap.emps2 shrink space;

Table altered.

SQL> analyze table swap.emps2 compute statistics;

Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS,num_rows,AVG_ROW_LEN,ROW_MOVEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name=’EMPS2′;

TABLE_ TABLESP     BLOCKS EMPTY_BLOCKS   NUM_ROWS AVG_ROW_LEN ROW_MOVE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

—— ——- ———- ———— ———- ———– ——– ————– ———– ———– ———–

EMPS2  MYTBS         1612           52     155648          71 ENABLED        18874368     1048576           1  2147483645

SQL> select OWNER,SEGMENT_NAME,BYTES,BLOCKS,EXTENTS from dba_segments where segment_name=’EMPS2′;

OWNE SEGME      BYTES     BLOCKS    EXTENTS

—- —– ———- ———- ———-

SWAP EMPS2   13631488       1664         13

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 0 Bytes = 0

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 0 Bytes = 0

Full Blocks = 1584 Bytes = 12976128

===============SUMMARY=============================

BLOCKS EMPTY_BLOCKS   NUM_ROWS BYTES     BLOCKS    EXTENTS
2267            37     219136 18874368       2304         18
Full Blocks = 2229 Bytes = 18259968   –fully empty 37*8*31024  
63488 rows deleted.
FS2 Blocks = 2229 Bytes = 18259968  – BLKS 25% to 50% free
2267           37 155648 18874368       2304         18
its export backup is taken
alter table swap.emps2 shrink space;
1424          112     155648 12582912       1536         12
FS2 Blocks = 2 Bytes = 16384                           BLKS 25% to 50% free
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 1422 Bytes = 11649024                FULLY FREE BLKS
Emps2 export backup is imported (the old emps2 table was renamed before doing this)
1636          668     155648 18874368       2304         18
FS4 Blocks = 27 Bytes = 221184                       BLKS 75% to 100% free
Full Blocks = 1577 Bytes = 12918784                  FULLY FREE BLKS
alter table swap.emps2 move;
1612          692     155648 18874368       2304         18
Full Blocks = 1584 Bytes = 12976128                     FULLY FREE BLKS
alter table swap.emps2 shrink space;
1612           52     155648 13631488       1664         13
Full Blocks = 1584 Bytes = 12976128                          FULLY FREE BLKS
Follow

Get every new post delivered to your Inbox.