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/，如需轉載，請註明出處，否則將追究法律責任。