ITPub博客

首页 > Linux操作系统 > Linux操作系统 > IMPDP导入远程数据库

IMPDP导入远程数据库

原创 Linux操作系统 作者:wailon 时间:2013-11-14 17:39:34 0 删除 编辑

这个功能在测试环境中较多使用。

-- 在目标库创建DBLINK后,通过IMPDP导入远程数据库到目标。
[oracle@dg dbbackup(01:13:06)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 01:15:17 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

01:15:37 SYS@wailon> create public database link db_wailon connect to scott identified by tiger using 'wailon';

Database link created.

01:16:39 SYS@wailon> col name for a20
01:16:47 SYS@wailon> col host for a30
01:16:54 SYS@wailon> select name,userid,host from link$;

NAME                 USERID                         HOST
-------------------- ------------------------------ ------------------------------
OGG                  SCOTT                          ogg
DB_WAILON            SCOTT                          wailon

01:16:57 SYS@wailon> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 使用IMPDP的NETWORK_LINK连接远程数据库,确保导入前存在相关的表空间,用户在导入时自动创建
-- NETWORK_LINK=远程数据库DBLINK_NAME,SCHEMAS=需要导入的用户,REMAP_SCHEMA=原SCHEMA:新SCHEMA
[oracle@dg dbbackup(01:18:22)]$ impdp scott/tiger network_link=db_wailon schemas=scott remap_schema=scott:wailon

Import: Release 11.2.0.3.0 - Production on Sun Sep 29 01:19:20 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/******** network_link=db_wailon schemas=scott remap_schema=scott:wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10.37 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WAILON"."GGS_DDL_HIST"                       2030 rows
. . imported "WAILON"."GGS_MARKER"                          829 rows
. . imported "WAILON"."CHECKPOINT"                            2 rows
. . imported "WAILON"."DEPT"                                  6 rows
. . imported "WAILON"."EMP"                                  18 rows
. . imported "WAILON"."GGS_DDL_HIST_ALT"                     71 rows
. . imported "WAILON"."GGS_SETUP"                             6 rows
. . imported "WAILON"."SALGRADE"                              5 rows
. . imported "WAILON"."BONUS"                                 0 rows
. . imported "WAILON"."CHECKPOINT_LOX"                        0 rows
. . imported "WAILON"."GGS_DDL_COLUMNS"                       0 rows
. . imported "WAILON"."GGS_DDL_LOG_GROUPS"                    0 rows
. . imported "WAILON"."GGS_DDL_OBJECTS"                       0 rows
. . imported "WAILON"."GGS_DDL_PARTITIONS"                    0 rows
. . imported "WAILON"."GGS_DDL_PRIMARY_KEYS"                  0 rows
. . imported "WAILON"."GGS_DDL_RULES"                         0 rows
. . imported "WAILON"."GGS_DDL_RULES_LOG"                     0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 01:21:02

-- 检查是否导入成功
[oracle@dg dbbackup(01:26:42)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 01:27:01 2013

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


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

01:26:50 WAILON@wailon> alter user wailon identified by wailon;

User altered.

01:27:02 WAILON@wailon> select table_name,tablespace_name from dba_tables where owner='WAILON';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GGS_SETUP                      USERS
GGS_DDL_HIST_ALT               USERS
GGS_MARKER                     USERS
GGS_DDL_RULES                  USERS
GGS_DDL_RULES_LOG              USERS
CHECKPOINT                     USERS
CHECKPOINT_LOX                 USERS
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
GGS_DDL_HIST                   USERS
GGS_DDL_COLUMNS                USERS
GGS_DDL_LOG_GROUPS             USERS
GGS_DDL_PARTITIONS             USERS
GGS_DDL_PRIMARY_KEYS           USERS
GGS_DDL_OBJECTS                USERS
GGS_TEMP_UK
GGS_STICK
GGS_TEMP_COLS

20 rows selected.

01:27:15 SYS@wailon> drop user wailon cascade;

User dropped.

01:29:53 SYS@wailon> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 使用IMPDP导入到不同的用户不同的表空间,确保导入前存在相关的表空间
-- REMAP_TABLESPACE=原TABLESPACE:新TABLESPACE
[oracle@dg dbbackup(01:42:10)]$ impdp scott/tiger network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon

Import: Release 11.2.0.3.0 - Production on Sun Sep 29 01:42:28 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/******** network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12.37 MB
Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'WAILON' does not exist
Failing sql is:
 CREATE USER "WAILON" IDENTIFIED BY VALUES 'S:757B41B311870958859653625C627A2D72CCED18785157E6D6AD16F95A9D;F894844C34402B67' DEFAULT TABLESPACE "WAILON" TEMPORARY TABLESPACE "TEMP01"
-- 表空间不存在时报错

-- 建立需要的表空间
[oracle@dg dbbackup(02:01:03)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 02:01:10 2013

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


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

02:06:39 SYS@wailon> create tablespace wailon datafile size 100m;

Tablespace created.

02:07:00 SYS@wailon> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 重新导入
[oracle@dg dbbackup(02:07:03)]$ impdp scott/tiger network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon

Import: Release 11.2.0.3.0 - Production on Sun Sep 29 02:07:05 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/******** network_link=db_wailon remap_tablespace=users:wailon remap_schema=scott:wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13.37 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WAILON"."GGS_DDL_HIST"                       2534 rows
. . imported "WAILON"."GGS_MARKER"                         1435 rows
. . imported "WAILON"."CHECKPOINT"                            2 rows
. . imported "WAILON"."DEPT"                                  6 rows
. . imported "WAILON"."EMP"                                  18 rows
. . imported "WAILON"."GGS_DDL_HIST_ALT"                    123 rows
. . imported "WAILON"."GGS_SETUP"                             6 rows
. . imported "WAILON"."SALGRADE"                              5 rows
. . imported "WAILON"."BONUS"                                 0 rows
. . imported "WAILON"."CHECKPOINT_LOX"                        0 rows
. . imported "WAILON"."GGS_DDL_COLUMNS"                       0 rows
. . imported "WAILON"."GGS_DDL_LOG_GROUPS"                    0 rows
. . imported "WAILON"."GGS_DDL_OBJECTS"                       0 rows
. . imported "WAILON"."GGS_DDL_PARTITIONS"                    0 rows
. . imported "WAILON"."GGS_DDL_PRIMARY_KEYS"                  0 rows
. . imported "WAILON"."GGS_DDL_RULES"                         0 rows
. . imported "WAILON"."GGS_DDL_RULES_LOG"                     0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 02:08:57

-- 检查是否导入成功
[oracle@dg dbbackup(02:09:03)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 02:09:26 2013

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


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

02:09:26 SYS@wailon> alter user wailon identified by wailon;

User altered.

02:09:43 SYS@wailon> conn wailon/wailon
Connected.
02:09:47 WAILON@wailon> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
CHECKPOINT                     TABLE
CHECKPOINT_LOX                 TABLE
DEPT                           TABLE
EMP                            TABLE
GGS_DDL_COLUMNS                TABLE
GGS_DDL_HIST                   TABLE
GGS_DDL_HIST_ALT               TABLE
GGS_DDL_LOG_GROUPS             TABLE
GGS_DDL_OBJECTS                TABLE
GGS_DDL_PARTITIONS             TABLE
GGS_DDL_PRIMARY_KEYS           TABLE
GGS_DDL_RULES                  TABLE
GGS_DDL_RULES_LOG              TABLE
GGS_MARKER                     TABLE
GGS_SETUP                      TABLE
GGS_STICK                      TABLE
GGS_TEMP_COLS                  TABLE
GGS_TEMP_UK                    TABLE
SALGRADE                       TABLE

20 rows selected.

02:09:51 WAILON@wailon> select tablespace_name,table_name from dba_tables where owner='WAILON';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
WAILON                         GGS_SETUP
WAILON                         GGS_DDL_HIST_ALT
WAILON                         GGS_MARKER
WAILON                         GGS_DDL_RULES
WAILON                         GGS_DDL_RULES_LOG
WAILON                         CHECKPOINT
WAILON                         CHECKPOINT_LOX
WAILON                         DEPT
WAILON                         EMP
WAILON                         BONUS
WAILON                         SALGRADE
WAILON                         GGS_DDL_HIST
WAILON                         GGS_DDL_COLUMNS
WAILON                         GGS_DDL_LOG_GROUPS
WAILON                         GGS_DDL_PARTITIONS
WAILON                         GGS_DDL_PRIMARY_KEYS
WAILON                         GGS_DDL_OBJECTS
                               GGS_TEMP_UK
                               GGS_STICK
                               GGS_TEMP_COLS

20 rows selected.

02:10:09 WAILON@wailon> set long 4000

02:10:50 WAILON@wailon> select dbms_metadata.get_ddl('TABLE','GGS_TEMP_UK') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','GGS_TEMP_UK')
--------------------------------------------------------------------------------

  CREATE GLOBAL TEMPORARY TABLE "WAILON"."GGS_TEMP_UK"
   (    "SEQNO" NUMBER NOT NULL ENABLE,
        "KEYNAME" VARCHAR2(100),
        "COLNAME" VARCHAR2(100),
        "NULLABLE" NUMBER,
        "VIRTUAL" NUMBER,
        "UDT" NUMBER,
        "ISSYS" NUMBER,
         PRIMARY KEY ("SEQNO", "KEYNAME", "COLNAME") ENABLE
   ) ON COMMIT DELETE ROWS

-- 临时表只是数据字典定义,只在使用时才会使用临时表空间

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

请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290554