expdp sample 應用一例
expdp sample 應用一例
DBA在日常維護中，資料遷移也是基本功。新業務上線僅需要表結構和少數資料時。可以使用expdp sample引數實現按比例匯出資料。
1.未使用sample匯出資料
[oracle@dbdou01 ~]$ expdp system/oracle directory=dump dumpfile=user.dmp logfile=user.log schemas=scott
Export: Release 11.2.0.4.0 - Production on Thu Aug 18 14:12:43 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
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/******** directory=dump dumpfile=user.dmp logfile=user.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 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/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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."TEST" 8.476 KB 14 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:
/u01/app/oracle/oradata/dbdou01/user.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Aug 18 14:13:02 2016 elapsed 0 00:00:17
2.sample=50效果
[oracle@dbdou01 ~]$ expdp system/oracle directory=dump dumpfile=user_50.dmp logfile=user_50.log schemas=scott sample=50
Export: Release 11.2.0.4.0 - Production on Thu Aug 18 14:15:18 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
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/******** directory=dump dumpfile=user_50.dmp logfile=user_50.log schemas=scott sample=50
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 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/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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.835 KB 0 rows
. . exported "SCOTT"."EMP" 8.359 KB 9 rows
. . exported "SCOTT"."SALGRADE" 5.820 KB 2 rows
. . exported "SCOTT"."TEST" 8.179 KB 6 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:
/u01/app/oracle/oradata/dbdou01/user_50.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Aug 18 14:15:32 2016 elapsed 0 00:00:13
3.sample=20效果
[oracle@dbdou01 ~]$ expdp system/oracle directory=dump dumpfile=user_20.dmp logfile=user_20.log schemas=scott sample=20
Export: Release 11.2.0.4.0 - Production on Thu Aug 18 14:16:07 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
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/******** directory=dump dumpfile=user_20.dmp logfile=user_20.log schemas=scott sample=20
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 51.19 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/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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.859 KB 1 rows
. . exported "SCOTT"."EMP" 8.164 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.804 KB 1 rows
. . exported "SCOTT"."TEST" 8.101 KB 4 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:
/u01/app/oracle/oradata/dbdou01/user_20.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Aug 18 14:16:20 2016 elapsed 0 00:00:12
總結：
合適的場景使用合適的技術可以提升效率數倍，所以請DBA多多溝通。
########################################################################################
版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文連結： http://blog.itpub.net/26442936/viewspace-2123672/
########################################################################################
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-2123672/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 應用程式日誌Sample
- SAMPLE語句在統計資訊收集中應用
- 【移動資料】data pump（中） EXPDP 應用
- 【PB】powerbuilder中xml的應用一例UIXML
- Spring AOP實際應用一例Spring
- 【expdp】10g資料泵expdp工具選項詳解及應用示例
- crypto++5.0應用一例 (2千字)
- Javacc sampleJava
- Redis應用一例（存證數量用計數器實現）Redis
- sample a texture as a rendertarget
- netty sampleNetty
- VB與資料庫應用一例----生成EXCEL表 (轉)資料庫Excel
- Command 模式在 SAP Spartacus 中的具體應用一例模式
- table type usage sample:
- Error in Memento Sample Code?Error
- A Simple Sample for Expression Tree (轉)Express
- WLS 10.3.0 更新發布應用異常終止處理一例
- Directx3D SimpleSample Sample3D
- (C language Sample ) Compile procedureCompile
- Blade 模版擴充套件應用一例：為低版 Laravel 新增 csrf 及 method 指令套件Laravel
- exp/expdp imp/impdp的使用【實戰實用】
- Selenium IDE 巨集 試用 一例IDE
- [CTO札記]業務流程圖Sample流程圖
- Oracle OAF(Oracle Application Framework) SampleOracleAPPFramework
- Oracle expdpOracle
- 透過SDK函式實現WIN32應用程式的唯一例項。 (轉)函式Win32
- 系統cpu佔用超高故障分析一例
- ARCore學習之旅：ARCore Sample 導讀
- Sample Schemas建庫後手動建立
- Teradata 之top n與sample n
- 動態呼叫儲存過程 sample:儲存過程
- Oracle 自己帶的Sample Sechemas 介紹Oracle
- impdp/expdp 示例
- oracle expdp and impdpOracle
- 【EXPDP】運用PARALLEL 和FILESIZE引數提高備份效率Parallel
- Oracle Database 19c安裝Sample SchemasOracleDatabase
- SAP QM 三種型別的Physical Sample型別
- Oracle 12CR2 Install the Sample SchemasOracle