Oracle Directory（目录）介绍

2022-11-20 09:08:15

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');
  6 
  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
/

码农公寓

Oracle Directory（目录）介绍

相关文章