加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle Database Data Pump（expdp/impdp）

发布时间：2020-12-12 13:31:44 所属栏目：百科 来源：网络整理
导读：导出 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='TPCC'; OWNER??????OBJECT_NAME???????????
导出 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='TPCC'; OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????STATUS ----------?---------------------------------------------?--------------------?-------------------- 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 TPCC???????INVENTORIES???????????????????????????????????TABLE????????????????VALID TPCC???????PRODUCT_INFORMATION???????????????????????????TABLE????????????????VALID TPCC???????LOGON?????????????????????????????????????????TABLE????????????????VALID TPCC???????PRODUCT_DESCRIPTIONS??????????????????????????TABLE????????????????VALID TPCC???????ORDERENTRY_METADATA???????????????????????????TABLE????????????????VALID OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????STATUS ----------?---------------------------------------------?--------------------?-------------------- TPCC???????PRODUCTS??????????????????????????????????????VIEW?????????????????VALID TPCC???????PRODUCT_PRICES????????????????????????????????VIEW?????????????????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 TPCC???????ORDER_PK??????????????????????????????????????INDEX????????????????VALID TPCC???????PRODUCT_INFORMATION_PK????????????????????????INDEX????????????????VALID TPCC???????PRD_DESC_PK???????????????????????????????????INDEX????????????????VALID TPCC???????INVENTORY_PK??????????????????????????????????INDEX????????????????VALID OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????STATUS ----------?---------------------------------------------?--------------------?-------------------- 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 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 OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????STATUS ----------?---------------------------------------------?--------------------?-------------------- 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???????CUST_FUNC_LOWER_NAME_IX???????????????????????INDEX????????????????VALID TPCC???????CARDDETAILS_CUST_IX???????????????????????????INDEX????????????????VALID TPCC???????CUSTOMER_SEQ??????????????????????????????????SEQUENCE?????????????VALID TPCC???????ORDERS_SEQ????????????????????????????????????SEQUENCE?????????????VALID TPCC???????ADDRESS_SEQ???????????????????????????????????SEQUENCE?????????????VALID TPCC???????LOGON_SEQ?????????????????????????????????????SEQUENCE?????????????VALID OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????STATUS ----------?---------------------------------------------?--------------------?-------------------- TPCC???????CARD_DETAILS_SEQ??????????????????????????????SEQUENCE?????????????VALID TPCC???????ORDERENTRY????????????????????????????????????PACKAGE??????????????VALID TPCC???????ORDERENTRY????????????????????????????????????PACKAGE?BODY?????????VALID 47?rows?selected. SQL>?host?mkdir?/tmp/tpccdump? SQL>?create?directory?tpcc_dump?as?'/tmp/tpccdump'; Directory?created. 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????????????????????????????TPCC_DUMP??????????????????????/tmp/tpccdump SYS????????????????????????????XMLDIR?????????????????????????/ade/b/2125410156/oracle/rdbms/xml SQL>?grant?read,?write?on?directory?tpcc_dump?to?tpcc; Grant?succeeded. [ [email?protected]?~]$?expdp?tpcc/password?schemas=tpcc?directory=tpcc_dump?dumpfile=tpcc_s.dmp Export:?Release?11.2.0.1.0?-?Production?on?Thu?Jul?5?10:11:17?2018 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,?OLAP,?Data?Mining?and?Real?Application?Testing?options Starting?"TPCC"."SYS_EXPORT_SCHEMA_01":??tpcc/********?schemas=tpcc?directory=tpcc_dump?dumpfile=tpcc_s.dmp? Estimate?in?progress?using?BLOCKS?method... Processing?object?type?SCHEMA_EXPORT/TABLE/TABLE_DATA Total?estimation?using?BLOCKS?method:?1023?MB 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/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/COMMENT 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/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/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing?object?type?SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS .?.?exported?"TPCC"."ORDER_ITEMS"????????????????????????228.5?MB?4292966?rows .?.?exported?"TPCC"."INVENTORIES"????????????????????????15.28?MB??902463?rows .?.?exported?"TPCC"."ORDERS"?????????????????????????????129.2?MB?1431488?rows .?.?exported?"TPCC"."ADDRESSES"??????????????????????????110.4?MB?1500623?rows .?.?exported?"TPCC"."CUSTOMERS"??????????????????????????108.1?MB?1000554?rows .?.?exported?"TPCC"."CARD_DETAILS"???????????????????????63.90?MB?1500554?rows .?.?exported?"TPCC"."LOGON"??????????????????????????????51.33?MB?2387354?rows .?.?exported?"TPCC"."ORDERENTRY_METADATA"????????????????5.539?KB???????4?rows .?.?exported?"TPCC"."PRODUCT_DESCRIPTIONS"???????????????224.5?KB????1000?rows .?.?exported?"TPCC"."PRODUCT_INFORMATION"????????????????187.9?KB????1000?rows .?.?exported?"TPCC"."WAREHOUSES"?????????????????????????35.08?KB????1000?rows Master?table?"TPCC"."SYS_EXPORT_SCHEMA_01"?successfully?loaded/unloaded ****************************************************************************** Dump?file?set?for?TPCC.SYS_EXPORT_SCHEMA_01?is: ??/tmp/tpccdump/tpcc_s.dmp Job?"TPCC"."SYS_EXPORT_SCHEMA_01"?successfully?completed?at?10:12:11 


导入
[[email?protected]?~]$?impdp?system/oracle?directory=tpcc_dump?dumpfile=tpcc_s.dmp?remap_schema=tpcc:soe?remap_tablespace=tpcc:soe?transform=storage:n,segment_attributes:n
Import:?Release?11.2.0.1.0?-?Production?on?Thu?Jul?5?11:40:50?2018
Copyright?(c)?1982,?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,?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=tpcc_dump?dumpfile=tpcc_s.dmp?remap_schema=tpcc:soe?remap_tablespace=tpcc:soe?transform=storage:n,segment_attributes:n?
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?"SOE"."ORDER_ITEMS"?????????????????????????228.5?MB?4292966?rows
.?.?imported?"SOE"."INVENTORIES"?????????????????????????15.28?MB??902463?rows
.?.?imported?"SOE"."ORDERS"??????????????????????????????129.2?MB?1431488?rows
.?.?imported?"SOE"."ADDRESSES"???????????????????????????110.4?MB?1500623?rows
.?.?imported?"SOE"."CUSTOMERS"???????????????????????????108.1?MB?1000554?rows
.?.?imported?"SOE"."CARD_DETAILS"????????????????????????63.90?MB?1500554?rows
.?.?imported?"SOE"."LOGON"???????????????????????????????51.33?MB?2387354?rows
.?.?imported?"SOE"."ORDERENTRY_METADATA"?????????????????5.539?KB???????4?rows
.?.?imported?"SOE"."PRODUCT_DESCRIPTIONS"????????????????224.5?KB????1000?rows
.?.?imported?"SOE"."PRODUCT_INFORMATION"?????????????????187.9?KB????1000?rows
.?.?imported?"SOE"."WAREHOUSES"??????????????????????????35.08?KB????1000?rows
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_SPEC
Processing?object?type?SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
ORA-39082:?Object?type?ALTER_PACKAGE_SPEC:"SOE"."ORDERENTRY"?created?with?compilation?warnings
Processing?object?type?SCHEMA_EXPORT/VIEW/VIEW
Processing?object?type?SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082:?Object?type?PACKAGE_BODY:"SOE"."ORDERENTRY"?created?with?compilation?warnings
Processing?object?type?SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing?object?type?SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing?object?type?SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Job?"SYSTEM"."SYS_IMPORT_FULL_01"?completed?with?2?error(s)?at?11:47:14


编译无效对象
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,?status?from?dba_objects?where?status?=?'INVALID';
OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????Status
----------?---------------------------------------------?--------------------?--------------------
SOE????????ORDERENTRY????????????????????????????????????PACKAGE?BODY?????????INVALID

SQL>?alter?package?soe.orderentry?compile?package;
Warning:?Package?altered?with?compilation?errors.

SQL>?col?text?for?a50
SQL>?select?owner,name,text?from?dba_errors?where?owner='SOE'?and?name='ORDERENTRY';
OWNER??????Tablespace?Name????????????????TEXT
----------?------------------------------?--------------------------------------------------
SOE????????ORDERENTRY?????????????????????PLS-00201:?identifier?'DBMS_LOCK'?must?be?declared
SOE????????ORDERENTRY?????????????????????PL/SQL:?Statement?ignored
SOE????????ORDERENTRY?????????????????????PLS-00201:?identifier?'DBMS_LOCK'?must?be?declared
SOE????????ORDERENTRY?????????????????????PL/SQL:?Statement?ignored

SQL>?grant?execute?on?sys.dbms_lock?to?soe;
revoke?execute?on?sys.dbms_lock?from?soe;

SQL>?alter?package?soe.orderentry?compile?package;
Package?altered.

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

OWNER??????OBJECT_NAME???????????????????????????????????OBJECT_TYPE??????????Status
----------?---------------------------------------------?--------------------?--------------------
SOE????????CUSTOMER_SEQ??????????????????????????????????SEQUENCE?????????????VALID
SOE????????ORDERS_SEQ????????????????????????????????????SEQUENCE?????????????VALID
SOE????????CARD_DETAILS_SEQ??????????????????????????????SEQUENCE?????????????VALID
SOE????????LOGON_SEQ?????????????????????????????????????SEQUENCE?????????????VALID
SOE????????ADDRESS_SEQ???????????????????????????????????SEQUENCE?????????????VALID
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
SOE????????CUSTOMERS_PK??????????????????????????????????INDEX????????????????VALID
SOE????????ADDRESS_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
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
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????????CARDDETAILS_CUST_IX???????????????????????????INDEX????????????????VALID
SOE????????ORDERENTRY????????????????????????????????????PACKAGE??????????????VALID
SOE????????PRODUCTS??????????????????????????????????????VIEW?????????????????VALID
SOE????????PRODUCT_PRICES????????????????????????????????VIEW?????????????????VALID
SOE????????ORDERENTRY????????????????????????????????????PACKAGE?BODY?????????VALID
SOE????????CUST_FUNC_LOWER_NAME_IX???????????????????????INDEX????????????????VALID

47?rows?selected.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同