Solved

# UTL_FILE file path problem

Posted on 2003-11-03
1,944 Views
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?

Thanks.
0
Question by:mfarid2
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Expert Comment

ID: 9674896
2). it will be like this format :

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)
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

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 48

Expert Comment

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

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
###### Suggested Courses
Course of the Month9 days, 11 hours left to enroll