Home » RDBMS Server » Server Administration » Get Default Datafile directory (Oracle 9i, XP)
Get Default Datafile directory [message #348048] Mon, 15 September 2008 08:43 Go to next message
Lewis99
Messages: 4
Registered: September 2008
Junior Member
Is there a way to get the default datafile directory from the dictionary views in Oracle? I can't find it anywhere in the Parameters table.

E.g. create tablespace XXXXX datafile 'XXXXX.dbf'

If you create a datafile in the manner shown above, Oracle puts it in the ..\server\database folder, wherever that may be. How can I get this from the database or perhaps the registry without having to hardcode part of the path?
Re: Get Default Datafile directory [message #348059 is a reply to message #348048] Mon, 15 September 2008 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
show parameter db_create_file_dest (or query v$system_parameter).
If not set it is in %ORACLE_HOME%\database and it depends of the instance and so is not a database parameter.

Regards
Michel


Regards
Michel
Re: Get Default Datafile directory [message #348072 is a reply to message #348059] Mon, 15 September 2008 09:41 Go to previous messageGo to next message
Lewis99
Messages: 4
Registered: September 2008
Junior Member
Thanks Michel.

So I take it that if db_create_file_dest is not set then there is no way that anybody can change the default %ORACLE_HOME%\database folder (i.e. this is hard-coded into the app.). I need to know that this is 100% safe as I intend to delete a datafile from there programatically (from an installer), if I can't find a given database and user in the db.

Thanks very much for your help.
Re: Get Default Datafile directory [message #348074 is a reply to message #348048] Mon, 15 September 2008 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You can always provide a fully qualifier path name to place the file anywhere you desire.
Re: Get Default Datafile directory [message #348081 is a reply to message #348074] Mon, 15 September 2008 10:17 Go to previous messageGo to next message
Lewis99
Messages: 4
Registered: September 2008
Junior Member
That is true, but in this case the installer will always create the tablespace without a fully qualified path. As long as I can guarantee that the subfolder (\database) will always be the same I can use the path to check for the existance of the file and remove it (as long as it has been dropped from the db).
Re: Get Default Datafile directory [message #348082 is a reply to message #348081] Mon, 15 September 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but in this case the installer will always create the tablespace without a fully qualified path.

This is wrong, if you give a fully qualified name then the datafile is fully qualified in the dictionary.

Quote:
As long as I can guarantee that the subfolder (\database) will always be the same

You can't.

Regards
Michel
Re: Get Default Datafile directory [message #348083 is a reply to message #348082] Mon, 15 September 2008 10:27 Go to previous messageGo to next message
Lewis99
Messages: 4
Registered: September 2008
Junior Member
OK, thanks guys. I won't try to deal with the datafile that is created - I'll leave that as a manual step outside the installer.

Many thanks for your help.
Re: Get Default Datafile directory [message #348093 is a reply to message #348083] Mon, 15 September 2008 10:47 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can rename the file that have already been created to add the directory.

ALTER DATABASE RENAME FILE

Regards
Michel

[Updated on: Mon, 15 September 2008 10:51]

Report message to a moderator

Previous Topic: Got internal error Exception caught in pfrrun() while running PLSQL
Next Topic: summertime
Goto Forum:
  


Current Time: Tue Dec 06 12:00:38 CST 2016

Total time taken to generate the page: 0.15361 seconds