Oracle Directory（目录）介绍

Oracle Directory（目录）可以让用户在Oracle数据库中灵活地对文件进行读写操作，极大地提高了Oracle的易用性和可扩展性。其语法如下所示：

CREATE [OR REPLACE] DIRECTORY DIRECTORY AS 'PATHNAME';

创建和删除Directory的权限为：CREATE ANY DIRECTORY、DROP ANY DIRECTORY。赋予普通用户使用Directory的权限包括READ、WRITE、EXECUTE，也可以直接写ALL，如下所示：

GRANT READ,WRITE,EXECUTE ON DIRECTORY EXP_DIR_LHR TO LHR;

GRANT ALL ON DIRECTORY EXP_DIR_LHR TO LHR;

需要注意的是，在创建Directory数据库对象时对应的路径不存在也不会报错，Oracle数据库并不会到操作系统上检验路径的存在性，只有在使用时才会校验，因此在创建Directory时谨记对应的路径的真实存在性，否则可能会报ORA-29913或ORA-29400的错误。

下面给出一个创建Oracle Directory的例子：

SYS@lhrdb> CREATE OR REPLACE DIRECTORY EXP_DIR_LHR AS '/tmp';

Directory created.

SYS@lhrdb> GRANT READ,WRITE ON DIRECTORY EXP_DIR_LHR TO LHR;

Grant succeeded.

SYS@lhrdb> COL DIRECTORY_PATH FORMAT A20

SYS@lhrdb> SELECT DIRECTORY_NAME ,DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='EXP_DIR_LHR';

DIRECTORY_NAME DIRECTORY_PATH

------------------------------ --------------------

EXP_DIR_LHR /tmp

MOS上对DIRECTORY的解释( 266875.1 )：

(1)、基于服务端 vs 基于客户端

DIRECTORY变量指出了expdp导出数据泵或impdp导入数据泵将dump文件、log文件以及SQL文件(仅适用于impdp)写到什么路径。

因为导出数据泵和导入数据泵都是基于服务端的，不是基于客户端的，因此输出文件的路径都是相对于服务端目录的路径。数据泵要求将目录路径作为一个目录对象。一个目录对象将文件系统的一个目录路径映射为一个名称。

(2)、如何创建一个目录对象？

为了创建目录，必须具有DBA角色或者赋予了CREATE ANY DIRECTORY权限。

示例：

Window平台

CONNECT system/manager CREATE OR REPLACE DIRECTORY my_dir as 'D:\DataPump'; CREATE OR REPLACE DIRECTORY my_logdir as 'E:\logs'; GRANT read, write ON DIRECTORY my_dir TO scott; GRANT read, write ON DIRECTORY my_logdir TO scott;

Unix平台

CONNECT system/manager GRANT CREATE ANY DIRECTORY TO scott; CONNECT scott/tiger CREATE OR REPLACE DIRECTORY my_dir as '/usr/DataPump'; CREATE OR REPLACE DIRECTORY my_logdir as '/usr/logs';

如果普通用户被赋予了CREATE ANY DIRECTORY权限，那么用户就自动具备目录的READ和WRITE权限。

注意：CREATE DIRECTORY语句不会创建磁盘的真实目录，如果目录是无效的，数据泵作业会报错：

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation

(3)、如何查询可用的目录？

可以使用如下SQL查询具有READ和WRITE权限的目录：

SET lines 80 COL grantee FORMAT a20 COL privilege FORMAT a10 SELECT directory_name, grantee, privilege FROM user_tab_privs t, all_directories d WHERE t.table_name(+)=d.directory_name ORDER BY 1,2,3; DIRECTORY_NAME GRANTEE PRIVILEGE ------------------------------ -------------------- ---------- DATA_PUMP_DIR EXP_FULL_DATABASE READ DATA_PUMP_DIR EXP_FULL_DATABASE WRITE DATA_PUMP_DIR IMP_FULL_DATABASE READ DATA_PUMP_DIR IMP_FULL_DATABASE WRITE MY_DIR SCOTT READ MY_DIR SCOTT WRITE MY_DIR SYSTEM READ MY_DIR SYSTEM WRITE MY_LOGDIR SCOTT READ MY_LOGDIR SCOTT WRITE MY_LOGDIR SYSTEM READ MY_LOGDIR SYSTEM WRITE ...

(4)、需要的操作系统权限。

对目录对象的READ或WRITE权限仅仅表示Oracle将会替你读或写这个文件。你并没有访问Oracle以外文件的权限，除非你具备合适的操作系统权限。

(5)、数据泵如何决定文件的路径

5.1 如果目录对象是文件标示符的一部分，那么目录对象指定的路径就需要使用。在目录MY_DIR创建dump文件的示例：

> expdp scott/tiger DUMPFILE=my_dir:expdp_s.dmp NOLOGFILE=Y

5.2 如果目录对象不代表一个文件，那么就需要使用DIRECTORY变量命名的目录对象。目录MY_DIR中创建dump文件，目录MY_DIR_LOG中创建日志文件的示例：

> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \ LOGFILE=my_logdir:expdp_s.log

5.3 如果没有明确目录对象，也没有以DIRECTORY变量命名的目录对象，那么环境变量DATA_PUMP_DIR将会使用。环境变量是在在运行导出和导入数据泵应用的客户端系统中使用操作系统命令定义的，分配给基于客户端环境变量的取值必须和基于服务端的目录对象一致，且必须首先在服务器端建立。

目录MY_DIR中创建dump文件和MY_DIR_LOG中创建日志文件的示例：

在使用expdp的客户端机器上，设定环境变量：

-- On windows, place all expdp parameters on one single line: C:\> set DATA_PUMP_DIR=MY_DIR C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp LOGFILE=my_logdir:expdp_s.log

注意环境变量DATA_DUMP_DIR对应的目录名称是大小写敏感的。设定错误的DATA_PUMP_DIR环境变量会报错，例如：DATA_PUMP_DIR=My_Dir：

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name My_Dir is invalid

5.4 如果之前三种情况都没有创建目录对象，作为一个具有权限的用户(例如具有EXP_FULL_DATABASE或IMP_FULL_DATABASE角色)，那么数据泵试图使用默认的基于服务器端的目录对象，DATA_PUMP_DIR。理解数据泵不会创建DATA_PUMP_DIR目录对象是非常重要的。仅当授权用户未使用任何之前提到的机制创建的目录对象时，才会尝试使用DATA_PUMP_DIR。这个默认的目录对象必须首先由DBA创建。不要将这个和同名的基于客户端的环境变量相混淆。

首先，清空DATA_PUMP_DIR环境变量：

C:\> set DATA_PUMP_DIR=

创建DATA_PUMP_DIR的目录：

CONNECT SYSTEM/MANAGER CREATE OR REPLACE DIRECTORY data_pump_dir AS 'D:\DataPump'; GRANT read, write ON DIRECTORY data_pump_dir TO scott;

-- On windows, place all expdp parameters on one single line: C:\> expdp system/manager@my_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott

如果SCOTT用户不是授权用户，不能使用默认的DATA_PUMP_DIR。

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39145: directory object parameter must be specified and non-null

用户SCOTT的解决方法：如上面5.3，SCOTT可以设置环境变量DATA_PUMP_DIR为MY_DIR：

-- On windows, place all expdp parameters on one single line: C:\> set DATA_PUMP_DIR=MY_DIR C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott

或者这种特定场景下，用户SCOTT也可以有目录DATA_PUMP_DIR的读和写权限：

-- On windows, place all expdp parameters on one single line: C:\> set DATA_PUMP_DIR=DATA_PUMP_DIR C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott

实验：

创建目录：CREATE DIRECTORY UTL_FILE_DIR AS '/oracle/backup';

向用目录对象标识的文件写内容：

						SQL> declare 
  2  fhandle utl_file.file_type; 
  3  begin 
  4  fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w'); 
  5  utl_file.put_line(fhandle, 'test write one'); 
  6  utl_file.put_line(fhandle, 'test write two'); 
  7  utl_file.fclose(fhandle); 
  8  end; 
  9  / 
PL/SQL procedure successfully completed. 

SQL> ! 
ora10g@vm-vmw4131-t$ more /oracle/backup/example.txt 
test write one 
test write two 
					

						
					

						读取使用目录对象DIRECTORY标识的文件内容： 
					

						SQL> declare 
  2  fhandle utl_file.file_type; 
  3  fp_buffer varchar2(4000); 
  4  begin 
  5  fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'R'); 
  6  utl_file.get_line(fhandle, fp_buffer); 
  7  dbms_output.put_line(fp_buffer); 
  8  utl_file.get_line(fhandle, fp_buffer); 
  9  dbms_output.put_line(fp_buffer); 
10  utl_file.fclose(fhandle); 
11  end; 
12  / 
PL/SQL procedure successfully completed. 

SQL> / 
PL/SQL procedure successfully completed. 

此时没有任何输出，设置serveroutput： 
					

						SQL>  set serveroutput on 
SQL> / 
test write one 
test write two 
PL/SQL procedure successfully completed. 
打印文件内容。 
					

DIRECTORY的目就在于可以让我们在Oracle中灵活地对文件系统中的文件进行操作。

ORACLE DIRECTORY目录

						Create DIRECTORY让我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性。 
					
						其语法为： 
					
						CREATE [OR REPLACE] DIRECTORY directory AS 'pathname'; 
					
						本案例具体创建如下: 
					
						create or replace directory exp_dir as '/tmp'; 
					
						目录创建以后，就可以把读写权限授予特定用户，具体语法如下: 
					
						GRANT READ[,WRITE] ON DIRECTORY directory TO username; 
					
						例如: 
					
						grant read, write on directory exp_dir to eygle; 
					
						此时用户eygle就拥有了对该目录的读写权限。

让我们看一个简单的测试:

SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file';

Directory created.

SQL> declare

2 fhandle utl_file.file_type;

3 begin

4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w');

5 utl_file.put_line(fhandle , 'eygle test write one');

6 utl_file.put_line(fhandle , 'eygle test write two');

7 utl_file.fclose(fhandle);

8 end;

9 /

PL/SQL procedure successfully completed.

SQL> !

[oracle@jumper 9.2.0]$ more /opt/oracle/utl_file/example.txt

eygle test write one

eygle test write two

[oracle@jumper 9.2.0]$

类似的我们可以通过utl_file来读取文件:

SQL> declare

2 fhandle utl_file.file_type;

3 fp_buffer varchar2(4000);

4 begin

5 fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R');

7 utl_file.get_line (fhandle , fp_buffer );

8 dbms_output.put_line(fp_buffer );

9 utl_file.get_line (fhandle , fp_buffer );

10 dbms_output.put_line(fp_buffer );

11 utl_file.fclose(fhandle);

12 end;

13 /

eygle test write one

eygle test write two

PL/SQL procedure successfully completed.

可以查询dba_directories查看所有directory.

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------

SYS UTL_FILE_DIR /opt/oracle/utl_file

SYS BDUMP_DIR /opt/oracle/admin/conner/bdump

SYS EXP_DIR /opt/oracle/utl_file

可以使用drop directory删除这些路径.

SQL> drop directory exp_dir;

Directory dropped

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------

SYS UTL_FILE_DIR /opt/oracle/utl_file

SYS BDUMP_DIR /opt/oracle/admin/conner/bdump

【DIRECTORY】普通用户创建Oracle DIRECTORY数据库对象的权限需求及探索本文以普通用户创建Oracle DIRECTORY数据库对象所需的权限入手，给大家展示一下自我提示的方法，进而探索一下DIRECTORY数据库对象的“小秘密”。 1.授予普通用户创建DIRECTORY的权限为保证普通用户具有创建Oracle数据库的directory对象，需要确切知道需要授予用户的权限是什么，我们这里需要的权限是“CREATE ANY DIRECTORY”权限。如果一时忘记需要的具体权限，可以使用如下参考类SQL语句获得提示。 sys@ora10g> select distinct privilege from dba_sys_privs where privilege like '%DIRECTORY%'; PRIVILEGE ---------------------------------------- DROP ANY DIRECTORY CREATE ANY DIRECTORY 对普通用户sec用户的授权语句如下。 sys@ora10g> grant CREATE ANY DIRECTORY to sec; Grant succeeded. 2.尝试在普通用户下创建DIRECTORY数据库对象 sec@ora10g> create directory dir_test as '/home/oracle/secooler'; Directory created. OK，此时在sec用户下创建DIRECTORY对象成功！ 3.创建 DIRECTORY 可能遇到的报错信息如若在创建DIRECTORY之前普通用户sec未获得相应权限，将会收到最为常见的“ORA-01031: insufficient privileges”错误。模拟再现一下，供大家参考。 sec@ora10g> conn / as sysdba Connected. sys@ora10g> revoke CREATE ANY DIRECTORY from sec; Revoke succeeded. sys@ora10g> conn sec/sec Connected. sec@ora10g> create directory dir_test as '/home/oracle/secooler'; create directory dir_test as '/home/oracle/secooler' * ERROR at line 1: ORA-01031: insufficient privileges 4.获得DIRECTORY创建结果信息用于查询获得DIRECTORY信息的数据库视图“仅有”两个，如下所示。 sys@ora10g> select TABLE_NAME from dict where table_name like '%DIRECTORIES'; TABLE_NAME ------------------------------ ALL_DIRECTORIES DBA_DIRECTORIES 这里之所以强调“仅有”，是与其他数据库对象视图相比较而言的。例如，用于获得同名的数据库视图一般都会有三类，如下所示。 sys@ora10g> select TABLE_NAME from dict where table_name like '%SYNONYMS'; TABLE_NAME ------------------------------ DBA_SYNONYMS USER_SYNONYMS ALL_SYNONYMS 用于查看DIRECTORY数据库对象的视图之所以没有以USER开头的是有其具体缘由的。请继续跟随我进行探索。我们使用ALL_DIRECTORIES视图查看刚刚创建成功的DIRECTORY对象。 sec@ora10g> col owner for a6 sec@ora10g> col DIRECTORY_NAME for a15 sec@ora10g> col DIRECTORY_PATH for a30 sec@ora10g> select * from all_directories where DIRECTORY_NAME = 'DIR_TEST'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ --------------- ------------------------------ SYS DIR_TEST /home/oracle/secooler 重点关注一下OWNER字段的返回结果，这里显示的内容是“SYS”，并不是我们所期待的那个创建DIRECTORY的当前用户“SEC”！这样便可以顺理成章的给出不存在以USER开头的“USER_DIRECTORIES”视图的合理解释，因为DIRECTORY数据库对象并不属于某个具体用户，都归SYS用户所有！ 5.小结在对Oracle数据库世界的探索过程中，不要放过任何蛛丝马迹，每一个细节问题都充满了乐趣，值得思考和总结。在践行的征程中我们追求的是一种甚解的态度！【DIRECTORY】确保DIRECTORY对应路径书写正确以避免遭遇ORA-29913和ORA-29400错误这里，提醒大家在创建DIRECTORY对象时，目录路径一定要书写正确，不要出现不必要的空格或者回车，否则将会遭遇ORA-29913和ORA-29400错误。 1.故障再现及分析过程 1）调整为英文显示环境 $ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 2）创建测试用户SECOOLER并授权 $ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 12 20:07:16 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@ora11g> drop user secooler cascade; User dropped. SYS@ora11g> create user secooler identified by secooler; User created. SYS@ora11g> grant dba to secooler; Grant succeeded. 2）创建DIRECTORY对象注意这里创建的是一个错误路径，因为这里在路径的结尾处出现了一个多余的回车换行。但是在查询DIR_DUMP执行路径是显示的内容并没有显示出结尾处的回车。 SECOOLER@ora11g> create directory dir_dump as 'c:\ 2 '; Directory created. SECOOLER@ora11g> col OWNER for a5 SECOOLER@ora11g> col DIRECTORY_NAME for a15 SECOOLER@ora11g> col DIRECTORY_PATH for a15 SECOOLER@ora11g> select * from dba_directories where directory_name = 'DIR_DUMP'; OWNER DIRECTORY_NAME DIRECTORY_PATH ----- --------------- --------------- SYS DIR_DUMP c:\ 3）以外部表卸载数据方式测试DIRECTORY是否可用 SECOOLER@ora11g> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; create table t1 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file c:\ \T1_6648_11956.log 可见，这里的报错与DIRECTORY创建时出现的那个回车换行有直接的关系（重点关注最后两行提示信息）。 4）与之对应的中文报错内容如下 $ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" $ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 3月 12 20:20:44 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@ora11g> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; create table t1 * 第 1 行出现错误: ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错 ORA-29400: 数据插件错误error opening file c:\ \T1_6648_4104.log 5）测试由于多余空格导致报错过程（1）重建DIRECTORY SECOOLER@ora11g> drop directory dir_dump; 目录已删除。 SECOOLER@ora11g> create directory dir_dump as 'c :\'; 目录已创建。（2）使用DIRECTORY卸载数据 SECOOLER@ora11g> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; create table t1 * 第 1 行出现错误: ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错 ORA-29400: 数据插件错误error opening file c :\T1_6648_6560.log 报错内容很显然，c与后面的冒号之间的空格是导致错误的根本原因。 2.故障处理方法问题的原因是由于创建DIRECTORY时指定的路径中出现多余的空格或者回车导致的，换一种说法便是，当我们指定的路径不存在时就会报错。我们的处理方法便是给出DIRECTORY正确的路径。 1）重建directory执行正确的路径 SECOOLER@ora11g> drop directory dir_dump; 目录已删除。 SECOOLER@ora11g> create directory dir_dump as 'c:\'; 目录已创建。 2）重新测试数据卸载 SECOOLER@ora11g> create table t1 2 organization external 3 ( type oracle_datapump 4 default directory dir_dump 5 location ('t1_part1.dat','t1_part2.dat') 6 ) 7 PARALLEL 2 8 as 9 select owner,table_name,tablespace_name from all_tables where wner='SYSTEM'; 表已创建。创建成功，表明DIRECTORY此时已经正确可用。 3.小结 ORA-29913和ORA-29400错误背后的根本原因是在创建DIRECTORY数据库对象时对应的路径不存在。在创建DIRECTORY时Oracle数据库并不会到操作系统上检验路径的存在性，只有在使用时才会校验，因此在创建DIRECTORY时谨记对应的路径的真实存在性。查看用户的目录操作权限 column grantee format a10 column grantor format a10 column dir_name format a20 column dir_path format a50 column privilege format a10 break on dir_name select d.directory_name dir_name, d.directory_path dir_path, p.privilege, p.grantee, p.grantor from dba_tab_privs p, dba_directories d where p.table_name = d.directory_name and p.grantee = upper('&user') order by d.directory_name, p.privilege /

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-12-01 09:00 ~ 2017-12-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群小麦苗的微店

.............................................................................................................................................

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

Oracle Directory（目录）介绍

[ 申请 ]友情链接：