[Oracle Import and Export] expdp

1. view the directory object of the data pump. The directory object is the file path stored when the data pump exports data:

 

SQL> SELECT * FROM dba_directories; OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH------------------------------ ------------------------------ --------------------------------------------------------------------------------SYS                            ADMIN_DIR                          /ade/aime_10.2_lnx_push/oracle/md/adminSYS                            DATA_PUMP_DIR                   /home/oracle/oracle/product/10.2.0/db_1/rdbms/log/SYS                            WORK_DIR                            /ade/aime_10.2_lnx_push/oracle/work

 

 

Only sys or system users can use the default data_pump_dir directory object. Therefore, system users can enable the data pump export job without providing the directory name.

[oracle@localhost ~]$ expdp system/lubinsuExport: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 17:04:18Copyright (c) 2003, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 320 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SYNONYM/SYNONYMProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/DB_LINKProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTIONProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/VIEW/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTIONProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          5.953 KB       2 rows. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.507 KB      28 rows. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.648 KB      19 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.296 KB       3 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           5.914 KB       2 rows. . exported "SYSTEM"."DEF$_AQCALL"                          0 KB       0 rows. . exported "SYSTEM"."DEF$_AQERROR"                         0 KB       0 rows. . exported "SYSTEM"."DEF$_CALLDEST"                        0 KB       0 rows. . exported "SYSTEM"."DEF$_DEFAULTDEST"                     0 KB       0 rows. . exported "SYSTEM"."DEF$_DESTINATION"                     0 KB       0 rows. . exported "SYSTEM"."DEF$_ERROR"                           0 KB       0 rows. . exported "SYSTEM"."DEF$_LOB"                             0 KB       0 rows. . exported "SYSTEM"."DEF$_ORIGIN"                          0 KB       0 rows. . exported "SYSTEM"."DEF$_PROPAGATOR"                      0 KB       0 rows. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"             0 KB       0 rows. . exported "SYSTEM"."DEF$_TEMP$LOB"                        0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$APPLY_MILESTONE"             0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$APPLY_PROGRESS":"P0"         0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$EVENTS"                      0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$HISTORY"                     0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$PARAMETERS"                  0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$PLSQL"                       0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$SCN"                         0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$SKIP"                        0 KB       0 rows. . exported "SYSTEM"."LOGSTDBY$SKIP_TRANSACTION"            0 KB       0 rows. . exported "SYSTEM"."MVIEW$_ADV_INDEX"                     0 KB       0 rows. . exported "SYSTEM"."MVIEW$_ADV_PARTITION"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_CONFLICT"                     0 KB       0 rows. . exported "SYSTEM"."REPCAT$_DDL"                          0 KB       0 rows. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"                   0 KB       0 rows. . exported "SYSTEM"."REPCAT$_EXTENSION"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_FLAVORS"                      0 KB       0 rows. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_GENERATED"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"            0 KB       0 rows. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"                  0 KB       0 rows. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"             0 KB       0 rows. . exported "SYSTEM"."REPCAT$_PRIORITY"                     0 KB       0 rows. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"            0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPCAT"                       0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPCATLOG"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPOBJECT"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPPROP"                      0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RESOLUTION"                   0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"                0 KB       0 rows. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_CZ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_HA"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_LYG"                     0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_NJ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_NT"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_PROVICE"                 0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_SQ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_SZ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_TZ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_WX"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_XZ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_YC"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_YZ"                      0 KB       0 rows. . exported "SYSTEM"."SERV_MSG":"P_ZJ"                      0 KB       0 rows. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:  /home/oracle/oracle/product/10.2.0/db_1/rdbms/log/expdat.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:04:57

 

Other users need to authorize the use of directory objects: 1. create directory objects:

 

[oracle@localhost ~]$ sqlplus system/lubinsuSQL*Plus: Release 10.2.0.1.0 - Production on Wed May 1 17:07:28 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> create directory dpump_dir01 as '/home/lubinsu/dpump';                   Directory created.

 

 

2. Authorization:

 

SQL> grant read,write on directory dpump_dir01 to lubinsu;Grant succeeded.

3. Export:

 

 

[lubinsu@localhost oracle]$ expdp lubinsu/lubinsu DIRECTORY=dpump_dir01 dumpfile=lubinsu.dmpExport: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 17:12:38Copyright (c) 2003, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsORA-39002: invalid operationORA-39070: Unable to open the log file.ORA-29283: invalid file operationORA-06512: at "SYS.UTL_FILE", line 475ORA-29283: invalid file operation

 

 

4. the directory in the real path is not actually created, and Oracle will not automatically create the directory to create the real directory and modify the permission: chmod 777 lubinsu chmod 777 dpump open the dpump under the lubinsu user to other users
Re-execute the previous operation:

[lubinsu@localhost oracle]$ expdp lubinsu/lubinsu dumpfile=lubinsu.dmp directory=DPUMP_DIR05Export: Release 10.2.0.1.0 - Production on Wednesday, 01 May, 2013 22:24:43Copyright (c) 2003, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "LUBINSU"."SYS_EXPORT_SCHEMA_01":  lubinsu/******** dumpfile=lubinsu.dmp directory=DPUMP_DIR05 Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128.6 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/DB_LINKProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "LUBINSU"."T_TEST"                          36.69 MB  393800 rows. . exported "LUBINSU"."GRID_MSG"                        20.79 MB  156588 rows. . exported "LUBINSU"."BSS_PO_SPEC_D"                   6.229 MB   26570 rows. . exported "LUBINSU"."MANAGER_TM"                      3.885 MB   65099 rows. . exported "LUBINSU"."MANAGER_TM_T"                    3.937 MB   65994 rows. . exported "LUBINSU"."AREA_TM"                         1.786 MB   33105 rows. . exported "LUBINSU"."SERV_MSG":"P_CZ"                 2.055 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_HA"                 2.041 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_LYG"                1.991 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_NJ"                 2.011 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_NT"                 2.197 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_SQ"                 1.996 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_SZ"                 2.084 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_TZ"                 1.951 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_WX"                 2.059 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_XZ"                 2.038 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_YC"                 1.935 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_YZ"                 1.819 MB   10008 rows. . exported "LUBINSU"."SERV_MSG":"P_ZJ"                 1.927 MB   10008 rows. . exported "LUBINSU"."PROCEDURE_RESULT"                6.335 KB      15 rows. . exported "LUBINSU"."STAT_PROCEDURE"                  46.99 KB     163 rows. . exported "LUBINSU"."TEACHER_VPN_STAT_DAILY"          80.25 KB    2377 rows. . exported "LUBINSU"."DEPT"                            5.656 KB       4 rows. . exported "LUBINSU"."EMP"                             7.820 KB      14 rows. . exported "LUBINSU"."LATN_AREA_INFO"                  16.11 KB      14 rows. . exported "LUBINSU"."PO_SPEC_CATE_TREE_RELA"          24.95 KB     578 rows. . exported "LUBINSU"."TEACHER_VPN_STAT_DAILY_T"        9.937 KB      65 rows. . exported "LUBINSU"."SERV_MSG":"P_PROVINCE"               0 KB       0 rowsMaster table "LUBINSU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for LUBINSU.SYS_EXPORT_SCHEMA_01 is:  /home/lubinsu/dpump/lubinsu.dmpJob "LUBINSU"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:24:58

Exported successfully