RE: Where, oh where, did my directory go?

From: Sweetser, Joe <JSweetser_at_icat.com>
Date: Tue, 14 Jul 2015 13:51:32 +0000
Message-ID: <D18D6513433DF04394041EA42B53E91C9D21F6D1_at_ICATMAIL1.ICAT.com>

(revisiting/update)

I have dug a bit deeper with no resolution (will probably open an SR at some point) and just wanted to share what I found. The reason I found myself in this situation is that I have a script that queries dba_directories to find the path for a given directory_name. I then use that path later in the script. It's not critical and I can work around it but that is how I discovered this strange behavior in one of my 12c databases. Anyway, below is just a little more info.

The view dba_directories is created with the below SQL:

select OWNER, DIRECTORY_NAME, DIRECTORY_PATH, ORIGIN_CON_ID from INT$DBA_DIRECTORIES;

int$dba_directories is also a view. And this is the underlying SQL:

select u.name, o.name, o.obj#, o.type#, d.os_path,

       decode(bitand(o.flags, 196608), 65536, 1, 131072, 1, 0),
       to_number(sys_context('USERENV', 'CON_ID'))

from sys.user$ u, sys.obj$ o, sys.x$dir d where u.user# = o.owner#
and o.obj# = d.obj#;

When I execute that statement (as shown below), I do see the directory.

SYS_at_pdb2> create directory joe_dir as '/u04/oradata/joe';

Directory created.

SYS_at_pdb2> select * from dba_directories where directory_name = 'JOE_DIR';

no rows selected

SYS_at_pdb2> set echo on
SYS_at_pdb2> @dircheck
SYS_at_pdb2> select u.name ||' - '|| o.name ||' - '|| o.obj# ||' - '|| o.type# ||' - '|| d.os_path ||' - '||
  2         decode(bitand(o.flags, 196608), 65536, 1, 131072, 1, 0) ||' - '||
  3         to_number(sys_context('USERENV', 'CON_ID'))

4 from sys.user$ u, sys.obj$ o, sys.x$dir d

  5  where u.user# = o.owner#
  6    and o.obj# = d.obj#
  7    and o.name = 'JOE_DIR'

8 /
U.NAME||'-'||O.NAME||'-'||O.OBJ#||'-'||O.TYPE#||'-'||D.OS_PATH||'-'||DECODE(BITAND(O.FLAGS,196608),65536,1,131072,1,0)||'-'||TO_NUMB

SYS - JOE_DIR - 123755 - 23 - /u04/oradata/joe - 0 - 3

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sweetser, Joe Sent: Sunday, June 28, 2015 7:20 AM
To: Herring, David; Sayan Malakshinov
Cc: rjoralist3_at_society.servebeer.com; oracle-l_at_freelists.org Subject: RE: Where, oh where, did my directory go?

I am not sure what the issue is, frankly. :-\

-joe

SQL> create directory joe_dir as '/u04/oradata/joe';

Directory created.

SQL> select directory_name from dba_directories where SQL> upper(directory_name) like '%JOE%';

no rows selected

SQL> select owner ||' - '|| object_name ||' - '|| object_type ||' - '|| SQL> created from dba_objects where object_name like '%JOE%';

OWNER||'-'||OBJECT_NAME||'-'||OBJECT_TYPE||'-'||CREATED
--------------------------------------------------------------------------------
SYS - JOE_DIR - DIRECTORY - 28-JUN-15

SQL> drop directory joe_dir;

Directory dropped.

SQL> -----Original Message-----
From: Herring, David [mailto:HerringD_at_DNB.com] Sent: Saturday, June 27, 2015 9:12 AM
To: Sweetser, Joe; Sayan Malakshinov
Cc: rjoralist3_at_society.servebeer.com; oracle-l_at_freelists.org Subject: RE: Where, oh where, did my directory go?

Are you sure it's a privilege/ownership issue and not a case issue? Have you tried?:

select directory_name from dba_directories where upper(directory_name) like '%JOE%';

I have idea why this would happen but just trying to eliminate possibilities.

Regards,

Dave

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sweetser, Joe Sent: Friday, June 26, 2015 4:41 PM
To: Sayan Malakshinov
Cc: rjoralist3_at_society.servebeer.com; oracle-l_at_freelists.org Subject: RE: Where, oh where, did my directory go?

Thanks for reply. I have not traced anything yet though that is a good idea.

I am connecting directly as user@<service_name> and don’t believe there is anything special happening.

Query results below.

-joe

15:36:10 pdb2> select owner ||' - '|| directory_name ||' - '|| directory_path ||' - '|| origin_con_id from dba_directories;

OWNER||'-'||DIRECTORY_NAME||'-'||DIRECTORY_PATH||'-'||ORIGIN_CON_ID

SYS - ORACLE_HOME - / - 1
SYS - ORACLE_BASE - / - 1
SYS - OPATCH_LOG_DIR - /opt/app/oracle/product/12.1.0.2/QOpatch - 1 SYS - OPATCH_SCRIPT_DIR - /opt/app/oracle/product/12.1.0.2/QOpatch - 1 SYS - OPATCH_INST_DIR - /opt/app/oracle/product/12.1.0.2/OPatch - 1 SYS - DATA_PUMP_DIR - /opt/app/oracle/admin/iap9/dpdump/ - 1

6 rows selected.

15:37:12 pdb2> 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

15:37:20 pdb2> select owner ||' - '|| directory_name ||' - '|| directory_path ||' - '|| origin_con_id || ' - '|| con_id from cdb_directories;

OWNER||'-'||DIRECTORY_NAME||'-'||DIRECTORY_PATH||'-'||ORIGIN_CON_ID||'-' OWNER||||CON_ID

SYS - ORACLE_HOME - / - 1 - 3
SYS - ORACLE_BASE - / - 1 - 3
SYS - OPATCH_LOG_DIR - /opt/app/oracle/product/12.1.0.2/QOpatch - 1 - 3 SYS - OPATCH_SCRIPT_DIR - /opt/app/oracle/product/12.1.0.2/QOpatch - 1 - 3 SYS - OPATCH_INST_DIR - /opt/app/oracle/product/12.1.0.2/OPatch - 1 - 3 SYS - DATA_PUMP_DIR - /opt/app/oracle/admin/iap9/dpdump/ - 1 - 3

6 rows selected.

15:37:46 pdb2> select owner ||' - '|| object_name ||' - '|| object_type ||' - '|| created from dba_objects where object_name like '%JOE%';

OWNER||'-'||OBJECT_NAME||'-'||OBJECT_TYPE||'-'||CREATED
--------------------------------------------------------------------------------
SYS - JOE_DIR - DIRECTORY - 26-JUN-15

15:37:59 pdb2>

From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com] Sent: Friday, June 26, 2015 3:33 PM
To: Sweetser, Joe
Cc: rjoralist3_at_society.servebeer.com; oracle-l_at_freelists.org Subject: Re: Where, oh where, did my directory go?

Have you tried to trace it with 10046 and with 10053? Are you sure that you hadn't executed "alter system set current_user=SYSTEM" or maybe something like connect through proxy_user? And could you show also "select * from dba_directories" and "select * from cdb_directories" without predicates?

On Sat, Jun 27, 2015 at 12:19 AM, Sweetser, Joe <JSweetser_at_icat.com> wrote: Me too! :)

FWIW, I also see the same behavior when connected as SYS.

-joe

15:15:02 pdb2> drop directory joe_dir;

Directory dropped.

15:15:21 pdb2> show user
USER is "SYS"
15:15:26 pdb2> create directory joe_dir as '/u04/oradata/backup/joe';

Directory created.

15:15:32 pdb2> select directory_name from dba_directories where directory_name like '%JOE%';

no rows selected

15:15:38 pdb2> select object_name ||' - '|| object_type ||' - '|| created from dba_objects where object_name like '%JOE%';

OBJECT_NAME||'-'||OBJECT_TYPE||'-'||CREATED

JOE_DIR - DIRECTORY - 26-JUN-15
15:16:09 pdb2>

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Friday, June 26, 2015 3:09 PM
To: oracle-l_at_freelists.org
Subject: Re: Where, oh where, did my directory go?

Joe writes:

> Without going into the details, I came across something strange in my
> environments today. Basically, I have a 12c pluggable database where
> a user-created directory does not show up in dba_directories. I have
> another one where it works as expected. These are simply the single,
> pluggable database that can be created without licensing the feature.
> Utilities like data pump work as expected in both databases. I just
> don't see an entry in dba_directories. There are undoubtedly
> differences between the servers, notably the version of Linux. But I don't know if that's any issue.

I see you're logged in as SYSTEM and the directory is owned by SYS. I'd have thought that the SELECT ANY DICTIONARY of the DBA role would allow that to be viewed, but I only have 11.2 here.

Just a thought...

Rich

--
http://www.freelists.org/webpage/oracle-l Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you. --
http://www.freelists.org/webpage/oracle-l

--
Best regards,
Sayan Malakshinov
Oracle ACE Associate
Lead performance tuning engineer
PSBank
http://orasql.org
i 0 zX + n { +i ^
��i��0��zX��+��n��{�+i�^ Received on Tue Jul 14 2015 - 15:51:32 CEST

This message: [ Message body ]
Next message: Ruel, Chris: "RE: How to track all the users/machines, which connect an Oracle database"
Previous message: Sheehan, Jeremy: "RE: How to track all the users/machines, which connect an Oracle database"
Next in thread: Hans Forbrich: "Re: Where, oh where, did my directory go?"
Reply: Hans Forbrich: "Re: Where, oh where, did my directory go?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message