oracle impdp transport_data,【Oracle Database】 数据迁移（expdp/impdp）

最新推荐文章于 2023-08-25 19:01:37 发布
最新推荐文章于 2023-08-25 19:01:37 发布
阅读量234 收藏
点赞数
文章标签： oracle impdp transport_data

导出SCHEMA

[oracle@king01 ~]$ sqlplus / as sysdba

SQL> col owner format a10

SQL> col object_name format a45

SQL> col object_type format a20

SQL> col status format a20

SQL> select owner, object_name, object_type, status from dba_objects where owner='SOE';

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

SOE        CUSTOMERS                                     TABLE                VALID

SOE        ADDRESSES                                     TABLE                VALID

SOE        CARD_DETAILS                                  TABLE                VALID

SOE        WAREHOUSES                                    TABLE                VALID

SOE        ORDER_ITEMS                                   TABLE                VALID

SOE        ORDERS                                        TABLE                VALID

SOE        INVENTORIES                                   TABLE                VALID

SOE        PRODUCT_INFORMATION                           TABLE                VALID

SOE        LOGON                                         TABLE                VALID

SOE        PRODUCT_DESCRIPTIONS                          TABLE                VALID

SOE        ORDERENTRY_METADATA                           TABLE                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

SOE        PRODUCTS                                      VIEW                 VALID

SOE        PRODUCT_PRICES                                VIEW                 VALID

SOE        ADDRESS_PK                                    INDEX                VALID

SOE        CUSTOMERS_PK                                  INDEX                VALID

SOE        CARD_DETAILS_PK                               INDEX                VALID

SOE        WAREHOUSES_PK                                 INDEX                VALID

SOE        ORDER_ITEMS_PK                                INDEX                VALID

SOE        ORDER_PK                                      INDEX                VALID

SOE        PRODUCT_INFORMATION_PK                        INDEX                VALID

SOE        PRD_DESC_PK                                   INDEX                VALID

SOE        INVENTORY_PK                                  INDEX                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

SOE        WHS_LOCATION_IX                               INDEX                VALID

SOE        INV_PRODUCT_IX                                INDEX                VALID

SOE        INV_WAREHOUSE_IX                              INDEX                VALID

SOE        ADDRESS_CUST_IX                               INDEX                VALID

SOE        ITEM_ORDER_IX                                 INDEX                VALID

SOE        ITEM_PRODUCT_IX                               INDEX                VALID

SOE        ORD_SALES_REP_IX                              INDEX                VALID

SOE        ORD_CUSTOMER_IX                               INDEX                VALID

SOE        ORD_ORDER_DATE_IX                             INDEX                VALID

SOE        ORD_WAREHOUSE_IX                              INDEX                VALID

SOE        CUST_ACCOUNT_MANAGER_IX                       INDEX                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

SOE        CUST_DOB_IX                                   INDEX                VALID

SOE        CUST_EMAIL_IX                                 INDEX                VALID

SOE        PROD_NAME_IX                                  INDEX                VALID

SOE        PROD_SUPPLIER_IX                              INDEX                VALID

SOE        PROD_CATEGORY_IX                              INDEX                VALID

SOE        CUST_FUNC_LOWER_NAME_IX                       INDEX                VALID

SOE        CARDDETAILS_CUST_IX                           INDEX                VALID

SOE        CUSTOMER_SEQ                                  SEQUENCE             VALID

SOE        ORDERS_SEQ                                    SEQUENCE             VALID

SOE        ADDRESS_SEQ                                   SEQUENCE             VALID

SOE        LOGON_SEQ                                     SEQUENCE             VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

SOE        CARD_DETAILS_SEQ                              SEQUENCE             VALID

SOE        ORDERENTRY                                    PACKAGE              VALID

SOE        ORDERENTRY                                    PACKAGE BODY         VALID

47 rows selected.

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

------------------------------ ------------------------------ --------------------------------------------------------------------------------

SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/product/11.2.0/db_1/ccr/state

SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/king/dpdump/

SYS                            XMLDIR                         /ade/b/2125410156/oracle/rdbms/xml

[oracle@king01 ~]$ expdp system/oracle schemas=soe directory=data_pump_dir dumpfile=soe_s.dmp logfile=soe.log

Export: Release 11.2.0.4.0 - Production on Tue Jul 31 13:37:42 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=soe directory=data_pump_dir dumpfile=soe_s.dmp logfile=soe.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.008 GB

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/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

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

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS

. . exported "SOE"."ORDER_ITEMS"                         228.4 MB 4289500 rows

. . exported "SOE"."INVENTORIES"                         15.15 MB  895041 rows

. . exported "SOE"."ORDERS"                              129.1 MB 1429790 rows

. . exported "SOE"."ADDRESSES"                           110.4 MB 1500000 rows

. . exported "SOE"."CUSTOMERS"                           108.0 MB 1000000 rows

. . exported "SOE"."CARD_DETAILS"                        63.88 MB 1500000 rows

. . exported "SOE"."LOGON"                               51.24 MB 2382984 rows

. . exported "SOE"."PRODUCT_DESCRIPTIONS"                224.5 KB    1000 rows

. . exported "SOE"."PRODUCT_INFORMATION"                 187.9 KB    1000 rows

. . exported "SOE"."ORDERENTRY_METADATA"                 5.539 KB       4 rows

. . exported "SOE"."WAREHOUSES"                          35.07 KB    1000 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/u01/app/oracle/admin/king/dpdump/soe_s.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 31 13:38:26 2018 elapsed 0 00:00:40

[oracle@king01 ~]$ scp /u01/app/oracle/admin/king/dpdump/soe_s.dmp 192.168.1.202:/u01/app/oracle/admin/king/dpdump

导入SCHEMA

[oracle@king02 ~]$ impdp system/oracle directory=data_pump_dir dumpfile=soe_s.dmp remap_schema=soe:tpcc remap_tablespace=soe:tpcc transform=storage:n,segment_attributes:n

Import: Release 11.2.0.4.0 - Production on Tue Jul 31 13:47:27 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, 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=data_pump_dir dumpfile=soe_s.dmp remap_schema=soe:tpcc remap_tablespace=soe:tpcc transform=storage:n,segment_attributes:n

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"TPCC" already exists

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/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TPCC"."ORDER_ITEMS"                        228.4 MB 4289500 rows

. . imported "TPCC"."INVENTORIES"                        15.15 MB  895041 rows

. . imported "TPCC"."ORDERS"                             129.1 MB 1429790 rows

. . imported "TPCC"."ADDRESSES"                          110.4 MB 1500000 rows

. . imported "TPCC"."CUSTOMERS"                          108.0 MB 1000000 rows

. . imported "TPCC"."CARD_DETAILS"                       63.88 MB 1500000 rows

. . imported "TPCC"."LOGON"                              51.24 MB 2382984 rows

. . imported "TPCC"."PRODUCT_DESCRIPTIONS"               224.5 KB    1000 rows

. . imported "TPCC"."PRODUCT_INFORMATION"                187.9 KB    1000 rows

. . imported "TPCC"."ORDERENTRY_METADATA"                5.539 KB       4 rows

. . imported "TPCC"."WAREHOUSES"                         35.07 KB    1000 rows

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

ORA-39082: Object type ALTER_PACKAGE_SPEC:"TPCC"."ORDERENTRY" created with compilation warnings

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

ORA-39082: Object type PACKAGE_BODY:"TPCC"."ORDERENTRY" created with compilation warnings

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Tue Jul 31 13:53:43 2018 elapsed 0 00:06:15

编译无效对象

[oracle@king02 ~]$ sqlplus / as sysdba

SQL> col owner format a10

SQL> col object_name format a45

SQL> col object_type format a20

SQL> col status format a20

SQL> select owner, object_name, object_type, status from dba_objects where status = 'INVALID';

OWNER      OBJECT_NAME                                   OBJECT_TYPE          Status

---------- --------------------------------------------- -------------------- --------------------

TPCC        ORDERENTRY                                    PACKAGE BODY         INVALID

SQL> alter package tpcc.orderentry compile package;

Warning: Package altered with compilation errors.

SQL> col text for a50

SQL> select owner,name,text from dba_errors where owner='TPCC' and name='ORDERENTRY';

OWNER      NAME                                               TEXT

---------- -------------------------------------------------- --------------------------------------------------

TPCC       ORDERENTRY                                         PLS-00201: identifier 'DBMS_LOCK' must be declared

TPCC       ORDERENTRY                                         PL/SQL: Statement ignored

TPCC       ORDERENTRY                                         PLS-00201: identifier 'DBMS_LOCK' must be declared

TPCC       ORDERENTRY                                         PL/SQL: Statement ignored

SQL> grant execute on sys.dbms_lock to tpcc;

SQL> alter package tpcc.orderentry compile package;

Package altered.

SQL> select owner, object_name, object_type, status from dba_objects where owner='TPCC';

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

TPCC       CUSTOMER_SEQ                                  SEQUENCE             VALID

TPCC       ORDERS_SEQ                                    SEQUENCE             VALID

TPCC       ADDRESS_SEQ                                   SEQUENCE             VALID

TPCC       LOGON_SEQ                                     SEQUENCE             VALID

TPCC       CARD_DETAILS_SEQ                              SEQUENCE             VALID

TPCC       CUSTOMERS                                     TABLE                VALID

TPCC       ADDRESSES                                     TABLE                VALID

TPCC       CARD_DETAILS                                  TABLE                VALID

TPCC       WAREHOUSES                                    TABLE                VALID

TPCC       ORDER_ITEMS                                   TABLE                VALID

TPCC       ORDERS                                        TABLE                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

TPCC       INVENTORIES                                   TABLE                VALID

TPCC       PRODUCT_INFORMATION                           TABLE                VALID

TPCC       LOGON                                         TABLE                VALID

TPCC       PRODUCT_DESCRIPTIONS                          TABLE                VALID

TPCC       ORDERENTRY_METADATA                           TABLE                VALID

TPCC       ORDERENTRY                                    PACKAGE              VALID

TPCC       CUSTOMERS_PK                                  INDEX                VALID

TPCC       ADDRESS_PK                                    INDEX                VALID

TPCC       CARD_DETAILS_PK                               INDEX                VALID

TPCC       WAREHOUSES_PK                                 INDEX                VALID

TPCC       ORDER_ITEMS_PK                                INDEX                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

TPCC       ORDER_PK                                      INDEX                VALID

TPCC       PRODUCT_INFORMATION_PK                        INDEX                VALID

TPCC       PRD_DESC_PK                                   INDEX                VALID

TPCC       INVENTORY_PK                                  INDEX                VALID

TPCC       WHS_LOCATION_IX                               INDEX                VALID

TPCC       INV_PRODUCT_IX                                INDEX                VALID

TPCC       INV_WAREHOUSE_IX                              INDEX                VALID

TPCC       ADDRESS_CUST_IX                               INDEX                VALID

TPCC       ITEM_ORDER_IX                                 INDEX                VALID

TPCC       ITEM_PRODUCT_IX                               INDEX                VALID

TPCC       ORD_SALES_REP_IX                              INDEX                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

TPCC       ORD_CUSTOMER_IX                               INDEX                VALID

TPCC       ORD_ORDER_DATE_IX                             INDEX                VALID

TPCC       ORD_WAREHOUSE_IX                              INDEX                VALID

TPCC       CUST_ACCOUNT_MANAGER_IX                       INDEX                VALID

TPCC       CUST_DOB_IX                                   INDEX                VALID

TPCC       CUST_EMAIL_IX                                 INDEX                VALID

TPCC       PROD_NAME_IX                                  INDEX                VALID

TPCC       PROD_SUPPLIER_IX                              INDEX                VALID

TPCC       PROD_CATEGORY_IX                              INDEX                VALID

TPCC       CARDDETAILS_CUST_IX                           INDEX                VALID

TPCC       CUST_FUNC_LOWER_NAME_IX                       INDEX                VALID

OWNER      OBJECT_NAME                                   OBJECT_TYPE          STATUS

---------- --------------------------------------------- -------------------- --------------------

TPCC       PRODUCTS                                      VIEW                 VALID

TPCC       PRODUCT_PRICES                                VIEW                 VALID

TPCC       ORDERENTRY                                    PACKAGE BODY         VALID

47 rows selected.

导出表空间

[oracle@king01 ~]$ sqlplus / as sysdba

SQL> execute dbms_tts.transport_set_check('soe',incl_constraints=> true,full_check=> true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> alter tablespace soe read only;

Tablespace altered.

[oracle@king01 ~]$ expdp system/oracle dumpfile=soe.dmp directory=data_pump_dir transport_tablespaces=soe logfile=soe.log

Export: Release 11.2.0.4.0 - Production on Tue Jul 31 10:28:48 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=soe.dmp directory=data_pump_dir transport_tablespaces=soe logfile=soe.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/USER_PREF_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/u01/app/oracle/admin/king/dpdump/soe.dmp

******************************************************************************

Datafiles required for transportable tablespace TPCC:

/u01/app/oracle/oradata/king/soe01.dbf

/u01/app/oracle/oradata/king/soe02.dbf

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Jul 31 10:29:09 2018 elapsed 0 00:00:20

[oracle@king01 ~]$ scp /u01/app/oracle/admin/king/dpdump/soe.dmp 192.168.1.202:u01/app/oracle/admin/king/dpdump

[oracle@king01 ~]$ scp /u01/app/oracle/oradata/king/soe01.dbf 192.168.1.202:/u01/app/oracle/oradata/king

[oracle@king01 ~]$ scp /u01/app/oracle/oradata/king/soe02.dbf 192.168.1.202:/u01/app/oracle/oradata/king

导入表空间

[oracle@king02 ~]$ sqlplus / as sysdba

SQL> create user soe identified by soe;

User created.

SQL> grant connect,resource to soe;

Grant succeeded.

[oracle@king02 ~]$ impdp system/oracle directory=data_pump_dir dumpfile=soe.dmp \

transport_datafiles='/u01/app/oracle/oradata/king/soe01.dbf, /u01/app/oracle/oradata/king/soe02.dbf' \

logfile=soe.log

Import: Release 11.2.0.4.0 - Production on Tue Jul 31 10:06:35 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=data_pump_dir dumpfile=soe.dmp transport_datafiles=/u01/app/oracle/oradata/king/soe01.dbf,

/u01/app/oracle/oradata/king/seo02.dbf logfile=soe.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/USER_PREF_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Jul 31 10:06:39 2018 elapsed 0 00:00:04

SQL> alter user soe default tablespace soe;

User altered.

SQL> alter tablespace soe read write;

Tablespace altered.

[oracle@king01 ~]$ sqlplus soe/soe

SQL> set line 200

SQL> col object_name for a30

SQL> select object_name,object_type,status from user_objects where object_type='TABLE';

OBJECT_NAME                    OBJECT_TYPE                                               STATUS

------------------------------ --------------------------------------------------------- ---------------------

CUSTOMERS                      TABLE                                                     VALID

ADDRESSES                      TABLE                                                     VALID

CARD_DETAILS                   TABLE                                                     VALID

WAREHOUSES                     TABLE                                                     VALID

ORDER_ITEMS                    TABLE                                                     VALID

ORDERS                         TABLE                                                     VALID

INVENTORIES                    TABLE                                                     VALID

PRODUCT_INFORMATION            TABLE                                                     VALID

LOGON                          TABLE                                                     VALID

PRODUCT_DESCRIPTIONS           TABLE                                                     VALID

ORDERENTRY_METADATA            TABLE                                                     VALID

11 rows selected.

[oracle@king02 ~]$ sqlplus soe/soe

SQL> set line 200

SQL> col object_name for a30

SQL> select object_name,object_type,status from user_objects where object_type='TABLE';

OBJECT_NAME                    OBJECT_TYPE                                               STATUS

------------------------------ --------------------------------------------------------- ---------------------

ORDERENTRY_METADATA            TABLE                                                     VALID

PRODUCT_DESCRIPTIONS           TABLE                                                     VALID

LOGON                          TABLE                                                     VALID

PRODUCT_INFORMATION            TABLE                                                     VALID

INVENTORIES                    TABLE                                                     VALID

ORDERS                         TABLE                                                     VALID

ORDER_ITEMS                    TABLE                                                     VALID

WAREHOUSES                     TABLE                                                     VALID

CARD_DETAILS                   TABLE                                                     VALID

ADDRESSES                      TABLE                                                     VALID

CUSTOMERS                      TABLE                                                     VALID

11 rows selected.

expdp/impdp 数据泵parallel 参数优化测试
shilukun的博客
09-28 5578
  expdp/impdp在进行数据迁移时速度极快,通过一定的优化方法，我们让expdp和impdp跑得更加快 1,parallel,在很多oracle的程序中都离不开并行操作，通过利用多core cpu的处理能力（根据自己机器实际情况优化一般不要超过core的2倍），速度增加相当明显 例:frank用户主要有表T1,T2,T3,每个表 中数据有500万行 [oracle@cscscslocal...
PLSQL_数据泵Datapump导入导出数据IMPDP / EXPDP（概念）（Oracle数据导入导出工具）（转）...
weixin_34405557的博客
09-12 1035
一、摘要 在平常备库和数据库迁移的时候，当遇到大的数据库的时候在用exp的时候往往是需要好几个小时，耗费大量时间。oracle10g以后可以用expdp来导出数据库花费的时间要远小于exp花费的时间，而且文件也要小很多。 二、exp/imp与expdp/impdp区别 (1) 把用户usera的对象导到用户userb,用法区别在于fromuser=usera touser=userb ,r...
oracle impdp transport_data,EXPDP/IMPDP 数据泵表空间传输
weixin_42129005的博客
04-08 413
数据泵表空间传输检测表空间支持的操作系统以及字节顺序如果是夸平台的进行表空间传输，需要检查2个平台的是否在oracle的支持列表中，而且他们的字节顺序(ENDIAN_FORMAT)要一致，不然要进行转换(oracle 10g以上才支持)SQL> SELECT * FROMV$TRANSPORTABLE_PLATFORM. ORDER BY PLATFORM_ID;PLATFORM_ID PL...
Oracle OCP 1Z0 053 Q428(transport database)
congsong2560的博客
04-04 88
428.Which of the following are prerequisite steps to transport a database? (Choose all that apply.) A. Query the ...
oracle transport tablespace
weixin_34390105的博客
05-27 119
源win_x64 oracle10.2.0.4平台,目标端linux_X64 11.2.0.4平台需要注意的理,两端的字符集务必一致检查方法:select * from nls_database_parameters;否则会报: IMP-00017: following statement failed with ORACLE error 29345:"BEGIN sys.dbms_plug...
传输数据（Transporting Data）_使用可传输特性（只导出元数据和直接复制数据文件）传输数据库，表空间，表或子分区
jetliu05的博客
01-13 704
使用可传输特性（只导出元数据和直接复制数据文件）传输数据库，表空间，表或子分区
expdp/impdp transport_tablespace
cizhiyou2391的博客
11-18 417
expdp/impdp transport_tablespace[@more@][oracle@primary ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Produc...
oracle 数据迁移 impdp expdp
qq_30905661的博客
04-26 338
    之前做数据备份的时候用的都是exp 和 imp，但最近exp导了一个几十万数据的表就花了将近10分钟时间，简直不能忍。一直听说expdp的效率高，今天就来试一试。    按照惯例，先贴几篇有用的文章，使用expdp导出数据，dblink的操作。    最先需要明确场景，本地库A和远程数据库B，现在想要把B的数据迁移到A里面来。（如果是本地库中不同用户C和D，那么就无需dblink）。需要实...
oracle19c数据泵传输数据
最新发布
m0_70247753的博客
08-25 440
从非 cdb 导出数据库-----查詢非 cdb 數據庫的字符集和語言------代表語言_地區.字符集，這樣在導出的時候就不會產生字符集的轉換---------執行命令導出整個數據庫注意：1.共享池太小会导致导入失败，可以通过告警日志获得信息。2、 如果在 profile 中设置了资源限制，也会导致导入失败，比如 SESSIONS_PER_USER 设置的太小。3、 如果源数据库初始时用的是 OMF 管理，那么目标数据库也应该是 OMF 管理， 如果不是，则要创建和源表空间路径一样的路径。
oracle数据迁移expdp,impdp
07-04
Oracle数据迁移expdp,impdp常用的一些语句，免费，资源分数是系统必填项没有办法。
传输表空间迁移数据：expdp&impdp
congchijian3142的博客
04-02 162
数据库版本信息： 1.创建目录 源数据库、目标数据库创建目录 create directory dir_datapump as '/home/oracle'; 2.源数据...
oracle数据棒导入命令,Oracle10g 数据泵导入命令 impdp 使用总结
weixin_33648177的博客
04-04 331
1.1.1impdp使用1)导入表impdpscott/tigerDIRECTORY=dump_dirDUMPFILE=tab.dmpTABLES=dept,empimpdpsystem/managerDIRECTORY=dump_dirDUMPFILE=tab.dmpTABLES=scott.dept,scott.empREMAP_SCHEMA=SCOTT:SYSTEM第一种方...
拷贝oracle表空间,oracle的表空间传输transport_tablespace
weixin_36254564的博客
04-03 427
两个数据库之间进行传输表空间的过程1、如果是跨平台的表空间传输，需要检查两个平台支持的字节存储顺序，检查字符集、时区SQL>select * fromv$transportable_platform order by platform_name;SQL>SELECTd.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORMtp...
oracle10 impdb,Oracle10g 数据泵导出命令impdp 使用总结
weixin_31783001的博客
04-05 204
IMPDP命令行选项与EXPDP有很多相同的,不同的有:(1)REMAP_DATAFILE该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项.REMAP_DATAFIEL=source_datafie:target_datafile(2)REMAP_SCHEMA该选项用于将源方案的所有对象装载到目标方案中.REMAP_SCHEMA=source_schema:t...
oracle数据库的导入和导出（impdp,expdp）
Liveor_Die的博客
07-19 942
---- 数据的导入和导出 -- 查看已创建的 DIRECTORY 对象 SELECT * FROM DBA_DIRECTORIES -- 导出数据 -- sqlplus 中执行 --- 创建diretory 对象 CREATE DIRECTORY dump_dir AS 'D:\work\Oracle\dmpLog'; -- 将directory的权限赋给scott用户，用户scott就也可...
Oracle 数据迁移
qq_41141058的博客
07-16 1129
一、数据迁移课程概述 Oracle 跨平台跨版本数据迁移 停机时间 100G 4小时，20TB 2小时 数据量 数据库版本11.2.0.1 11.2.0.4 操作系统平台 win->linux,aix->linux,hpxu->linux 字节序 大端还是小端 x86 合适的迁移技术 数据迁移技术: dataguard RMAN异机恢复 exp/imp、expdp/impdp...
Oracle数据库迁移（expdp/impdp工具)
weixin_45031409的博客
05-09 6729
Oracle数据库利用数据泵技术进行数据库迁移、备份。
传输表空间--使用DATA_PUMP
weixin_34256074的博客
12-18 100
--传输表空间相关sql语句1,准备工作,检查字节顺序,blocksize,字符集--1.1，查询Endia Format字节顺序是否相同select d.name,i.version ,d.PLATFORM_NAME ,tp.ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM tp,v$database d,v$instance i wh...
[己解决]SYS_IMPORT_FULL_01
weixin_30337157的博客
03-19 7947
作业 "ZHCZ_UAT"."SYS_IMPORT_FULL_01" 因致命错误于 星期一 3月 20 16:56:18 2017 elapsed 0 00:08:01 停止怎么导入都是报错~，结果一查是oracle自身的bug，浪费了几个小时~解决方法：1.导出整个数据库（必须使用oracle管理员账号） exp user/pwd file=xxx.dmp log=xxx.log full...
数据库oracle开发手记——expdp/impdp 详细参数解释
u_nightroad的博客
05-10 6213
数据泵导出实用程序提供了一种用于在Oracle数据库之间传输 数据对象的机制。该实用程序可以使用以下命令进行调用: 示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp 您可以控制导出的运行方式。具体方法是: 在 'expdp' 命令后输入 各种参数。要指定各参数, 请使用关键字: 格式:expdp KEY...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值