How to find available directory objects on Oracle 11g system?

user272735 picture user272735 · Jun 14, 2011 · Viewed 133.1k times · Source

I assume this information is available in Oracle metadata tables, but where exactly ?

I need the directory object for BFILENAME as in this answer: Using PL/SQL how do you I get a file's contents in to a blob?

I tried:

select * from all_objects where object_type ='DIRECTORY';

OWNER                  OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME          OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED         LAST_DDL_TIME   TIMESTAMP       STATUS  T G S
------------------- ------------------- ------------------- ------- - - -
 NAMESPACE EDITION_NAME
---------- ------------------------------
SYS                ORACLE_OCM_CONFIG_DIR
                    12689        DIRECTORY
2010-03-30 10:16:30 2011-01-10 12:49:39 2011-01-10:12:49:39 VALID   N N N
     9

SYS                DATA_PUMP_DIR
                    12764        DIRECTORY
2010-03-30 10:16:43 2011-01-10 12:49:38 2011-01-10:12:49:38 VALID   N N N
     9

SYS                XMLDIR
                    57134        DIRECTORY
2010-03-30 10:29:37 2010-03-30 10:29:37 2010-03-30:10:29:37 VALID   N N N
     9

But based on the output timestamps these are not the dirs created for me. This also doesn't show what is the real OS filepath for the object.

Answer

Justin Cave picture Justin Cave · Jun 14, 2011

The ALL_DIRECTORIES data dictionary view will have information about all the directories that you have access to. That includes the operating system path

SELECT owner, directory_name, directory_path
  FROM all_directories