导出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.
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
