oracle 表遷移方法 （二） 約束不失效

DB：11.2.0.3.0

在中,只是move了一張普通的表,如果表的欄位帶有主鍵約束呢 ？

[oracle@db01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 3 18:40:16 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

建立dahao表空間
SQL> create tablespace dahao datafile '/u01/app/oracle/oradata/orcl/dahao01.dbf' size 100m;

Tablespace created.

建立yoon使用者
SQL> create user yoon identified by yoon default tablespace dahao;

User created.

授權
SQL> grant dba to yoon;

Grant succeeded.

當前使用者
SQL> show user
USER is "YOON"

建立測試表yoon
SQL> create table yoon as select * from scott.emp;

Table created.

檢視當前表索引
SQL> select index_name from user_indexes;

no rows selected

建立empno主鍵約束
SQL> alter table yoon add constraint pk_empno primary key (empno);

Table altered.

SQL> conn / as sysdba
Connected.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/dahao01.dbf

建立yoon表空間
SQL> create tablespace yoon datafile '/u01/app/oracle/oradata/orcl/yoon01.dbf' size 100m;

Tablespace created.

SQL> conn yoon/yoon;
Connected.

建立索引
SQL> create index idx_deptno on yoon(deptno); 

Index created.

設定yoon表為只讀
SQL> alter table yoon.yoon read only;

Table altered.

遷移yoon表
SQL> alter table yoon.yoon move tablespace yoon;

Table altered.

修改使用者預設表空間
SQL> alter user yoon default tablespace yoon;

User altered.

檢視使用者對應預設表空間
SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
SYSTEM                         SYSTEM
SCOTT                          USERS
GGS                            USERS
YOON                           YOON

檢視索引狀態
SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes where index_name='PK_EMPNO' ;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_EMPNO                       YOON                           YOON                           UNUSABLE

SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes where index_name='IDX_DEPTNO' ;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
IDX_DEPTNO                     YOON                           YOON                           UNUSABLE

重建索引
SQL> alter index IDX_DEPTNO rebuild ;

Index altered.

SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes where index_name='PK_EMPNO' ;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_EMPNO                       YOON                           YOON                           UNUSABLE

SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes where index_name='IDX_DEPTNO' ;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
IDX_DEPTNO                     YOON                           YOON                           VALID

設定yoon表為讀寫
SQL> alter table yoon read write;

Table altered.

插入資料
SQL> insert into yoon (empno) values (7934);
insert into yoon (empno) values (7934)
*
ERROR at line 1:
ORA-01502: index 'YOON.PK_EMPNO' or partition of such index is in unusable state

重建索引
SQL> alter index PK_EMPNO rebuild;

Index altered.

SQL> insert into yoon (empno) values (7934);
insert into yoon (empno) values (7934)
*
ERROR at line 1:
ORA-00001: unique constraint (YOON.PK_EMPNO) violated

經上述測試發現,透過move遷移表至另一個表空間,索引失效,主鍵約束不失效.