oracle 表遷移方法 （一）

標籤：

在生產系統中,因業務需求,56張表中清空54張表資料,另外兩張表資料保留,資料量大約10G左右：
1.大部分人想法就是expdp/impdp,的確是這樣，哈哈

2.rman

3.以下方法,move

虛擬機器單表類比如下：
[[email protected] ~]$ 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

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

Tablespace created.

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

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) tablespace users;

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 tablespace yoon;

User altered.

查看錶狀態
SQL> select TABLE_NAME,TABLESPACE_NAME,READ_ONLY from dba_tables where owner=‘DAHAO‘ and table_name=‘DAHAO‘;

TABLE_NAME                     TABLESPACE_NAME                REA
------------------------------ ------------------------------ ---
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 from user_indexes where index_name=‘INDEX_EMPNO‘;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
------------------------------ ------------------------------ ------------------------------ --------
INDEX_EMPNO                    DAHAO                          DAHAO                          UNUSABLE

重建索引
SQL> alter index index_empno rebuild tablespace users;

Index altered.

查看使用者預設資料表空間
SQL> select username,default_tablespace from dba_users;
DAHAO                          YOON

將表設定讀寫入模式
SQL> alter table dahao read write;

Table altered.

查看錶狀態
SQL> select TABLE_NAME,TABLESPACE_NAME,READ_ONLY from dba_tables where owner=‘DAHAO‘ and table_name=‘DAHAO‘;

TABLE_NAME                     TABLESPACE_NAME                REA
------------------------------ ------------------------------ ---
DAHAO                          YOON                           NO

oracle 表遷移方法 （一）