|
《Oracle簡單常用的數(shù)據(jù)泵導(dǎo)出導(dǎo)入(expdp/impdp)命令舉例(下)》 目的：指導(dǎo)項(xiàng)目側(cè)自行進(jìn)行簡單的數(shù)據(jù)泵遷移工作。 本文實(shí)驗(yàn)環(huán)境：Oracle 11.2.0.4,，利用數(shù)據(jù)庫自帶的scott示例用戶進(jìn)行試驗(yàn)測試。
特別注意：如果后續(xù)要導(dǎo)入的數(shù)據(jù)庫版本低，所有導(dǎo)出命令就需要在后面加一個(gè)version=指定版本,。
例如11g -> 10g,，假設(shè)10g具體版本為10.2.0.1，那么就加一個(gè)版本的參數(shù) 1. 首先需要創(chuàng)建Directory這里目錄名字定義為"jy",， 若是windows平臺，對應(yīng)系統(tǒng)目錄為"E:\jingyu"; create or replace directory jy as 'E:\jingyu'; 若是Unix/Linux平臺,，對應(yīng)系統(tǒng)目錄為"/tmp/jingyu". create or replace directory jy as '/tmp/jingyu'; 注意：目錄在系統(tǒng)上需要真實(shí)存在（mkdir -p /tmp/jingyu）,，且有訪問的權(quán)限。 drwxr-xr-x. 2 oracle oinstall 4.0K May 22 16:48 jingyu 2. 使用expdp導(dǎo)出用戶數(shù)據(jù)2.1 只導(dǎo)出scott用戶的元數(shù)據(jù),，且不包含統(tǒng)計(jì)信息,；expdp system directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log $ expdp system directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log Export: Release 11.2.0.4.0 - Production on Fri May 22 16:57:59 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log 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/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_meta.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:13 2015 elapsed 0 00:00:11 2.2 只導(dǎo)出scott用戶的數(shù)據(jù)；expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log $ expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log Export: Release 11.2.0.4.0 - Production on Fri May 22 16:58:47 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.484 KB 12 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_data.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:57 2015 elapsed 0 00:00:07 2.3 只導(dǎo)出scott用戶下的emp,，dept表及數(shù)據(jù)；這里如果用scott用戶導(dǎo)出,，需要注意scott用戶對于directory的權(quán)限問題：需要dba用戶賦予scott用戶read,write目錄的權(quán)限,。 即：grant read, write on directory jy to scott; SQL> grant read, write on directory jy to scott; Grant succeeded. expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log $ expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Export: Release 11.2.0.4.0 - Production on Fri May 22 17:13:55 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.484 KB 12 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/jingyu/scott_emp_dept.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 22 17:14:04 2015 elapsed 0 00:00:06 2.4 只導(dǎo)出scott用戶下的emp，dept表結(jié)構(gòu),；expdp scott directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log $ expdp scott directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log Export: Release 11.2.0.4.0 - Production on Fri May 22 17:14:51 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=jy tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/jingyu/scott_emp_dept_meta.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Fri May 22 17:15:01 2015 elapsed 0 00:00:07 2.5 導(dǎo)出scott用戶下所有的內(nèi)容,；expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log $ expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log Export: Release 11.2.0.4.0 - Production on Fri May 22 17:15:52 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB 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/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 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/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.484 KB 12 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_all.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 17:16:06 2015 elapsed 0 00:00:11 2.6 并行導(dǎo)出scott用戶下所有的內(nèi)容；expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 $ expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 Export: Release 11.2.0.4.0 - Production on Fri May 22 16:55:13 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB 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 . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.484 KB 12 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 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/STATISTICS/TABLE_STATISTICS Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/jingyu/scott_all01.dmp /tmp/jingyu/scott_all02.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:56:12 2015 elapsed 0 00:00:54 3. 查詢當(dāng)前用戶用到的表空間select tablespace_name from user_tables union select tablespace_name from user_tab_partitions union select tablespace_name from user_indexes union select tablespace_name from user_ind_partitions; |
|
|
