ITPub博客

首页 > 数据库 > Oracle > expdp使用

expdp使用

原创 Oracle 作者:sky850623 时间:2014-02-17 20:05:13 0 删除 编辑

1.导出到默认路径

D:\>expdp scott/tiger dumpfile=scott.dmp logfile=scott.log schemas=scott  --导出到默认路径

Export: Release 10.2.0.1.0 - Production on Monday, 17 February, 2014 19:48:24

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  scott/******** dumpfile=scott.dmp logfile=scott.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 836.8 MB
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
..............
. . exported "SCOTT"."TEST_ZONE"                         5.625 KB       1 rows
. . exported "SCOTT"."TT"                                4.976 KB       3 rows
. . exported "SCOTT"."T_3"                               4.914 KB       1 rows
. . exported "SCOTT"."T_PART":"P4"                       4.937 KB       2 rows
. . exported "SCOTT"."V_DEPTNAME"                        5.289 KB       4 rows
. . exported "SCOTT"."BMB"                                   0 KB       0 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CUST_2"                                0 KB       0 rows
. . exported "SCOTT"."CUST_3"                                0 KB       0 rows
. . exported "SCOTT"."CUST_4"                                0 KB       0 rows
. . exported "SCOTT"."CUST_5"                                0 KB       0 rows
. . exported "SCOTT"."EMPLOYEES"                             0 KB       0 rows
. . exported "SCOTT"."T14"                                   0 KB       0 rows
. . exported "SCOTT"."T_1"                                   0 KB       0 rows
. . exported "SCOTT"."T_2"                                   0 KB       0 rows
. . exported "SCOTT"."T_PART":"PMAX"                         0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_03 is:
  C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully completed at 19:50:29
默认路径可以通过以下视图查看

SQL> col directory_path for a90
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------------------------
SUBDIR                         c:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         c:\oracle\product\10.2.0\db_1\demo\schema\order_entry\
MEDIA_DIR                      c:\oracle\product\10.2.0\db_1\demo\schema\product_media\
LOG_FILE_DIR                   c:\oracle\product\10.2.0\db_1\demo\schema\log\
WORK_DIR                       C:\ADE\aime_10.2_nt_push\oracle/work
DATA_FILE_DIR                  c:\oracle\product\10.2.0\db_1\demo\schema\sales_history\
DATA_PUMP_DIR                  c:\oracle\product\10.2.0\admin\orcl\dpdump\
ADMIN_DIR                      C:\ADE\aime_10.2_nt_push\oracle/md/admin

8 rows selected.

2.导到指定目录
 1)首先创建一个目录

SQL> create directory mydir as 'c:\bak';

Directory created.

SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------------------------
MYDIR                          c:\bak
SUBDIR                         c:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         c:\oracle\product\10.2.0\db_1\demo\schema\order_entry\
MEDIA_DIR                      c:\oracle\product\10.2.0\db_1\demo\schema\product_media\
LOG_FILE_DIR                   c:\oracle\product\10.2.0\db_1\demo\schema\log\
WORK_DIR                       C:\ADE\aime_10.2_nt_push\oracle/work
DATA_FILE_DIR                  c:\oracle\product\10.2.0\db_1\demo\schema\sales_history\
DATA_PUMP_DIR                  c:\oracle\product\10.2.0\admin\orcl\dpdump\
ADMIN_DIR                      C:\ADE\aime_10.2_nt_push\oracle/md/admin

9 rows selected.
2)导出scott用户下的对象

D:\>expdp scott/tiger directory=mydir dumpfile=scott.dmp logfile=scott.log schemas=scott

Export: Release 10.2.0.1.0 - Production on Monday, 17 February, 2014 20:03:17

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  scott/******** directory=mydir dumpfile=scott.dmp logfile=scott.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 836.8 MB
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"."T_1"                                   0 KB       0 rows
. . exported "SCOTT"."T_2"                                   0 KB       0 rows
. . exported "SCOTT"."T_PART":"PMAX"                         0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_03 is:
  C:\BAK\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully completed at 20:05:02
3)不指定directory参数
C:\>expdp scott/tiger dumpfile=mydir:scott.dmp logfile=mydir:scott.log schemas=scott

Export: Release 10.2.0.1.0 - Production on Tuesday, 22 April, 2014 11:41:36
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_03":  scott/******** dumpfile=mydir:scott.dmp logfile=mydir:scott.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 888.5 MB
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/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
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/POST_SCHEMA/PROCOBJ
. . exported "SCOTT"."IDXTEST"                           601.7 MB 6456448 rows
. . exported "SCOTT"."CUST_DET"                          5.937 KB       1 rows
. . exported "SCOTT"."DEPART"                            5.281 KB       2 rows
...省略
. . exported "SCOTT"."T_2"                                   0 KB       0 rows
. . exported "SCOTT"."T_PART":"PMAX"                         0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_03 is:
  C:\BAK\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_03" successfully completed at 11:44:35



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26937943/viewspace-1082779/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle闪回特性
请登录后发表评论 登录
全部评论

注册时间:2013-05-30

  • 博文量
    154
  • 访问量
    473026