Home » RDBMS Server » Server Administration » UTL_FILE_DIR in 10g
UTL_FILE_DIR in 10g [message #167078] Tue, 11 April 2006 04:24 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Hi,

I tried setting the UTL_FILE_DIR in init.ora file under pfile directory under 10g oracle.

But when trying with the utl_file.fopen it is giving invalid directory path, while checking the value for utl_file_dir in v$parameter it is giving null value.


Any idea how to set it
Thanks and Regards,

nirmal

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167118 is a reply to message #167078] Tue, 11 April 2006 07:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
UTL_FILE_DIR is depreciated long back.
You are supposed to use CREATE DIRECTORY.
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167119 is a reply to message #167078] Tue, 11 April 2006 07:47 Go to previous messageGo to next message
Frank Naude
Messages: 4581
Registered: April 1998
Senior Member
Are you using a PFILE or SPFILE? Try:

SQL> alter system set UTL_FILE_DIR = '/tmp' scope=spfile;


As suggested above, you should rather use database directories.

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167130 is a reply to message #167078] Tue, 11 April 2006 08:14 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member
Hi Nirmalnarayan,
You have to create a pfile from the spfile if u r using Oracle 10g.
First shutdown the Database.Then create a pfile by giving the command
CREATE PFILE='PFILE-NAME' FROM SPFILE='SPFILE-NAME';
Then change the parameter of UTL_FILE_DIR and set the directory path.
Now create the SPFILE from the PFILE by giving the command
CREATE SPFILE='SPFILE-NAME' FROM PFILE='PFILE-NAME';

Now startup the Database.
IF u now query ur v$Parameter view then u will find that UTL_FILE_DIR had been set to ur specified path.
Now u will have no trouble to run utl_file.fopen.
Try it out.

Thanks & Regards,
Sanka

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167145 is a reply to message #167130] Tue, 11 April 2006 09:37 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
@sanka yanka,
did you even read Frank's reply. That is how you alter an spfile parameter, NOT your method.

Regards
Jim

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167228 is a reply to message #167145] Wed, 12 April 2006 00:06 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member
hello JSI2001,
Yea, I read Frank's Reply. But from that method which I wrote also works fine. you can also do it by this method.
Regards,
Sanka

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #167259 is a reply to message #167228] Wed, 12 April 2006 03:22 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

You have to create a pfile from the spfile if u r using Oracle 10g
No you don't.

Quote:

First shutdown the Database

Shutdown 1.
Quote:

Then create a pfile by giving the command
CREATE PFILE='PFILE-NAME' FROM SPFILE='SPFILE-NAME';

And how do you do that? The database is shut down.

Quote:

Now create the SPFILE from the PFILE by giving the command


And how do you do that? The database is shut down.


Quote:

Now startup the Database.


This entire method would not work at all. Have you followed your own steps?

If you insist on using this method (which is far more convoluted than is necessary then it would be:

1. Create pfile from spfile.
2. Shutdown the db.
3. Edit the pfile to include the utl_file_dir parameter
4. startup the database to point at the pfile:
startup pfile= pfile loc
5. create spfile from pfile.
6. Shutdown the database
7. startup the database.


Or you could issue

1. alter system set UTL_FILE_DIR = '/tmp' scope=spfile;
2. shutdown immediate
3. startup.


I know which one is the only sensible option.

Jim

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #489194 is a reply to message #167259] Thu, 13 January 2011 13:50 Go to previous messageGo to next message
jaimitop
Messages: 2
Registered: January 2011
Junior Member
YOUR procedure does not work Mad : database says that utl_file_dir is not a modifiable parameter when i issue "alter system.....".

PFILE/SPFILE procedure does work Surprised ; only minor adjustments:

1. create pfile='pfile_file' from spfile;
2. modifiy pfile_file
3. create spfile='spfile_file' from pfile='pfile_file';
4. shutdown database
5. replace default spfile (%oracle_home%\database\spfileDBNAME.ora) with spfile_file
6. startup database
7. show parametrer utl_file_dir is ok.

Bye

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #489202 is a reply to message #489194] Thu, 13 January 2011 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
NEVER ever use UTL_FILE_DIR in the current releases.
This is a very bad practice and a BIG security hole.

Regards
Michel

Report message to a moderator

Re: UTL_FILE_DIR in 10g [message #489329 is a reply to message #489202] Fri, 14 January 2011 09:16 Go to previous message
jaimitop
Messages: 2
Registered: January 2011
Junior Member
i know it's not intended to be used any more ...but... it's a legacy app compatibility issue... Confused

Report message to a moderator

Previous Topic: >startup open after shutdown throws ORA-12514 error
Next Topic: database10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 08:42:27 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.