数据移植实验

参考视频:[bbk4992]袁宝华 第十二章 - 数据移植

实验步骤:

1、test user---create table  --- emp1

2、test user---create index --- emp1

3、create direcotry --- test_dir --- grant

4、show directory

5、expdp --- emp1

expdp test/test DUMPFILE=emp1.dmp DIRECTORY=test_dir tables=emp1 logfile=emp1.log
View Code

 

6、man-made destroy accidents

drop table emp1 purge;
View Code

 

7、impdp

impdp test/test DUMPFILE=emp1.dmp DIRECTORY=test_dir logfile=emp1.log
View Code

 

将test用户中的表emp，移植到用户user1中

C:\Users\MaryHu>expdp TEST/TEST dumpfile=emp.dmp directory=dmp_dir logfile=emp.log tables=emp

Export: Release 11.2.0.1.0 - Production on Tue Jul 9 15:06:34 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01":  TEST/******** dumpfile=emp.dmp directory=dmp_dir logfile=emp.log tables=emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."EMP"                                5.429 KB       1 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  D:\APP\MARYHU\ORADATA\BACKUP\EMP.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:06:40


C:\Users\MaryHu>
将表emp从用户TEST中导出

 

C:\Users\MaryHu>impdp user1/user1 dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1

Import: Release 11.2.0.1.0 - Production on Tue Jul 9 15:16:50 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER1"."SYS_IMPORT_FULL_01":  user1/******** dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER1"."EMP"                               5.429 KB       1 rows
Job "USER1"."SYS_IMPORT_FULL_01" successfully completed at 15:16:53


C:\Users\MaryHu>
将表emp导入到user1下

 

C:\Users\MaryHu>sqlplus user1/user1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 9 15:19:00 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
T                              TABLE
T2                             TABLE

SQL> select * from emp;

        ID NAME
---------- ----------
         0 arcerzhang

SQL> col segment_name for a30
SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            APP_DATA
T                              APP_TEST
T2                             APP_TEST

SQL>
验证导入情况，发现数据表虽然导入进来，但是所在的表空间没有相应导入过来；任然在原来表空间；
SQL> drop table emp purge;

Table dropped.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
T                              APP_TEST
T2                             APP_TEST
删除emp，重新做映射；
C:\Users\MaryHu>impdp user1/user1 dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1 remap_tablespace=app_data:app_test

Import: Release 11.2.0.1.0 - Production on Tue Jul 9 15:22:07 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER1"."SYS_IMPORT_FULL_01":  user1/******** dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1 remap_tablespace=app_data:app_test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER1"."EMP"                               5.429 KB       1 rows
Job "USER1"."SYS_IMPORT_FULL_01" successfully completed at 15:22:09


C:\Users\MaryHu>sqlplus user1/user1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 9 15:22:15 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
T                              TABLE
T2                             TABLE

SQL> select * from emp;

        ID NAME
---------- ----------
         0 arcerzhang

SQL> col segment_name for a40
SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                             TABLESPACE_NAME
---------------------------------------- ------------------------------
EMP                                      APP_TEST
T                                        APP_TEST
T2                                       APP_TEST

SQL>
重新导入，添加表空间映射；

 

 

posted @ 2013-07-09 16:24  ArcerZhang  阅读(203)  评论(0)  编辑  收藏  举报