oracle 表迁移方法 （二…

在oracle 表迁移方法 （一）中,只是move了一张普通的表,如果表的字段带有主键约束呢 ？

[oracle@db01 ~]$ sqlplus / as sysdba

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

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

Connected to:
Oracle Database 11g EnterpriseEdition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, DataMining and Real Application Testing options

SQL> select name fromv$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 yoondefault tablespace dahao;

User created.

授权
SQL> grant dba to yoon;

Grant succeeded.

当前用户
SQL> show user
USER is "YOON"

创建测试表yoon
SQL> create table yoon as select * fromscott.emp;

Table created.

查看当前表索引
SQL> select index_name fromuser_indexes;

no rows selected

创建empno主键约束
SQL> alter table yoon add constraint pk_empnoprimary key (empno);

Table altered.

SQL> conn / as sysdba
Connected.

SQL> select name fromv$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 onyoon(deptno);

Index created.

设置yoon表为只读
SQL> alter table yoon.yoon readonly;

Table altered.

迁移yoon表
SQL> alter table yoon.yoon move tablespaceyoon;

Table altered.

修改用户默认表空间
SQL> alter user yoon default tablespaceyoon;

User altered.

查看用户对应默认表空间
SQL> select username,default_tablespace fromdba_users;

USERNAMEDEFAULT_TABLESPACE
------------------------------------------------------------
SYS SYSTEM
SYSTEM SYSTEM
SCOTT USERS
GGS USERS
YOONYOON

查看索引状态
SQL> selectINDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes whereindex_name='PK_EMPNO' ;

INDEX_NAME TABLE_OWNER TABLE_NAMESTATUS
------------------------------------------------------------ --------------------------------------
PK_EMPNO YOON YOONUNUSABLE

SQL> selectINDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes whereindex_name='IDX_DEPTNO' ;

INDEX_NAME TABLE_OWNER TABLE_NAMESTATUS
------------------------------------------------------------ --------------------------------------
IDX_DEPTNO YOON YOONUNUSABLE

重建索引
SQL> alter index IDX_DEPTNO rebuild;

Index altered.

SQL> selectINDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes whereindex_name='PK_EMPNO' ;

INDEX_NAME TABLE_OWNER TABLE_NAMESTATUS
------------------------------------------------------------ --------------------------------------
PK_EMPNO YOON YOONUNUSABLE

SQL> selectINDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from user_indexes whereindex_name='IDX_DEPTNO' ;

INDEX_NAME TABLE_OWNER TABLE_NAMESTATUS
------------------------------------------------------------ --------------------------------------
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' orpartition 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迁移表至另一个表空间,索引失效,主键约束不失效.