Solved

UTL_FILE file path problem

Posted on 2003-11-03
5
1,941 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
[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
  • Learn & ask questions
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 48

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 276
PL SQL Developer 7 71
create a nested synonym 4 40
Oracle programming for starter 14 30
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

749 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