导出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.
