Oraclemove Command Summary

Oracle Move Command Summary

Starting at 8i, Oracle begins to provide a command for move. We usually use this command to move a table segment from one tablespace to another tablespace. Move is actually the physical copy data between blocks, so we can reduce the HWM of the table in this way. Let's start with an experiment to see how move moves data.

1. Build the table and insert the data:

Sql> CREATE TABLE sjh.test1 (id int) tablespaceusers;

Table has been created.

sql> INSERT INTO Sjh.test1 values (1);

1 lines have been created.

sql> INSERT INTO Sjh.test1 values (2);

1 lines have been created.

sql> INSERT into Sjh.test1 values (3);

1 lines have been created.

sql> INSERT INTO SJH.TEST1 values (4);

1 lines have been created.

sql> INSERT into SJH.TEST1 values (5);

1 lines have been created.

sql> INSERT INTO Sjh.test1 values (6);

1 lines have been created.

sql> INSERT INTO Sjh.test1 values (7);

1 lines have been created.

sql> INSERT INTO SJH.TEST1 values (8);

1 lines have been created.

S ql> commit;

Submit completed.

Sql> select * from Sjh.test1;

8 rows have been selected.

2. View ROWID information and block ID information for the table:

Sql> select Rowid,id from Sjh.test1;

8 rows have been selected.

Sql> Select Extent_id,file_id,relative_fno,block_id,blocks

-8 records are on one block (AAAABH)

Here is a brief introduction to ROWID's knowledge: rowID requires 10 bytes of storage on disk and uses 18 characters to show that it contains the following components:

Data Object number: Each data object, such as a table or index, is assigned this number when it is created, and the number is unique in the database;

Related file Number: This number is unique for each file in a table space;

Block Number: Represents the position of the block containing this row in the file;

Row number: Identifies the location of the row directory location of the header;

In the internal data object number needs 32 bits, the related file number needs 10 bits, the block number needs 22, the bit line number needs 16 bits, adds up is 80 or 10 bytes, ROWID uses the 64 cardinal encoding scheme to show that the scheme will use six locations for the data object, Number three locations for the related file number six locations for block number three locations for line numbering a 64-based encoding scheme uses characters A-Z-z 0-9 + and/A total of 64 characters,

As shown in the following example: Aaamlqaae Aaaabh AAA

3. Do some DML operations, and then observe the ROWID has not changed:

Sql> Delete from sjh.test1 where id=1;

1 rows have been deleted.

Sql> Delete from sjh.test1 where id=3;

1 rows have been deleted.

Sql> Delete from sjh.test1 where id=5;

1 rows have been deleted.

Sql> commit;

Submit completed.

Sql> select Rowid,id from Sjh.test1;

--we see rowid remain unchanged.

4. Do the move operation, and then observe the ROWID:

Sql> ALTER TABLE SJH.TEST1 move;

The table has changed.

Sql> select Rowid,id from Sjh.test1;

--rowid changed block_id from the original 65 to 73,block number from the original Aaaabh into AAAABM

5.move Impact on HWM:

Here is a reference to the Netizen yjz0065 example:

Sql> CREATE TABLE My_objects tablespace HWM

Sql> Delete from my_objects where rownum<10000;

9999 rows deleted

Sql> Select COUNT (*) from my_objects;

COUNT (*)

----------

sql> exec show_space (p_segname => ' my_objects ', P_owner => ' Dlinger ', P_type => ' TABLE ');

Total Blocks ................... 425

Total Bytes ... ................... 3481600

Unused Blocks ......... .......... ... 3

Unused Bytes .......... ......... .... 24576

Last Used Ext Fileid ............ 11

Last Used Ext blockid ............ 1294

Last Used blocks ................ ... 2

Here Hwm=425-3 + 1 = 423

Then move action on tablemy_objects:

Sql> ALTER TABLE my_objects move;

The table has 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 ............ 1584

Last Used blocks ................ ... 4

We can see that Tablemy_objects's HWM moved from 423 to 290,table HWM lowered! (Show_space is a custom process).

Some uses of move:

Here is the complete syntax for the move clause in altertable, and we'll explain some of them:

Move [ONLINE]

[Segment_attributes_clause]

[Data_segment_compression]

[Index_org_table_clause]

[{lob_storage_clause | varray_col_properties}

? 0?2?0?2?0?2 [{lob_storage_clause | varray_col_properties}] ...

]

[Parallel_clause]

A. We can move a table from the current tablespace to another tablespace, such as:

ALTER TABLE t move tablespace tablespace_name;

B. We can also use move to change the storage parameters of the block already in the table, such as:

ALTER TABLE T move storage (initial 30k next 50k);

C. In addition, the move operation can also be used to solve the problem of row migration in the table.

Some considerations for using move:

A. The index on table needs rebuild:

As we discussed before, the move operation, the rowid of the data has changed, we know that index is through the ROWID to fetch the data row, so, the index on the table must be 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_indexeswhere index_name= ' i_my_objects ';

As you can see from here, when tablemy_objects move, the status of the Inedx on the table is unusable, then we can use the AlterIndex i_my_objects rebuild online command, Rebuild the index i_my_objects online.

B. Locking a table when move

When we move to Tablemy_objects, the query v$locked_objects view can be found, Tablemy_objects is added Exclusivelock:

Sql>select object_id, session_id,oracle_username,locked_mode from V$locked_objects;

Sql> Select object_id from user_objects whereobject_name = ' my_objects ';

object_id

----------

This means that when the table is in move, we can only select it. Conversely, when one of our sessions is DML to a table and there is no commit, you cannot move the table in another session, or Oracle returns the error message: ORA-00054: Resources are busy, Requires the specified nowait.

C. Questions about space use in move:

When we use altertable move to reduce the hwm of a table, it is important to note that the current tablespace requires 1 time times the free space of the table for use:

sql> CREATE tablespace TEST1 datafile ' d:oracleoradataoracle9itest1.dbf ' SIZE 5M uniformsize 128K;

Sql> CREATE TABLE my_objects tablespace test1 asselect * from All_objects;

Table has been created.

Sql> Select bytes/1024/1024 from User_segmentswhere segment_name= ' my_objects ';

bytes/1024/1024

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

Sql> ALTER TABLE my_objects move;

ALTER TABLE my_objects move

ERROR is on line 1th:

ORA-01652: Unable to extend temp segment through 16 (in Tablespace TEST1)

sql> ALTER DATABASE datafile ' D:oracleoradataoracle9itest1. DBF ' resize7m;

The database has changed.

Sql> ALTER TABLE my_objects move;

The table has changed.