OracleMove command Summary

Oracle Move command Summary

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

1. Create a table and insert data:

SQL> create table sjh. test1 (id int) tablespaceusers;

The table has been created.

SQL> insert into sjh. test1 values (1 );

One row has been created.

SQL> insert into sjh. test1 values (2 );

One row has been created.

SQL> insert into sjh. test1 values (3 );

One row has been created.

SQL> insert into sjh. test1 values (4 );

One row has been created.

SQL> insert into sjh. test1 values (5 );

One row has been created.

SQL> insert into sjh. test1 values (6 );

One row has been created.

SQL> insert into sjh. test1 values (7 );

One row has been created.

SQL> insert into sjh. test1 values (8 );

One row has been created.

S ql> commit;

Submitted.

SQL> select * from sjh. test1;

Eight rows have been selected.

2. view the rowid and block id information of the table:

SQL> select rowid, id from sjh. test1;

Eight rows have been selected.

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

-- Eight records are stored in one block (AAAABH)

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

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

Related File number: This number is unique for each file in a tablespace;

Block number: indicates the position of the block containing this row in the file;

Row number: Specifies the location of the row directory in the block header;

The internal data object number requires 32 bits, the related file number requires 10 bits, the block number requires 22 Bits, And the bit row number requires 16 bits. The total number is 80 bits or 10 bytes, ROWID uses a 64-based encoding scheme to show that this scheme uses six locations for data objects, serial three locations used for correlation file serial six locations used for Block Serial three locations used for row serial numbers 64-based encoding scheme use character A-Z a-z 0-9 + and/Total 64 characters,

Example: AAAMlQAAE AAAABH AAA

3. Perform some DML operations and observe whether the ROWID has changed:

SQL> delete from sjh. test1 where id = 1;

One row has been deleted.

SQL> delete from sjh. test1 where id = 3;

One row has been deleted.

SQL> delete from sjh. test1 where id = 5;

One row has been deleted.

SQL> commit;

Submitted.

SQL> select rowid, id from sjh. test1;

-- We can see that the ROWID remains unchanged.

4. Perform the MOVE operation and observe the ROWID:

SQL> alter table sjh. test1 move;

The table has been changed.

SQL> select rowid, id from sjh. test1;

-- The ROWID changes the BLOCK_ID from 65 to 73, and the BLOCK number changes from AAAABH to AAAABM.

5. Impact of move on HWM:

Here is an example of yjz0065:

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 ......

Total Bytes...

Unused Blocks ......

Unused Bytes...

Last Used Ext FileId...

Last Used Ext BlockId... 1294

Last Used Block...

Here HWM = 425-3 + 1 = 423

Then, perform the move operation on tableMY_OBJECTS:

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 ......

Total Bytes...

Unused Blocks ......

Unused Bytes...

Last Used Ext FileId...

Last Used Ext BlockId... 1584

Last Used Block...

We can see that the HWM of tableMY_OBJECTS is moved from 423 to 290, And the HWM of table is reduced! (Show_space is a custom process ).

Some Usage of Move:

The following describes the complete syntax of the move clause in altertable:

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 use move to move a table from the current tablespace to another tablespace, for example:

Alter table t move tablespace tablespace_name;

B. We can also use move to change the storage parameters of the existing block in the table, for example:

Alter table t move storage (initial 30 k next 50 k );

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

A. The index on the table requires rebuild:

As we have discussed earlier, after the move operation, the rowid of the data changes. We know that the index uses rowid to fetch the data rows. Therefore, the index on the table must be rebuilt.
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 ';

We can see that after tableMY_OBJECTS moves the table, inedx is in the UNUSABLE state. In this case, we can use the alterindex I _MY_OBJECTS rebuild online command, online rebuild of index I _MY_OBJECTS.

B. Locking table during move

When we move tableMY_OBJECTS, We can query the v $ locked_objects view and find that exclusivelock is added to tableMY_OBJECTS:
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 moving a table, we can only select it. Conversely, when one of our sessions performs DML operations on the table without commit, the table cannot be moved in another session. OtherwiseOracleAn error message like this is returned: ORA-00054: the resource is busy, requiring that NOWAIT be specified.

C. Question about the use of moving time and space:

When altertable move is used to reduce the HWM of a table, pay attention to it. In this case, the current tablespace needs to have 1 times the free space of the table for use:

SQL> CREATE TABLESPACE TEST1 DATAFILE 'd :\ORACLE\ ORADATA \ORACLE9I \ TEST1.dbf 'size 5 m uniformsize 128 K;

SQL> create table my_objects tablespace test1 asselect * from all_objects;

The 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 located in row 1st:

ORA-01652: unable to extend temp segments through 16 (in table space TEST1)

SQL> ALTER DATABASE DATAFILE 'd :\ORACLE\ ORADATA \ORACLE9I \ test1.dbf' RESIZE7M;

The database has been changed.

SQL> alter table MY_OBJECTS move;

The table has been changed.