Starting from 8i, Oracle starts providing Move commands. We usually use this command to move a Table Segment from a tableSpace to another TABLESPACE.
Move is actually a physical COPY data between blocks, then we can reduce Table's HWM in this way. Let's take a look at how Move moves data. Create Table Test_hwm, INSERT Some data:
SQL> CREATE TABLE TEST_HWM (ID INT, Name Char (2000)) TABLESPACE HWM;
Table created
Our data in Table Test_hwm is as follows:
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
----------------------------------------
Aaah7jaalaaaaaaaaa 1 aa
Aaah7jaalaaaaaaaab 2 BB
Aaah7jaalaaaaaaaac 2 cc
Aaah7jaaaaaaavaaa 3 DD
Aaah7jaalaaaaavaab 4 DS
Aaah7jaalaaaaavaac 5 DSS
Aaah7jaalaaaaaaaaa 6 DSS
Aaah7jaalaaaaaaawaab 7 ESS
Aaah7jaalaaaaaaaaac 8 ES
Aaah7jaalaaaaaxaaa 9 ES
Aaah7jaaaaaaaxaab 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 11 11 19 5
Here, simply introduce the relevant knowledge of RowID:
RowID requires 10 bytes of storage on disk and uses 18 characters to display it contains the following components:
Data Object Number: Each data object is assigned to have this number when creating is created, and this number is unique in the database;
Related file number: This number is unique to each file in a table space;
Block Number: Indicates the location of the block containing this line in the file;
Number: Identifies the location of the bank's line directory location;
The internal data object number requires 32 bits, the relevant file number requires 10 digits, the block number requires 22, the bit line number requires 16 bits, add up to 80 bits or 10 bytes, and RowID uses 64-based coding scheme. Displaying this scenario for data objects, numbered three locations for related file numbers Six positions for block numbers Three locations for row numbers with 64 coding scheme using characters AZ AZ 0-9 And / 64 characters, as shown in the following example:
Aaah7j aal aaaaau aaa
In this example
AAAH7J is the data object number
AAL is the relevant file number
AAAAAU is a block number
AAA is the number
So, according to the data of Rowid, we can see that this 11-line data is distributed in the four blocks of Aaaaau, Aaaaav, Aaaaaw, Aaaaax.
Then we delete 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;
Delete from test_hwm where id = 9;
Let's take a look at this Table RowID and Block ID and information:
SQL> SELECT ROWID, ID, NAME FROM TEST_HWM;
Rowid ID Name
----------------------------------- ------------- -
Aaah7jaalaaaaaaaaa 1 aa
Aaah7jaalaaaaavaac 5 DSS
Aaah7jaalaaaaaaaaa 6 DSS
Aaah7jaaaaaaaxaab 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 11 11 19 5 Here, we can see that the RowId of the data has not changed. We can see that the 4 lines of data remain in Aaaaau, AAAAAV, AAAAAW, AAAAX. Next, we will follow the Table Test_hwm, then observe the information about RowID, Blockid:
SQL> ALTER TABLE TEST_HWM MOVE;
Table altered
SQL> SELECT ROWID, ID, NAME FROM HWM;
Rowid ID Name
-------------------------------------------------------------------------------------------------------------------------- -
Aaah7naaaaaaaaaa 1 aa
Aaah7naalaaaanraab 5 DSS
Aaah7naaaaaanraac 6 DSS
Aaah7naaaaaaaaaa 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 11 11 874 5
We can see that after the Table Test_hwm is Move, the shutier of the table has changed, and the data of the data has naturally changed. From the above results, we can see that the data of Table Test_hwm is now distributed in Aaaaanr, Aaaans, two blocks. However, in the order of the RowID of these four lines, the four lines of data in the storage order in Table does not change. This also proves that Move is a physical COPY data between blocks.
Let's take a look at the MOVE operation for Table's HWM's location. We also use the system view all_Objects to create test Table my_objects, then DELETE 9999 row data:
SQL> CREATE TABLE MY_OBJECTS TABLESPACE HWM
2 as select * from all_Objects;
SQL> delete from my_objects where rownum <10000;
9999 ROWS DELETED
SQL> SELECT Count (*) from my_objects;
Count (*)
------------
21015
SQL> EXEC SHOW_SPACE (p_segname => 'my_Objects', p_owner => 'DLinger', p_type => 'Table');
Total Blocks ............................ 425
Total Bytes ............................. 3481600
Unused blocks ......................... 3UNUSED BYTES ................... ......... 24576
Last Used Ext FileId .................... 11
Last Used Ext Blockid ................... 1294
Last use block ....................... 2
Here hwm = 425 - 3 1 = 423
Then the Table My_Objects is then Move:
SQL> ALTER TABLE MY_OBJECTS MOVE;
The table has been changed.
SQL> EXEC SHOW_SPACE (p_segname => 'my_Objects', p_owner => 'DLinger', p_type => 'Table');
Total Blocks ............................ 290
Total Bytes ........................... 2375680
Unused blocks ......................... 1
Unused bytes ............................ 8192
Last Used Ext FileId .................... 11
Last Used Ext Blockid ...........................................
Last use block ....................... 4
We can see that Table my_Objects hwm moves from 423 to 290, and Table's HWM is lowered!
We can also use other methods to reduce Table's HWM, such as CTAS, Insert Into, etc., then Move operations are relatively small for the Redo Logo's writing and other ways, we do not list specific The experimental results, everyone is interested, you can do it yourself.
Above We discussed the Move's implementation mechanism and how to reduce Table's HWM, here, we add some other uses of Move, and some of the questions you should pay attention to using Move.
Move's usage
The following is the full grammar of the Move clause in the alter table, we introduce some of them:
Move [Online]
[segment_attributes_clause]
[data_segment_compression]
[INDEX_ORG_TABLE_CLAUSE]
[{LOB_STORAGE_CLASE | VARRAY_COL_PROPERTIES}
[{LOB_STORAGE_CLASE | VARRAY_COL_PROPERTIES}] ...
]
[parallel_clause]
a. We can use Move to move a table from the current tableSpace to another TABLESPACE, such as:
Alter Table Tby TableSpace TableSpace_name;
b. We can also use Move to change the storage parameters of the Table Existing Block, such as: Alter Table T Move Storage (Initial 30K Next 50K);
In addition, the MOVE operation can also be used to solve the problem of row migration in Table.
Some precautions using MOVE
a. INDEX on table Need REBUILD: After we discussed in front, the data RowID has changed, and we know that index is to fetch data through RowID, so INDEX on Table is necessary to rebuild . SQL> CREATE INDEX I_MY_OBJECTS ON MY_OBJECTS (Object_ID); Index Created
SQL> ALTER TABLE MY_OBJECTS MOVE; TABLE Altered
SQL> SELECT INDEX_NAME, Status from user_indexes where index_name = 'i_my_Objects';
Index_name status ------------------------------ -------- i_my_objects unusable can be seen from here, when Table After MY_OBJECTS performs Move operation, the state of the INEDX on the table is unusable. At this time, we can use the ALTER INDEX I_MY_Objects Rebuild Online command to online Rebuild.
b. Locks for Table when Move
When we perform Move operations for Table My_Objects, Query V $ located_Objects view can be found, Table my_objects adds Exclusive Lock:
SQL> SELECT Object_ID, session_id, oracle_username, Locked_Mode from V $ locked_Objects;
Object_id session_id oracle_username locked_mode
---------- -------------------------------------
32471 9 DLinger 6
SQL> SELECT OBJECT_ID from user_Objects where object_name = 'my_objects';
Object_id
------------
32471
This means that TABLE can only perform SELECT operations when performing MOVE operations. Conversely, when a session is DML operation for Table and does not have commits, it is not possible to make Move operations in another session, otherwise Oracle will return such an error message: ORA-00054: Resources is busy Request for NOWAIT.
c. Questions about the use of Move time space:
When we use ALTER TABLE MOVE to reduce Table HWM, you need to pay attention, at this time, there is a need to have 1 times of Table's idle space for use in the current tableSpace for use:
SQL> CREATE TABLESPACE TEST1
2 DataFile 'D: /oracle/oradata/oracle9i/test1.dbf' size
5M
3 uniform size 128k;
SQL> CREATE TABLE MY_OBJECTS TABLESPACE TEST1 AS SELECT * from ALL_OBJECTS; the table has been created.
SQL> SELECT BYTES / 1024/1024 from user_segments where segment_name = 'my_Objects';
BYTES / 1024/1024
---------------
3.125
SQL> ALTER TABLE MY_OBJECTS MOVE;
Alter Table My_Objects Move
*
Error is located on the first line:
ORA-01652: The TEMP segment cannot be expanded through 16 (in Test1 Test1 in Table Space)
SQL> ALTER DATABASE
2 DataFile 'D: /oracle/oradata/oracle9i/test1.dbf' Resize
7m
;
The database has been changed.
SQL> ALTER TABLE MY_OBJECTS MOVE;
The table has been changed.
