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.

Stalk

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.

Springlet

Thanks. I've tried that, but the value in my test database is empty. – Stalk

@Michael: if it's empty then there is no default directory. The manual doesn't really say what happens then, only "The filename can include a path prefix. If you do not specify such a path prefix, then the database adds the path prefix for the default storage location, which is platform dependent" – Feathering

@a_horse_with_no_name: Under the CREATE TABLESPACE section in the manual, it is said: "If the DB_CREATE_FILE_DEST initialization parameter is not specified, then the statement creating the data file fails.". Strangely, when I execute the CREATE TABLESPACE command, everything works although the parameter has no value. – Stalk

@Michael: interesting, I didn't see that. My quote is also from the SQL reference for CREATE TABLESPACE: docs.oracle.com/cd/E11882_01/server.112/e41084/… – Feathering

Hi. I wanted to know the location of all oracle database xe related files. I am making a centos docker container and want to mount an external volume to secure data and other files – Jun

Answer 2

Use this sentence with system user:

  Select * from dba_data_files ;

Catricecatrina

Unfortunately, as I've stated in my question, the application that was initiating the query had no special permissions. – Stalk

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.

Hanker

Answer 4

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

Helprin

I did not find my files here. Instead, they were at the location given by @Hanker and nekperu15739. – Itinerary

Answer 5

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.

Gamopetalous

Thanks for the answer. But, if I run: create tablespace test datafile 'test.dbf' size 1M; a DBF file will be created somewhere. How does Oracle know where to put this file? – Stalk

@Stalk - in that case, the datafile is created unsing the default location given by the db_create_file_dest parameter value. – Hanker

Answer 6

Try this:

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

Injudicious

As I've mentioned in my question, using the dataFile view was not an option because it was only accessible to the database administrators. – Stalk

Answer 7

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';

Nannie

Answer 8

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

Genteelism

Could you expand on your answer, please? This will help future searchers understand why, and not just the answer to a specific question. – Showing