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

sql oracle dbf
Michael picture Michael · Nov 19, 2014 · Viewed 73k times · Source

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

Cyryl1972 picture Cyryl1972 · Nov 21, 2014

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More