将表空间test1中的数据全部迁移到表空间test2中

最新推荐文章于 2020-01-03 10:01:14 发布

cjfcxf010101

最新推荐文章于 2020-01-03 10:01:14 发布

阅读量169

点赞数

需求：将表空间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

完

参考文档：
http://www.2cto.com/database/201212/177162.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28916011/viewspace-1652312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28916011/viewspace-1652312/

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
将表空间test1中的数据全部迁移到表空间test2中

需求：将表空间test1中的数据全部迁移到表空间test2中我的实验步骤，欢迎大家指正。SQL> 1、创建两个表空间SQL> create tablespace test1 datafile ...
复制链接

扫一扫

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。