oracle 表迁移方法 （一）

在生产系统中,因业务需求,56张表中清空54张表数据,另外两张表数据保留,数据量大约10G左右：
1.大部分人想法就是expdp/impdp,的确是这样，哈哈

2.rman

3.以下方法,move

虚拟机单表模拟如下：
[oracle@db01 ~]$ sqlplus / assysdba

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

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

Tablespace created.

创建用户
SQL> create user dahao identified by dahao default tablespacedahao;

User created.

授权
SQL> grant dba to dahao;

Grant succeeded.

SQL> conn dahao/dahao
Connected.

SQL> show user
USER is "DAHAO"

创建测试表
SQL> create table dahao as select * from scott.emp;

Table created.

查看索引
SQL> select index_name from user_indexes;

no rows selected

创建索引
SQL> create index index_empno on dahao(empno) tablespaceusers;

Index created.

查看索引
SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
INDEX_EMPNO

创建表move的表空间
SQL> create tablespace yoon datafile'/u01/app/oracle/oradata/orcl/yoon01.dbf' size 100m;

Tablespace created.

将表设置只读模式
SQL> alter table dahao.dahao read only;

Table altered.

迁移表对应表空间
SQL> alter table dahao.dahao move tablespace yoon;

Table altered.

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

User altered.

查看表状态
SQL> select TABLE_NAME,TABLESPACE_NAME,READ_ONLY from dba_tableswhere owner='DAHAO' and table_name='DAHAO';

TABLE_NAME TABLESPACE_NAMEREA
------------------------------------------------------------ ---
DAHAO YOON YES

SQL> show user
USER is "DAHAO"

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
INDEX_EMPNO

查看索引状态,失效
SQL> select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS fromuser_indexes where index_name='INDEX_EMPNO';

INDEX_NAME TABLE_OWNER TABLE_NAMESTATUS
------------------------------------------------------------ --------------------------------------
INDEX_EMPNO DAHAO DAHAOUNUSABLE

重建索引
SQL> alter index index_empno rebuild tablespaceusers;

Index altered.

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

将表设置读写模式
SQL> alter table dahao read write;

Table altered.

查看表状态
SQL> select TABLE_NAME,TABLESPACE_NAME,READ_ONLY from dba_tableswhere owner='DAHAO' and table_name='DAHAO';

TABLE_NAME TABLESPACE_NAMEREA
------------------------------------------------------------ ---
DAHAO YOON NO