Solved

UTL_FILE file path problem

Posted on 2003-11-03
5
1,937 Views
Last Modified: 2007-12-19
My questions are:

1)What are the file path restrictions for the UTL_FILE package?
2)I know that I have to have the value UTIL_FILE_DIR set in INIT.ORA file on the server.
Can someone give me an example as to how it should look like?
3)What is the role of V$PARAMETER?
4)What if I don't have access to INIT.ORA? Restarting the database is simply not an option. Is
there a way around it?
5)What are the directories to which I can read/write if I don't have access to INIT.ORA?

Thanks.
0
Comment
Question by:mfarid2
5 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9674896
1). any directory (file path) is fine as long as the oracle admin user account have the full read/write access to it.
2). it will be like this format :
 UTL_FILE_DIR = g:\oracle\admin\ora81\util  (in windows)
UTL_FILE_DIR = /u01/opt/oracle/admin/util (in unix)

3). take a look at the definition of v$parameter view, you can derive its role:
SQL> desc v$parameter

 Name                                                                                                      Null?    Type
----------------------------------------------------------------------------------------------------------------- -------- -------------
NUM                                                                                                                NUMBER
NAME                                                                                                               VARCHAR2(64)
TYPE                                                                                                               NUMBER
VALUE                                                                                                              VARCHAR2(512)
ISDEFAULT                                                                                                          VARCHAR2(9)
ISSES_MODIFIABLE                                                                                                   VARCHAR2(5)
ISSYS_MODIFIABLE                                                                                                   VARCHAR2(9)
ISMODIFIED                                                                                                         VARCHAR2(10)
ISADJUSTED                                                                                                         VARCHAR2(5)
DESCRIPTION                                                                                                        VARCHAR2(64)
UPDATE_COMMENT                                                                                                     VARCHAR2(255)


Among its columns , ISSES_MODIFIABLE and ISSYS_MODIFIABLE are two most useful ones, it can tell you which parameters in INIT<SID>.ora can be dynamically changed either in session level or system level.
4). NO, you have to bounce the database to make it take effect.
5). None in PL/SQL.
0
 
LVL 4

Expert Comment

by:mikejrobison
ID: 9675877
I'm not sure about 9i, but in 8i, you will have to bounce the database to pick up the change for the utl_file
0
 
LVL 47

Expert Comment

by:schwertner
ID: 9676814
In 9i:

ALTER SYSTEM SET utl_file= directory SCOPE=x

wher x is MEMORY, BOTH, SPFILE
MEMORY - changes for this running instance
BOTH - changes registered in SPFILE, but  not in the running instance
BOTH - both above

You do not need to bounce the instance.
0
 

Author Comment

by:mfarid2
ID: 9686897
seazodiac, so let me get this straight. We have Oracle on an Unix server. We can read and write from any location as long as that location is specified in the UTIL_FILE_DIR parameter in Init.Ora file. Correct?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 9687266
No, that's not true.
In Unix, for example, the user account who create the oracle databases must have read/write to the directory defined in UTL_FILE_DIR ( NOT UTIL_FILE_DIR).

so for example: the user named "oracle" created the Oracle databse, if you want to create a directory,
/tmp/util to store those files. then you need to log in as ROOT , to assign user "oracle" the read/write permission on this folder .
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Space Delimited Sql File 4 70
Oracle RMAN Database Restore 5 32
query returning everything 11 68
EXECUTE IMMEDIATE 5 36
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now