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

Question

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 1

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.

Answer 2

There is no true default location for a data file in Oracle, the closest thing would be a directory under ORACLE_HOME. If I recall (I don't have access to an Oracle DB at the moment to verify this) the directory structure varies slightly based on the OS and version.

The reason there is no default is because you will typically want to spread your data across physical drives to avoid contention. You will often have some tables that get hit almost all of the time while others are access much less frequently. At the least you want to keep your temporary and redo/undo separate from your data.

Answer 3

To determine the default datafiles location, I am using the following query:

SELECT DISTINCT SUBSTR (file_name,
                        1,
                        INSTR (file_name,
                               '/',
                               -1,
                               1))
  FROM DBA_DATA_FILES
 WHERE tablespace_name = 'SYSTEM'

It works for ME because all our datafiles are installed in a the same directory.

Answer 4

select DISTINCT SUBSTR (file_name,1, instr(file_name, 'SYSTEM') - 1) FROM DBA_DATA_FILES WHERE tablespace_name = 'SYSTEM';

Answer 5

Try this:

select substr(name, 1, instr(name, 'USER') - 1) prefix from v$datafile where name like '%USER%'; 

Answer 6

You can view the default location for tablespaces with

show parameter DB_CREATE_FILE_DEST;

You can change the parameter with

alter system set DB_CREATE_FILE_DEST='/ORCL/u02/app/oracle/oradata/ORCL';

Answer 7

Use this sentence with system user:

  Select * from dba_data_files ;

Answer 8

As default the .dbf files are stored under something like:

/u01/app/oracle/product/__VERSION__/__DB_NAME__/dbs

where __VERSION__ may be something like "11.0.1.1" and __DB_NAME__ is your DB