Skip to content
Oracle DBA Scripts & Database Utilities

Directory Objects Analysis (ddir.sql)

What This Script Does

Section titled “What This Script Does”

This script lists all Oracle directory objects in the database, showing their owners and corresponding file system paths.

The Script

Section titled “The Script”
rem  ddir.sql
rem
set linesize 150
rem
ttitle 'Database Directories'
rem


col OWNER           format a15  heading 'Owner'
col DIRECTORY_NAME  format a20  heading 'Dir_NM'
col DIRECTORY_PATH  format a100 heading 'Dir_Path'


select OWNER
      ,DIRECTORY_NAME
      ,DIRECTORY_PATH
  from dba_directories
 where upper(nvl(DIRECTORY_NAME,'null')) like upper(nvl('&directory_nm', '%'))
 order by owner, directory_name
;

Usage

Section titled “Usage”
-- Basic usage
@ddir.sql


-- When prompted, enter:
-- directory_nm: ** - Directory name pattern to filter (use % for all)

Parameters

Section titled “Parameters”

The script prompts for:

  • &directory_nm - ** - Directory name pattern to filter (use % for all)

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_DIRECTORIES

Sample Output

Section titled “Sample Output”
Database Directories


Owner           Dir_NM               Dir_Path
--------------- -------------------- ----------------------------------------------------------------------------------------------------
SYS             DATA_PUMP_DIR        /u01/app/oracle/admin/ORCL/dpdump/
SYS             ORACLE_BASE          /u01/app/oracle
SYS             ORACLE_HOME          /u01/app/oracle/product/19.0.0/dbhome_1
SYS             OPATCH_INST_DIR      /u01/app/oracle/product/19.0.0/dbhome_1/OPatch
SYS             OPATCH_LOG_DIR       /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log
SYS             OPATCH_SCRIPT_DIR    /u01/app/oracle/product/19.0.0/dbhome_1/QOpatch
HR              EXT_TAB_DIR          /u01/app/oracle/external_tables/hr

Key Output Columns

Section titled “Key Output Columns”
  • Owner - Schema that owns the directory object
  • Dir_NM - Directory object name
  • Dir_Path - Full file system path

Common Use Cases

Section titled “Common Use Cases”

Find Data Pump Directories

-- Locate Data Pump directories
@ddir.sql
-- Enter: DATA_PUMP%

List All Directories

-- View all directory objects
@ddir.sql
-- Enter: %
Section titled “Related Scripts”