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

czxin788發表於2015-05-13

原文網址 : http://blog.itpub.net/28916011/viewspace-1652312/

需求：將表空間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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

16、表空間建立表空間
2020-12-03
MySQL 中的共享表空間與獨立表空間如何選擇
2021-07-25
MySql
PostgreSQL:表空間-->資料庫-->表
2018-09-24
SQL資料庫
openGauss中如何管理表空間
2024-03-28
oracle建立臨時表空間和資料表空間以及刪除
2018-10-19
Oracle
MySQL 遷移表空間，備份單表
2021-05-31
MySql
Oracle中表空間、表、索引的遷移
2020-02-13
Oracle索引
table/index/LOBINDEX遷移表空間
2021-05-08
Index
表空間利用率及表空間的補充
2021-01-14
Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間方法！
2018-03-22
Oracle
用傳輸表空間跨平臺遷移資料
2019-04-21
當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
2019-06-03
KingbaseES的表空間
2022-03-30
Oracle表空間
2019-06-11
Oracle
oracle 表空間
2024-09-27
Oracle
PostgreSQL 表空間
2024-07-20
SQL
PostgreSQL：表空間
2020-12-14
SQL
表空間和資料檔案的管理
2018-10-03
UNDO表空間空間回收及切換
2018-03-10
Oracle 12cbigfile表空間物件遷移
2021-08-27
Oracle物件
oracle表空間的整理
2020-03-31
Oracle
達夢（DM）資料庫的表空間建立和遷移維護
2020-07-10
資料庫
undo表空間容量
2019-01-15
增加oracle表空間
2018-10-10
Oracle
Configure innodb 表空間
2018-08-03
表空間限額
2019-10-24
3.2. 表空間
2020-02-10
只讀表空間
2019-06-15
oracle temp 表空間
2019-05-21
Oracle
oracle 表移動表空間
2018-05-23
Oracle
Oracle表移動表空間
2018-03-14
Oracle
達夢資料庫表空間等空間大小查詢方法總結
2021-02-25
資料庫
Ora-01536：超出了表空間users的空間限量
2018-12-30
mysql共享表空間擴容,收縮,遷移
2018-03-30
MySql
臨時表空間和回滾表空間使用率查詢
2019-07-01
達夢資料庫系統表空間資料檔案遷移過程
2022-01-06
資料庫
【資料遷移】XTTS跨平臺傳輸表空間v4
2020-07-29
TTS
聊聊Oracle表空間Offline的三種引數（中）
2018-03-12
Oracle
淺談mysql中各種表空間（tablespaces）的概念
2020-01-03
MySql