CODEWITHSUNDEEP
oracle-databaseplsql
Bartb
Bartb

Reputation: 65

Oracle: Where do I keep the config settings?

I have three Oracle 11g databases: develop, test and production. Suppose the logging directory is different on the three systems:

dev  => \var\log\oracle\
test => e:\logging\
prod => g:\logs\logging\

I'm using these directories with utl_file.

Where do I keep these settings? Is there some kind of 'ora_config.ini' where I store these values and read them easily with PL/SQL?

Upvotes: 2

Views: 253

Answers (1)

Colin 't Hart
Colin 't Hart

Reputation: 7729

Since at least Oracle 9i, Oracle has a directory object. See http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5007.htm

You should create a directory object with the same name on each of your systems, and refer to that directory by name in your calls to utl_file.

For example, on "dev":

create directory logging_dir as '/var/log/oracle';

On "test":

create directory logging_dir as 'e:\logging';

On "prod":

create directory logging_dir as 'g:\logs\logging';

And your PL/SQL code is identical in all environments, eg:

f := utl_file.fopen('LOGGING_DIR', 'filename.log', 'W');

The DBA should create the directory objects and grant permissions to the database users or roles needing to use them.

Don't grant system privilege to create directory objects to just any database users as this is creating security holes by allowing Oracle to read or possibly write anywhere on the filesystem as the OS user "oracle".

Upvotes: 2

Related Questions