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

From: Herring, David <HerringD_at_DNB.com>
Date: Sat, 27 Jun 2015 10:11:48 -0500
Message-ID: <AD8FE6616C097545A4C9A8B0792909AC58E30DD1DA_at_DNBEXCH01.dnbint.net>



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_at_<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||'-'||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
i0zX+n{+i^ Received on Sat Jun 27 2015 - 17:11:48 CEST

Original text of this message