数据移植实验

阅读量：5110 次

发布时间：2019-06-13

本文共 5714 字，大约阅读时间需要 19 分钟。

参考视频:[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=empExport: Release 11.2.0.1.0 - Production on Tue Jul 9 15:06:34 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "TEST"."SYS_EXPORT_TABLE_01":  TEST/******** dumpfile=emp.dmp directory=dmp_dir logfile=emp.log tables=empEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "TEST"."EMP"                                5.429 KB       1 rowsMaster 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.DMPJob "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:06:40C:\Users\MaryHu>

将表emp从用户TEST中导出

C:\Users\MaryHu>impdp user1/user1 dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1Import: Release 11.2.0.1.0 - Production on Tue Jul 9 15:16:50 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "USER1"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "USER1"."SYS_IMPORT_FULL_01":  user1/******** dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1Processing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "USER1"."EMP"                               5.429 KB       1 rowsJob "USER1"."SYS_IMPORT_FULL_01" successfully completed at 15:16:53C:\Users\MaryHu>

将表emp导入到user1下

C:\Users\MaryHu>sqlplus user1/user1SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 9 15:19:00 2013Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------EMP                            TABLET                              TABLET2                             TABLESQL> select * from emp;        ID NAME---------- ----------         0 arcerzhangSQL> col segment_name for a30SQL> select segment_name,tablespace_name from user_segments;SEGMENT_NAME                   TABLESPACE_NAME------------------------------ ------------------------------EMP                            APP_DATAT                              APP_TESTT2                             APP_TESTSQL>

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

SQL> drop table emp purge;Table dropped.SQL> select segment_name,tablespace_name from user_segments;SEGMENT_NAME                   TABLESPACE_NAME------------------------------ ------------------------------T                              APP_TESTT2                             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_testImport: Release 11.2.0.1.0 - Production on Tue Jul 9 15:22:07 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "USER1"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "USER1"."SYS_IMPORT_FULL_01":  user1/******** dumpfile=emp.dmp directory=dmp_dir logfile=emp.log remap_schema=TEST:user1 remap_tablespace=app_data:app_testProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "USER1"."EMP"                               5.429 KB       1 rowsJob "USER1"."SYS_IMPORT_FULL_01" successfully completed at 15:22:09C:\Users\MaryHu>sqlplus user1/user1SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 9 15:22:15 2013Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------EMP                            TABLET                              TABLET2                             TABLESQL> select * from emp;        ID NAME---------- ----------         0 arcerzhangSQL> col segment_name for a40SQL> select segment_name,tablespace_name from user_segments;SEGMENT_NAME                             TABLESPACE_NAME---------------------------------------- ------------------------------EMP                                      APP_TESTT                                        APP_TESTT2                                       APP_TESTSQL>

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

转载于:https://www.cnblogs.com/arcer/p/3180370.html

你可能感兴趣的文章

[HDU5727]Necklace（二分图最大匹配，枚举）