將表空間test1中的資料全部遷移到表空間test2中

czxin788發表於2015-05-13

需求：將表空間test1中的資料全部遷移到表空間test2中

我的實驗步驟，歡迎大家指正。

SQL> 1、建立兩個表空間
SQL> create tablespace test1 datafile '/rman_backup/test1.dbf' size 10m;

Tablespace created.

SQL> create tablespace test2 datafile '/rman_backup/test2.dbf' size 10m;

Tablespace created.

SQL> 2、建立兩個使用者
SQL> create user test1 identified by test1 default tablespace test1;

User created.

SQL> create user test2 identified by test2 default tablespace test2;

User created.

SQL> 3、給許可權
SQL> grant connect,resource to test1;

Grant succeeded.

SQL> grant connect,resource to test2;

Grant succeeded.

SQL> 4、在test1表空間裡建表
SQL> conn test1/test1
Connected.
SQL> create table t(i number) tablespace test1;

Table created.

SQL> begin
2 for i in 1..10000 loop
3 insert into t values (i);
4 end loop;
5 commit;
6 end ;
7 /

PL/SQL procedure successfully completed.

SQL> 5、檢視建立的表空間和表的情況

SQL> conn / as sysdba
Connected.
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST2 TEST2 TEMP
TEST1 TEST1 TEMP

SQL> col OBJECT_NAME for a10
SQL> select object_name,object_type,status from DBA_objects where owner in ('TEST1','TEST2');

OBJECT_NAM OBJECT_TYPE STATUS
---------- ------------------- -------
T TABLE VALID

SQL> select segment_name,segment_type,tablespace_name,owner from dba_extents where owner in ('TEST1','TEST2');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME OWNER
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
T TABLE TEST1 TEST1
T TABLE TEST1 TEST1
T TABLE TEST1 TEST1

SQL> conn test1/test1
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE

SQL> select count(*) from t;

COUNT(*)
----------
10000

SQL> conn test2/test2
Connected.
SQL> select * from tab;

no rows selected
SQL> 6、下面開始匯出表空間test1
SQL> conn / as sysdba
Connected.

SQL> !expdp system/oracle directory=my_dir dumpfile=test1.dmp tablespaces=test1
Export: Release 11.2.0.1.0 - Production on Wed May 13 13:39:18 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** directory=my_dir dumpfile=test1.dmp tablespaces=test1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST1"."T" 82.92 KB 10000 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/home/oracle/expdp_bak/test1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 13:39:26

7、下面再驗證一下表空間和表的情況
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST2 TEST2 TEMP
TEST1 TEST1 TEMP

SQL> select object_name,object_type,status from DBA_objects where owner in ('TEST1','TEST2');

OBJECT_NAM OBJECT_TYPE STATUS
---------- ------------------- -------
T TABLE VALID

SQL> select segment_name,segment_type,tablespace_name,owner from dba_extents where owner in ('TEST1','TEST2');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME OWNER
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
T TABLE TEST1 TEST1
T TABLE TEST1 TEST1
T TABLE TEST1 TEST1

8、下面我開始把表空間test1的東西匯入到表空間test2中

SQL> !impdp system/oracle directory=my_dir dumpfile=test1.dmp remap_tablespace=test1:test2
Import: Release 11.2.0.1.0 - Production on Wed May 13 13:41:29 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=my_dir dumpfile=test1.dmp remap_tablespace=test1:test2
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TEST1"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:41:32

看到匯入報錯了，說是表test1.t存在。
那我們看看在上述報錯的情況下表空間test1的資料是否匯入到了表空間test2中呢？發現沒有:

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST2 TEST2 TEMP
TEST1 TEST1 TEMP

SQL> select object_name,object_type,status,owner from DBA_objects where owner in ('TEST1','TEST2');

OBJECT_NAM OBJECT_TYPE STATUS OWNER
---------- ------------------- ------- ------------------------------
T TABLE VALID TEST1

SQL> select segment_name,segment_type,tablespace_name,owner from dba_extents where owner in ('TEST1','TEST2');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME OWNER
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
T TABLE TEST1 TEST1
T TABLE TEST1 TEST1
T TABLE TEST1 TEST1

下面我們在匯入的時候加個 table_exists_action=replace引數

SQL> !impdp system/oracle directory=my_dir dumpfile=test1.dmp remap_tablespace=test1:test2 table_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Wed May 13 13:43:39 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=my_dir dumpfile=test1.dmp remap_tablespace=test1:test2 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST1"."T" 82.92 KB 10000 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 13:43:41

哈哈，看到這回匯入成功了。

############################
小知識：
table_exists_action引數說明
使用imp進行資料匯入時，若表已經存在，要先drop掉表，再進行匯入。

而使用impdp完成資料庫匯入時，若表已經存在，有四種的處理方式：

1) skip：預設操作

2) replace：先drop表，然後建立表，最後插入資料

3) append：在原來資料的基礎上增加資料

4) truncate：先truncate，然後再插入資料

#############################

10、接下來，我們就驗證一下，發現表空間test1的資料確實匯入到了表空間test2裡面

SQL> select segment_name,segment_type,tablespace_name,owner from dba_extents where owner in ('TEST1','TEST2');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME OWNER
--------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------
T TABLE TEST2 TEST1
T TABLE TEST2 TEST1
T TABLE TEST2 TEST1

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST2 TEST2 TEMP
TEST1 TEST1 TEMP

SQL> select object_name,object_type,status,owner from DBA_objects where owner in ('TEST1','TEST2');

OBJECT_NAM OBJECT_TYPE STATUS OWNER
---------- ------------------- ------- ------------------------------
T TABLE VALID TEST1

SQL> conn test1/test1
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE

SQL> select count(*) from t;

COUNT(*)
----------
10000

SQL> conn test2/test2
Connected.
SQL> select * from tab;

no rows selected

SQL> spool off

完

參考文件：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28916011/viewspace-1652312/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

將表從一個表空間遷移到另外一個表空間
2014-11-04
把表遷移到其他的表空間
2008-07-31
將表遷移至其他表空間
2013-07-23
遷移SYSTEM表空間為本地管理表空間
2011-08-09
表空間遷移
2017-07-15
遷移表空間
2013-05-08
資料庫物件遷移表空間
2010-07-27
資料庫物件
基於可傳輸表空間的表空間遷移
2012-08-07
跨平臺表空間遷移(傳輸表空間)
2009-05-15
【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移
2010-07-23
【遷移】表空間transport
2011-05-24
RMAN遷移表空間
2009-04-27
線上遷移表空間資料檔案
2015-05-28
Oracle 表空間資料檔案遷移
2012-12-04
Oracle
遷移表到新的表空間
2015-03-12
ORACLE表批量遷移表空間
2014-12-04
Oracle
MySQL 中的共享表空間與獨立表空間如何選擇
2021-07-25
MySql
【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移[轉]
2012-07-30
oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組
2014-05-28
OracleASM
PostgreSQL:表空間-->資料庫-->表
2018-09-24
SQL資料庫
【資料遷移】使用傳輸表空間遷移資料
2010-08-21
將字典管理表空間轉換為本地管理表空間
2017-08-25
InnoDB資料表空間檔案平滑遷移
2010-04-16
MySQL innodb共享表空間新增表空間資料檔案方法
2016-08-25
MySql
IMPDP 多個表空間物件匯入到一個表空間中
2011-03-01
物件
expdp/impdp 遷移表空間
2012-06-05
[zt] 如何將資料字典管理表空間(DMT)轉化為本地管理表空間(LMT)
2011-03-12
表空間中有資料也可以壓縮表空間（資料檔案）大小
2014-06-16
oracle建立臨時表空間和資料表空間以及刪除
2018-10-19
Oracle
表空間的資料字典管理
2011-03-22
表在表空間中的儲存情況
2006-12-19
Oracle 不同平臺間表空間遷移
2011-11-16
Oracle
水煮orale22——查詢表空間使用資訊以及表空間中的每個資料檔案資訊
2013-04-07
管理表空間（表空間的屬性）轉貼
2009-03-11
(個人)Oracle 表空間資料檔案遷移（轉）
2014-11-20
Oracle
海量資料遷移之傳輸表空間(一)
2015-06-17
分析表空間空閒率並收縮表空間
2015-04-22
openGauss中如何管理表空間
2024-03-28