From 10g, Oracle begins to provide Shrink command, if the automatic segment space management (ASSM) is supported in our tablespace, you can use this characteristic reduction segment, that is, the HWM is reduced. Here you need to emphasize this new feature, 10G, only valid for the ASSM table space, otherwise ORA-10635: Invalid Segment or TableSpace Type.
In Part 4, we have discussed, how to investigate if Table under ASSM needs to recycle waste space, here we come to discuss such as a space for a waste of ASSM segment.
Similarly, we use the system view all_Objects to create a test table MY_OBJECTS on TableSpace ASSM, the content of this section, the experimental 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.2.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 remove some 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 see the data storage status of MY_OBJECTS:
SQL> EXEC SHOW_SPACE ('my_Objects', 'DLinger'); Total Blocks ............................ 680
Total Bytes .............................. 5570560
Unused blocks ......................... 1
Unused bytes ............................ 8192
Last used ext fileid .................... 6
Last Used Ext Blockid ................. 793
Last use block ....................... 4
The PL / SQL process has been 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: .............................
Free Space 75-100% blocks: ............ 229
Full Blocks: .............................. 45
Unformatted blocks: ................... 0
The PL / SQL process has been successfully completed.
Here, there is 679 blocks under the HWM of Table My_Objects, where free space has 205 blocks, including 180 50-75% Block, free space is 75-100% block. 229, Full Space's Block is only 45, in which case we need to reorganize this Table's existing data line.
To use Shink on ASSM, first we need to support the table to move, you can use this command to complete:
Alter Table My_Objects Enable Row Movement
Now, you can reduce the HWM of MY_OBJECTS, the recycling space, use the command:
Alter Table Bookings Shrink Space;
We look at the results of the experiment:
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 ............................ 265
Total Bytes .......................................................
Unused blocks ......................... 2
Unused bytes ............................ 16384
Last used ext fileid .................... 6
Last Used Ext Blockid ................... 308
Last use block ....................... 3
The PL / SQL process has been successfully completed.
SQL> EXEC SHOW_SPACE_ASSM ('my_Objects', 'DLinger'); Free Space 0-25% blocks: ................ 0
Free Space 25-50% blocks: ............. 1
Free Space 50-75% blocks: ............. 0
Free Space 75-100% blocks: .............. 0
Full Blocks: ............................ 249
Unformatted blocks: ................... 0
The PL / SQL process has been successfully completed.
After performing playing shrink commands, we can see that Table my_objects' hwm is now dropped to 264 position, and the space usage of Block under HWM, 249 Block's block, free space is 25-50% block only 1.
Let's discuss the implementation mechanism of Shrink, we also use the experiments that discuss the MOVE mechanism to observe.
SQL> CREATE TABLE TEST_HWM (ID INT, Name Char (2000)) TABLESPACE ASSM;
Table created
Insert the following data to 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 this Table's RowID and Block ID and information:
SQL> SELECT ROWID, ID, NAME FROM TEST_HWM;
Rowid ID Name
-----------------------------
Aaanhqaagaaaafhaaa 1 aa
Aaanhqaagaaaafhaab 2 BB
Aaanhqaagaaaafhaac 2 CC
Aaanhqaagaaaafiaaa 3 DD
Aaanhqaagaaaaafiaab 4 DS
Aaanhqaagaaaafiaac 5 DSS
Aaanhqaagaaaafjaaaa 6 DSS
Aaanhqaagaaaafjaab 7 ESS
Aaanhqaagaaaaafjaac 8 ES
Aaanhqaagaaaafkaaa 9 ES
Aaanhqaagaaaaafkaab 10 ES
11 Rows SELECTEDSQL> 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 6 324 5
1 6 6 329 5
Then remove 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 information of 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
Aaanhqaagaaaafjaaaa 6 DSS
Aaanhqaagaaaafkaaa 9 ES
Aaanhqaagaaaaafkaab 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 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 four consecutive blocks such as AAAAFH, AAAAFI, AAAAfj, Aaaafk.
SQL> EXEC SHOW_SPACE_ASSM ('Test_hwm', 'DLinger');
Free Space 0-25% blocks: ................ 0
Free Space 25-50% blocks: ............. 1
Free Space 50-75% blocks: ............... 3
Free Space 75-100% blocks: .............. 3
Full Blocks: ............................ 0
Unformatted blocks: ................... 0
Through Show_Space_assm, we can see the current four Block space usage, AAAAFH, AAAAFI, AAAAfj, all have a line of data, we speculate that free space is 50-75% of 3 blocks is these three blocks, then Free Space is 25-50% of 1 Block is AAAAFK, the remaining free space is 75-100% of 3 blocks, which is formatted under the HWM that has not been used. (About ASSM HWM Mobile We have discussed in detail, when EXTENT is not greater than 16 Block, is moving in an extent as unit), then we perform Shtink's operations for 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
Aaanhqaagaaaafjaaaa 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 6 324 5
1 6 6 329 5
When the Shrink operation is performed, interesting phenomenon appears. Let's take a look at how Oracle moved data, and the situation here is not the same. We know that when the move operation, all rows of ROWID have changed, and the block of the block is also changing, but all the order of physical storage has changed, so we have received the conclusion, Oracle With blocks, data COPY is carried out. So after Shrink, we found that the RowID of some line data has changed. At the same time, the order of physical storage of some line data has also changed, and the area of the block in Table is not changed, which means that Shrink is only moving The row data part of Table is to complete the release space, and this process is done in the Block currently used by Table.
So what is the process of Oracle's specific mobile line data? According to such an experimental result, we can guess:
Oracle is to move data in behavioral units. Oracle starts moving from the last row of data stored in Table, starting from the current Table first Block to start searching space, so before Shrink, the line data (10, ES) of ROWNUM = 10, is moved to block aaaafh, write Behind the data of (1, AA), so the ROWNUM and ROWID of (10, ES) have changed simultaneously. Then (9, ES) this line of data, repeat the above process. This is the rule of Oracle's approximately followed the row data, then the algorithm of the specific mobile line data is more complicated, including the order of the insert data to the ASSERT data using Block's order, everyone has Interests can be studied by themselves, here we don't have much discussion. We can also sell this Table on the Table in Shrink Table.
Alter Table My_Objects Shrink Space Cascade;
Similarly, this operation is only available when INDEX on the table is also ASSM.
Regarding the problem of logs, we contracted two tables of the same amount of data and distribution, and the Redo Size that is generated under Move and Shrink (there is no INDEX on 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 * ALL_OBJECTS WHERE ROWNUM <20000
Table created
SQL> CREATE TABLE MY_OBJECTS1 TABLESPACE HWM As SELECT * ALL_OBJECTS WHERE ROWNUM <20000
Table created
SQL> 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 completion
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
For Table My_Objects, Shrink, Generated 32579712 - 27808792 = 4770920, about 4.5m REDO; Move for Table My_Objects1, Generated 32676784-32579712 = 97072, about 95K REDO Size. So, compared with Move, Shrink's log is much larger.
SHRINK's questions:
I. After SHRINK, INDEX needs Rebuild:
Because Shrink's operation will also change row data, then if there is index on the table, Shrink Table will not change to unusable? Let's see such an experiment, and build a test table of MY_OBJECTS:
Create Table My_Objects TableSpace Assm As SELECT * ALL_OBJECTS WHERE ROWNUM <20000
CREATE INDEX I_MY_OBJECTS ON MY_OBJECTS (Object_ID);
Delete from my_Objects where object_name like '% C%';
Delete from my_objects where object_name like '% u%'
Now let's come to Shrink Table My_Objects:
SQL> ALTER TABLE MY_OBJECTS Enable Row Movement
Table altered
SQL> ALTER TABLE MY_OBJECTS SHRINK SPACE;
Table altered
SQL> SELECT INDEX_NAME, Status from user_indexes where index_name = 'i_my_Objects';
Index_name status ------------------------------------ i_my_objects valid
We have found that the status of Index on Table My_Objects is Valid, which estimates that Shrink is moving the data RowID of the corresponding row on Index together when Shrink is moving. We believe that this is an improvement in Rebuild Index after Move operation. But if there is more in a table, we know that maintaining the cost of Index is relatively high, the cost used to maintain Index during the Shrink will also be relatively high.
b. Shrink's LOCK
How to lock TABLE when making Shrink for Table? When we use the Table My_Objects for Shrink, Query V $ locked_Objects view can find that row-x (sx) LOCK is added to Table my_Objects:
SQL> SELECT Object_ID, session_id, oracle_username, Locked_Mode from V $ locked_Objects;
Object_id session_id oracle_username locked_mode
---------- -------------------------------------
55422 153 DLINGER 3
SQL> SELECT OBJECT_ID from user_Objects where object_name = 'my_objects';
Object_id
------------
55422
So, when Table is under Shrink, we can perform DML operations for TABLE.
c. SHRINK's requirements for space
We discussed the mobile mechanism of Shrink's data, since Oracle moved the line data, then Shrink's operation would not be like Move, Shrink does not need to use additional idle space.
