Search code examples
oracleclonedatabase-administration

Dynamic Instance in DBA Directories

Disclaimer: I'm not a DBA, just a DBA-wannabe.

I have created a Directory in our Production Environment:

CREATE DIRECTORY PAY_FOLDER AS '/u01/EBSPROD/apps/apps_st/appl/cust/12.0.0/per/1.0';
GRANT READ ON DIRECTORY PAY_FOLDER TO PUBLIC;

However, when our Production Environment is cloned to a Non-Prod Instance (for example EBSDEV), the instance name doesn't change and still stays as EBSPROD. I would want it to be: /u01/EBSDEV/apps/apps_st/appl/cust/12.0.0/per/1.0 when cloned over to DEV /u01/EBSDEV/apps/apps_st/appl/cust/12.0.0/per/1.0 when cloned over to TEST /u01/EBSUAT/apps/apps_st/appl/cust/12.0.0/per/1.0 when cloned over to UAT

Do I need a separate script to re-create these directories when i clone an instance?

Solution

  • Yes. The directory name is just a string; it does not know that you have embedded the instance name.

    Use

    CREATE OR REPLACE DIRECTORY directory_name AS'path_name';

    instead of a DROP-Statement followed by a CREATE-Statement. This will keep all the grants on this directory.

    It should not be too hard to create a single parameterized script that would take the DB Name or Instance name (whichever is appropriate) and build the correct directory name.