expdp使用

最新推荐文章于 2024-03-22 07:00:00 发布

cjllyl654321

最新推荐文章于 2024-03-22 07:00:00 发布

阅读量110

点赞数

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

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

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