设为首页收藏本站
查看: 105|回复: 0

[PHP] Oracle数据库导出数据泵(EXPDP)文件存放的位置

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2018-7-11 03:36:00 | 显示全部楼层 |阅读模式
    数据泵是服务器端工具,导出的文件是放在数据库所在的服务器上,当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别,当然是有优先级顺序的

    数据泵是服务器端工具,免备案空间,导出的文件是放在数据库所在的服务器上,当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别,当然是有优先级顺序的,优先级从上往下
    1.每个文件单独的指定具体的目录
    2.expdp导出时,指定的目录参数
    3.用户定义的环境变量DATA_PUMP_DIR指定的目录
    4.默认的目录对象DATA_PUMP_DIR

    当然了对于oracle11g R2来说,又引入了一个可选项,我们就当是5
    5.DATA_PUMP_DIR_SCHEMA_NAME目录


    一、默认的目录对象DATA_PUMP_DIR测试

    SQL> desc dba_directories
    Name Null? Type
    ----------------------------------------------------------------- -------- ---------------------- OWNER NOT NULL VARCHAR2(30)
    DIRECTORY_NAME NOT NULL VARCHAR2(30)
    DIRECTORY_PATH VARCHAR2(4000)



    SQL> set linesize 120 pagesize 100
    SQL> col OWNER for a5
    SQL> col DIRECTORY_NAME for a22
    SQL> col DIRECTORY_PATH for a80


    SQL> select * from dba_directories;

    OWNER DIRECTORY_NAME DIRECTORY_PATH
    ----- ---------------------- ----------------------------------------------------------

    SYS SUBDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep
    SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry/
    SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/log/
    SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/product_media/
    SYS XMLDIR /u01/app/oracle/product/11.2.0/db/rdbms/xml
    SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/sales_history/
    SYS DATA_PUMP_DIR /u01/app/oracle/admin/tj01/dpdump/
    SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db/ccr/state



    通过查询我们看到,所有的目录都属于SYS用户,而不管是哪个用户创建的,香港空间,在数据库里已经提前建好了这个目录对象DATA_PUMP_DIR。如果在使用expdp导出时,不指定目录对象参数,Oracle会使用数据库缺省的目录DATA_PUMP_DIR,不过如果想使用这个目录的话,用户需要具有exp_full_database的权限才行



    SQL> conn scott/tiger
    Connected.

    SQL> select * from tab;
    TNAME TABTYPE CLUSTERID
    ------------------------------ ------- ----------
    BONUS TABLE
    DEPT TABLE
    EMP TABLE
    SALGRADE TABLE


    SQL> create table demo as select empno,ename,sal,deptno from emp;
    Table created.


    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    由于没有exp_full_database,所以这样的导出会报错,网站空间,你可以选择授权或者使用有权限的用户

    [oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp
    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 13:48:19 2012
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    ORA-39002: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39145: directory object parameter must be specified and non-null


    [oracle@asm11g ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:58:14 2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    SQL> grant exp_full_database to scott;
    Grant succeeded.

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    授权之后,导出成功,并且导出的文件和日志会到DATA_PUMP_DIR对象指定的目录
    [oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp

    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 13:58:52 2012
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp.dmp tables=emp
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported "SCOTT"."EMP" 8.562 KB 14 rows
    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
    /u01/app/oracle/admin/tj01/dpdump/emp.dmp
    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 13:59:05


    对于oracle11g来说,我们还可以定义一个新的目录对象,对象名称是DATA_PUMP_DIR_SCHEMA_NAME,如果定义了这个目录对象,并授予权限,在用户导出没有指定

    具体的目录参数的情况下,导出文件会到这个目录

    [oracle@asm11g ~]$ mkdir sdir
    [oracle@asm11g ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:13:06 2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    SQL> create directory data_pump_dir_scott as '/home/oracle/sdir';
    Directory created.

    SQL> grant read,write on directory data_pump_dir_scott to scott;
    Grant succeeded.

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    [oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp1.dmp tables=emp



    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:14:29 2012

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    Database Directory Object has defaulted to: "DATA_PUMP_DIR_SCOTT".
    Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp5.dmp tables=emp
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported "SCOTT"."EMP" 8.562 KB 14 rows
    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
    /home/oracle/sdir/emp1.dmp
    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:14:34



    二、如果设定了环境变量DATA_PUMP_DIR的话,环境变量会覆盖缺省设置
    [oracle@asm11g ~]$ mkdir udir
    [oracle@asm11g ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:59:15 2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    SQL> create directory udir as '/home/oracle/udir';
    Directory created.

    SQL> grant write ,read on directory udir to scott;
    Grant succeeded.

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    注意,在定义环境变量的时候,后面的值要用大写
    [oracle@asm11g ~]$ export DATA_PUMP_DIR=udir
    [oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp2.dmp tables=emp

    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:07 2012

    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    ORA-39002: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39087: directory name udir is invalid


    [oracle@asm11g ~]$ export DATA_PUMP_DIR=UDIR
    [oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp2.dmp tables=emp
    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:23 2012
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    Database Directory Object has defaulted to: "UDIR".
    Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp3.dmp tables=emp
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported "SCOTT"."EMP" 8.562 KB 14 rows
    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
    /home/oracle/udir/emp2.dmp
    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:05:29



    三、如果导出时定义了参数directory,会使用参数指定的目录
    [oracle@asm11g ~]$ expdp scott/tiger directory=udir tables=emp

    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:07:33 2012
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=udir tables=emp
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported "SCOTT"."EMP" 8.562 KB 14 rows
    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
    /home/oracle/udir/expdat.dmp
    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:07:41



    四、如果每个文件都指定了具体的目录的话,会以文件定义的目录为准
    [oracle@asm11g ~]$ mkdir userdir
    [oracle@asm11g ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:09:58 2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    SQL> create or replace directory userdir as '/home/oracle/userdir';
    Directory created.

    SQL> grant read,write on directory userdir to scott;
    Grant succeeded.

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options



    [oracle@asm11g ~]$ expdp scott/tiger dumpfile=userdir:emp3.dmp logfile=userdir:test.log tables=emp

    Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:12:38 2012
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    Database Directory Object has defaulted to: "UDIR".
    Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=userdir:emp4.dmp logfile=userdir:test.log tables=emp
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported "SCOTT"."EMP" 8.562 KB 14 rows
    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
    /home/oracle/userdir/emp3.dmp
    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:12:44


    oracle视频教程请关注:
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表