move table 的注意事项

1.table上的index要rebuild

SQL> r
1 select index_name,table_name,column_name from user_ind_columns
2* where table_name='T'

INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------
T_OBJECT_ID_INDEX T OBJECT_ID

SQL> select index_name,status from user_indexes where index_name='T_OBJECT_ID_INDEX';

INDEX_NAME STATUS
------------------------------ --------
T_OBJECT_ID_INDEX VALID

SQL> alter table t move tablespace users;

Table altered.

SQL> select index_name,status from user_indexes where index_name='T_OBJECT_ID_INDEX';

INDEX_NAME STATUS
------------------------------ --------
T_OBJECT_ID_INDEX UNUSABLE

SQL> alter index T_OBJECT_ID_INDEX rebuild;

Index altered.

SQL> select index_name,status from user_indexes where index_name='T_OBJECT_ID_INDEX';

INDEX_NAME STATUS
------------------------------ --------
T_OBJECT_ID_INDEX VALID

如果某索引在unusable时，不能对索引所属的表进行DML操作.如下测试：

SQL> insert into t (select * from t);
insert into t (select * from t)
*
ERROR at line 1:
ORA-01502: index 'SYS.T_OBJECT_ID_INDEX' or partition of such index is in
unusable state

SQL> delete t where object_id=97;
delete t where object_id=97
*
ERROR at line 1:
ORA-01502: index 'SYS.T_OBJECT_ID_INDEX' or partition of such index is in
unusable state

SQL> update t set object_id=98 where object_id=97;
update t set object_id=98 where object_id=97
*
ERROR at line 1:
ORA-01502: index 'SYS.T_OBJECT_ID_INDEX' or partition of such index is in
unusable state

SQL> select index_name,status from user_indexes
2 where index_name='T_OBJECT_ID_INDEX';

INDEX_NAME STATUS
------------------------------ --------
T_OBJECT_ID_INDEX UNUSABLE

SQL> alter index T_OBJECT_ID_INDEX rebuild;

Index altered.

SQL> insert into t (select * from t);

4042 rows created.

2.move时对table的锁定

session 1

SQL> update t set object_id=6450 where object_id=6449;

0 rows updated.

session 2
SQL> alter table t move tablespace test_indx;
alter table t move tablespace test_indx
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

切换到session 1

SQL> commit;

Commit complete.

回到seesion 2

SQL> l
1* alter table t move tablespace test_indx
SQL> r
1* alter table t move tablespace test_indx

Table altered.

3.关于move时空间使用

SQL> select bytes/(1024*1024) from user_segments where segment_name='T';

BYTES/(1024*1024)
-----------------
7

SQL> create tablespace test01
2 datafile '/opt/ora9/oradata/catalog/test01.dbf' size 10m;

SQL> alter table t move tablespace test01;

Table altered.

SQL> alter table t move tablespace users;

Table altered.

SQL> alter database datafile '/opt/ora9/oradata/catalog/test01.dbf' resize 7m;

Database altered.

SQL> alter table t move tablespace test01;

Table altered.

SQL> alter table t move tablespace users;

Table altered.

SQL> alter database datafile '/opt/ora9/oradata/catalog/test01.dbf' resize 6m;

Database altered.

SQL> alter table t move tablespace test01;
alter table t move tablespace test01
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST01

ITPub博客

[原创] move table 的注意事项

最新文章