Michael Michael - 10 days ago 4
SQL Question

How to find the default location in which Oracle DBF files are created?

During the creation of a new Tablespace in Oracle Database, the user has to enter the DBF file name that he (or she) want to use. The DBF file is then created in a specific location.

The user may also specify a path in which the DBF file should be created.

I need to find a way to get the default location of the DBF file.

I know how to do it in MS Sql by using a SQL query:

select substring(physical_name, 1, charindex(N'master.mdf', lower(physical_name)) - 1) from master.sys.master_files where database_id = 1 and file_id = 1;


But I have no idea about how to do it in Oracle. I've tried several things:


  • Ran a query on
    all_directories
    - didn't find any information there

  • Looked at the
    v$datafile
    view - realized that this view and the others are accesible to database administrators only



There are also several limitations:


  • The Oracle Database may be installed on another machine with a different operating system.

  • My application may connect to the database with a user who is not an
    admin.

  • It should be done preferably with a SQL query.



Any help is much appreciated.

Answer

DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles (see its entry in the Database Reference).

You can retrieve its value with the following SQL query:

select value from v$parameter where name = 'db_create_file_dest'

To access the v$parameter view a user needs at least the SELECT_CATALOG_ROLE role.