Understanding DBA Directory

 To create a directory:

CREATE OR REPLACE DIRECTORY alias AS 'pathname';

To Grant:

GRANT permission ON DIRECTORY alias TO {user | role | PUBLIC};

SQL> SELECT DIRECTORY_NAME , DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DUMP';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------
DUMP                          /home/appsolworld
$ chmod 777 /home/appsolworld
Just test run:

DECLARE
   Handle   UTL_FILE.FILE_TYPE;
BEGIN
   Handle := UTL_FILE.FOPEN ('DUMP', 'abc.txt', 'w');

   UTL_FILE.PUT ('This is the first line');
   UTL_FILE.FCLOSE (Handle);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
      RAISE;
END;
/

DBA_DIRECTORIES view which we use to check directory information. Let us describe that.

SQL> desc DBA_DIRECTORIES;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(128)
 DIRECTORY_NAME 			   NOT NULL VARCHAR2(128)
 DIRECTORY_PATH 				    VARCHAR2(4000)
 ORIGIN_CON_ID					    NUMBER

Now I am describing CDB_DIRECTORIES.

SQL> desc CDB_DIRECTORIES;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(128)
 DIRECTORY_NAME 			   NOT NULL VARCHAR2(128)
 DIRECTORY_PATH 				    VARCHAR2(4000)
 ORIGIN_CON_ID					    NUMBER
 CON_ID 					    NUMBER

We can see CDB_DIRECTORIES have the same structure as DBA_DIRECTORIES just one column is added CON_ID.

Let us explore more

Step 1: Query DBA_DIRECTORIES view from CDB$ROOT container

SQL> select * from dba_directories;

OWNER  DIRECTORY_ DIRECTORY_PATH      ORIGIN_CON_ID
----- ---------- -------------------- -------------
SYS	 ORACLE_HOME /				          1
	 E

SYS	 ORACLE_BAS /				          1
	 E

SYS	 OPATCH_LOG /u01/oracle/product/	   1
	 _DIR	    12.1.0/db_1/QOpatch

SYS	 OPATCH_SCR /u01/oracle/product/	  1
	 IPT_DIR    12.1.0/db_1/QOpatch

OWNER DIRECTORY_ DIRECTORY_PATH	      ORIGIN_CON_ID
----- ---------- -------------------- -------------

SYS	 OPATCH_INS /u01/oracle/product/	  1
	 T_DIR	     12.1.0/db_1/OPatch

SYS	 DATA_PUMP_ /u01/oracle/admin/my	  1
	 DIR	    cdb/dpdump/

SYS	 XSDDIR     /u01/oracle/product/	  1
		       12.1.0/db_1/rdbms/xm
		       l/schema



Step 2 : Query DBA_DIRECTORIES from pluggable database .

SQL> select * from dba_directories;

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID
-------- ---------- -------------------- -------------
SYS	 DATA_PUMP_ /appsolworld/admin/my	     1
	 DIR	    cdb/dpdump/

SYS	 OPATCH_INS /appsolworld/product/	     1
	 T_DIR	    12.1.0/db_1/OPatch

SYS	 OPATCH_SCR /appsolworld/product/	     1
IPT_DIR 12.1.0/db_1/QOpatch SYS OPATCH_LOG /appsolworld/product/ 1
_DIR 12.1.0/db_1/QOpatch OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID -------- ---------- -------------------- ------------- SYS ORACLE_BAS / 1 E SYS ORACLE_HOM / 1 E SYS DEM /appsolworld/ 3
SYS XSDDIR /appsolworld/product/ 3
12.1.0/db_1/rdbms/xm l/schema OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID -------- ---------- -------------------- -------------

Step 3 : Query CDB_DIRECTORIES from CDB$ROOT container :

SQL> select * from CDB_DIRECTORIES;

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------
SYS	 DATA_PUMP_ /appsolworld/admin/my	     1		3
DIR cdb/dpdump/ SYS OPATCH_INS /appsolworld/product/ 1 3
T_DIR 12.1.0/db_1/OPatch SYS OPATCH_SCR /appsolworld/product/ 1 3
IPT_DIR 12.1.0/db_1/QOpatch SYS OPATCH_LOG /appsolworld/product/ 1 3
_DIR 12.1.0/db_1/QOpatch OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID CON_ID -------- ---------- -------------------- ------------- ---------- SYS ORACLE_BAS / 1 3 E SYS ORACLE_HOM / 1 3 E SYS DEM /appsolworld 3 3
SYS XSDDIR /appsolworld/product/ 3 3
12.1.0/db_1/rdbms/xm l/schema OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID CON_ID -------- ---------- -------------------- ------------- ---------- SYS XSDDIR /appsolworld/product/ 1 1
12.1.0/db_1/rdbms/xm l/schema SYS DATA_PUMP_ /appsolworld/admin/my 1 1
DIR cdb/dpdump/ SYS OPATCH_INS /appsolworld/product/ 1 1
T_DIR 12.1.0/db_1/OPatch OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID CON_ID -------- ---------- -------------------- ------------- ---------- SYS OPATCH_SCR /appsolworld/product/ 1 1
IPT_DIR 12.1.0/db_1/QOpatch SYS OPATCH_LOG /appsolworld/product/ 1 1
_DIR 12.1.0/db_1/QOpatch SYS ORACLE_BAS / 1 1 E SYS ORACLE_HOM / 1 1 E OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID CON_ID -------- ---------- -------------------- ------------- ----------

Step 4 : Query CDB_DIRECTORIES from pluggable database PDB1:

SQL> select * from CDB_DIRECTORIES;

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------
SYS	 DEM	    /appsolworld			     3		3
SYS XSDDIR /appsolworld/product/ 3 3
12.1.0/db_1/rdbms/xm l/schema SYS DATA_PUMP_ /appsolworld/admin/my 1 3
DIR cdb/dpdump/ SYS OPATCH_INS /appsolworld/product/ 1 3
T_DIR 12.1.0/db_1/OPatch OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID CON_ID -------- ---------- -------------------- ------------- ---------- SYS OPATCH_SCR /appsolworld/product/ 1 3
IPT_DIR 12.1.0/db_1/QOpatch SYS OPATCH_LOG /appsolworld/product/ 1 3
_DIR 12.1.0/db_1/QOpatch SYS ORACLE_BAS / 1 3 E SYS ORACLE_HOM / 1 3 E OWNER DIRECTORY_ DIRECTORY_PATH ORIGIN_CON_ID CON_ID -------- ---------- -------------------- ------------- ---------- 8 rows selected. SQL>

We have queries DBA_DIRECTORIES and CDB_DIRECTORIES from root container and pluggable database container.

In step 1 we have queried DBA_DIRECTORIES from root container which shows directories associated with Root container.

In step 2 we have queries DBA_DIRECTORIES from mypdb1 which shows all directories associated with Pluggable container mypdb1.

In step 3 we have queries CDB_DIRECTORIES from root container which shows directories of root container as well as all directories of all pluggable database we can see associated CON_ID in CDB_DIRECTORIES.

In step 4 we have queried CDB_DIRECTORIES from mypdb1 which shows same output as DBA_DIRECTORIES just one column more which shows CON_ID of PDB1 that is 3.

Comments

Popular posts from this blog

How to drop index and before dropping it how to get the DDL.

PRVG-11250 : The check "RPM Package Manager database" was not performed because

How to troubleshoot Long Running Concurrent Request in EBS 12.2

ORA-00257:archiver error, connect internal only until freed

Verifying Daemon “Avahi-Daemon” Not Configured And Running …FAILED (PRVG-1360)

Linux OL7/RHEL7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

CPU Patch Analysis

How to write to a CSV file using Oracle SQL*Plus

SKIP DNS RESLOV.CONF CHECK DURING RAC CONFIGURATION

How to fix Oracle SQL Developer connection issue "Got minus one from a read call"