Oracle 10 Gb automatic segment space management (assm)

Starting from 10 Gb, Oracle began to provide shrink commands. If our tablespace supports automatic segment space management (assm), we can make

Use this feature to narrow down the segment, that is, to reduce hwm. here we need to emphasize that the new 10 Gb feature is only valid for the assm tablespace, otherwise it will be reported

ORA-10635: Invalid segment or tablespace type.

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 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 has been completed successfully. Here, there are 679 blocks in the hwm of table my_objects, of which, free space is 25-

There are 50% blocks in 205, 75% blocks in free space 50-180, 100% blocks in free space 75-229, and blocks in full space.

Space has only 45 blocks. 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 has been successfully completed. After running the shrink command, we can see that the hwm of table my_objects is now reduced

264 location, and the space usage of the block in hwm, the full space has 249 blocks, and the free space is 25-50%

There is only one block.

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 inserts the following data into Table test_hwm:

Insert into test_hwm values (1, 'A ');

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 and 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 block_id Blocks

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

0, 6, 324, 5

1 6 6 329 5 from the above information, we can see that in Table test_hwm, the remaining data is distributed in

Aaaafh, aaaafi, aaaafj, and aaaafk are four consecutive blocks.

SQL> exec show_space_assm ('test _ hwm ', 'dlinger ');

Free Space 0-25% blocks: ...... 0

Free Space 25-50% blocks: ......

Free Space 50-75% blocks: ......

Free Space 75-100% blocks: ......

Full blocks: ......

Unformatted blocks :...

, Aaaafh, aaaafi, and aaaafj each have a row of data. We assume that the three blocks with free space 50-75% are the three blocks.

Block, so one block with free space 25-50% is aaaafk, and the remaining three blocks with free space 75-100% are

Formatted unused blocks in hwm. (We have discussed in detail about the hwm movement in assm. in extent

When there are more than 16 blocks, they are moved in the unit of one extent)

Then, we perform the shtink operation on table my_objects:

SQL> ALTER TABLE test_hwm enable row movement;

Table altered

SQL> ALTER TABLE test_hwm shrink space;

Table altered

SQL> select rowid, ID, name from test_hwm;

Rowid ID name

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

Aaanhqaagaaaafhaaa 1 aa

Aaanhqaagaaaafhaab 10 es

Aaanhqaagaaaafhaad 9 es

Aaanhqaagaaaafiaac 5 DSS

Aaanhqaagaaaafjaaa 6 DSS

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 when the shrink operation is executed, an interesting phenomenon occurs. Let's take a look at how Oracle moves row data.

The condition is different from that of move. We know that during the move operation, the rowid of all rows changes, and

The block region has also changed, but the physical storage sequence of all rows has not changed. Therefore, we can conclude that Oracle uses

Block is the unit, and the data between blocks is copied. Then, after shrink, we find that the rowid of some row data has changed.

The order of physical storage of some row data has also changed, while the block region where the table is located has not changed. This shows that

, Shrink only moves part of the row data in the table to release the space, and this process is currently used in the table

Block.

So what is the specific process of Oracle moving row data? Based on the experiment results, Let's guess:

Oracle moves data in the unit of behavior. Oracle moves data from the last row of data stored in the current table, and is used first from the current table.

So, the row (10, ES) of rownum = 10 before shrink is moved to block aaaafh.

, Written to the end of (1, AA) This line of data, so the rownum and rowid of (10, ES) change at the same time. Then (9, ES) This line of data

Repeat the above process. This is a general rule for Oracle to move row data from the back to the back, so the specific algorithm for moving row data is more complex.

Complex algorithms, including the block sequence algorithm used to insert data into the table of assm, are also complicated. If you are interested, you can study it on your own.

We will not discuss it here.

We can also use the index on the table shrink at the same time as the shrink table:

Alter table my_objects shrink space cascade;

Similarly, this operation can be used only when the index on the table is assm.

For the log, we compared the two tables with the same data volume and distribution, and the redo generated under move and shrink.

Size (if there is no index on the table ):

SQL> select tablespace_name, segment_space_management from dba_tablespaces

2 Where tablespace_name in ('assm ', 'hwm ');

Tablespace_name segment_space_management

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

Assm auto

Hwm Manual

SQL> Create Table my_objects tablespace assm as select * From all_objects where rownum CREATE TABLE my_objects1 tablespace hwm as select * From all_objects where rownum select Bytes/1024/1024 from user_segments where segment_name = 'my _ objects';

Bytes/1024/1024

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

2.1875

SQL> Delete from my_objects where object_name like '% C % ';

7278 rows deleted

SQL> Delete from my_objects1 where object_name like '% C % ';

7278 rows deleted

SQL> Delete from my_objects where object_name like '% u % ';

2732 rows deleted

SQL> Delete from my_objects1 where object_name like '% u % ';

2732 rows deleted

SQL> commit;

Commit complete

SQL> ALTER TABLE my_objects enable row movement;

Table altered

SQL> select value from V $ mystat, V $ statname

2 Where V $ mystat. Statistic # = V $ statname. Statistic #

3 and V $ statname. Name = 'redo size ';

Value

----------

27808792

SQL> ALTER TABLE my_objects shrink space;

Table altered

SQL> select value from V $ mystat, V $ statname

2 Where V $ mystat. Statistic # = V $ statname. Statistic #

3 and V $ statname. Name = 'redo size ';

Value

----------

32579712

SQL> ALTER TABLE my_objects1 move;

Table altered

SQL> select value from V $ mystat, V $ statname

2 Where V $ mystat. Statistic # = V $ statname. Statistic #

3 and V $ statname. Name = 'redo size ';

Value

----------

32676784 perform shrink on table my_objects and generate a redo of 32579712-27808792 = 4770920, approximately MB.

; Move Table my_objects1 to generate a redo size of 32676784-32579712 = 97072, about 95k. Then

In comparison, the shrink log is much larger.

Shrink:

A. Does the index after shrink need rebuild:

Because the shrink operation also changes the rowid of the row data, if there is an index on the table, will the index change after the shrink table?

Unusable: Let's take a look at this experiment to build a test table of my_objects:

Create Table my_objects tablespace assm as select * From all_objects where rownum <20000;

Create index I _my_objects _ disibledevent = 'my _ objects ';

Object_id

----------

55422. When the table is performing shrink, we can perform DML operations on the table.

C. Shrink Space Requirements

We have discussed the shrink data movement mechanism. Since Oracle moves data from the back to the front, the shrink operations will not be like

Similar to move, shrink does not need additional free space. 9:56:59