In-depth discussion on the Shrink mechanism of Oracle Database 10 GB

Starting from 10 Gb, oracle began to provide Shrink commands. If our tablespace supports automatic segment space management (ASSM), we can use this feature to narrow down the segment, that is, reduce the HWM. Here we need to emphasize that this new 10g feature is only valid for the ASSM tablespace, otherwise the ORA-10635: Invalid segment or tablespace type will be reported.

Here, we will discuss how to recycle wasted space for an ASSM segment.

Similarly, we use the System View all_objects to create the test table my_objects on tablespace ASSM. The experiment environment is oracle10.1.0.2:

SQL> select * from v $ version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Prod

PL/SQL Release 10.1.0.2.0-Production

CORE 10.1.0.2.0 Production

 

TNS for 32-bit Windows: Version 10.1.0.2.0-Production

NLSRTL Version 10.1.0.2.0-Production

 

SQL> select TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT,

2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT

3 from dba_tablespaces where TABLESPACE_NAME = ASSM;

 

TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT

----------------------------------------------------------------------------------

ASSM 8192 LOCAL UNIFORM AUTO

 

SQL> create table my_objects tablespace assm

2 as select * from all_objects;

Table created

 

 

Then we randomly delete part of the data from table MY_OBJECTS:

SQL> select count (*) from my_objects;

COUNT (*)

----------

47828

SQL> delete from my_objects where object_name like % C %;

16950 rows deleted

 

SQL> delete from my_objects where object_name like % U %;

4503 rows deleted

 

SQL> delete from my_objects where object_name like % A %;

6739 rows deleted

 

 

Now we use show_space and show_space_assm to check the data storage status of my_objects:

SQL> exec show_space (MY_OBJECTS, DLINGER );

Total Blocks ......

Total Bytes...

Unused Blocks ......

Unused Bytes...

Last Used Ext FileId...

Last Used Ext BlockId... 793

Last Used Block...

 

The PL/SQL process is successfully completed.

 

SQL> exec show_space_assm (MY_OBJECTS, DLINGER );

Free space 0-25% Blocks: ...... 0

Free space 25-50% Blocks: ...... 205

Free space 50-75% Blocks: ...... 180

Free space 75-100% Blocks: ...... 229

Full Blocks: ......

Unformatted blocks :...

 

The PL/SQL process is successfully completed.

 

 

Here, there are 679 blocks under the HWM of table my_objects. Among them, there are 50% blocks with free space 25-205, and 75% blocks with free space 50-180, there are 100% blocks with free space 75-229 and 45 blocks with full space. In this case, we need to reorganize the existing data rows of this table.

To use shink on assm, first we need to make the table support row movement. We can use this command to complete:

Alter table my_objects enable row movement;

Now we can reduce the HWM of my_objects and recycle space. Run the following command:

Alter table bookings shrink space;

Let's take a look at the experiment results:

SQL> alter table my_objects enable row movement;

The table has been changed.

 

SQL> alter table my_objects shrink space;

The table has been changed.

 

SQL> exec show_space (MY_OBJECTS, DLINGER );

Total Blocks ......

Total Bytes...

Unused Blocks ......

Unused Bytes...

Last Used Ext FileId...

Last Used Ext BlockId... 308

Last Used Block...

 

The PL/SQL process is successfully completed.

 

SQL> exec show_space_assm (MY_OBJECTS, DLINGER );

Free space 0-25% Blocks: ...... 0

Free space 25-50% Blocks: ......

Free space 50-75% Blocks: ...... 0

Free space 75-100% Blocks: ...... 0

Full Blocks: ......

Unformatted blocks :...

 

The PL/SQL process is successfully completed.

 

 

After running the shrink command, we can see that the HWM of table my_objects has now dropped to the position of 264, and the space usage of blocks under HWM has 249 blocks in full space, free space has only one Block ranging from 25 to 50%.

 

Next we will discuss the shrink implementation mechanism. We will also use the experiment discussing the move mechanism to observe it.

SQL> create table TEST_HWM (id int, name char (2000) tablespace ASSM;

 

Table created

 

 

Insert the following data into table test_hwm:

Insert into TEST_HWM values (1, aa );

Insert into TEST_HWM values (2, bb );

Insert into TEST_HWM values (2, cc );

Insert into TEST_HWM values (3, dd );

Insert into TEST_HWM values (4, ds );

Insert into TEST_HWM values (5, dss );

Insert into TEST_HWM values (6, dss );

Insert into TEST_HWM values (7, ess );

Insert into TEST_HWM values (8, es );

Insert into TEST_HWM values (9, es );

Insert into TEST_HWM values (10, es );

 

Let's take a look at the rowid and block ID and information of this table:

SQL> select rowid, id, name from TEST_HWM;

 

ROWID ID NAME

------------------------------------------

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFHAAB 2 bb

AAANhqAAGAAAAFHAAC 2 cc

AAANhqAAGAAAAFIAAA 3 dd

AAANhqAAGAAAAFIAAB 4 ds

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFJAAB 7 ess

AAANhqAAGAAAAFJAAC 8 es

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

 

11 rows selected

 

SQL> select EXTENT_ID, FILE_ID, RELATIVE_FNO, BLOCK_ID, BLOCKS

2 from dba_extents where segment_name = TEST_HWM;

 

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

----------------------------------------------------

0, 6, 324, 5

1 6 6 329 5

 

 

Then delete some data from table test_hwm:

Delete from TEST_HWM where id = 2;

Delete from TEST_HWM where id = 4;

Delete from TEST_HWM where id = 3;

Delete from TEST_HWM where id = 7;

Delete from TEST_HWM where id = 8;

 

Observe the rowid and blockid of table test_hwm:

SQL> select rowid, id, name from TEST_HWM;

 

ROWID ID NAME

-----------------------------------------

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

 

SQL> select EXTENT_ID, FILE_ID, RELATIVE_FNO, BLOCK_ID, BLOCKS

2 from dba_extents where segment_name = TEST_HWM;

 

EXTENT_ID FILE_ID RELATIVE_FNO