CODEWITHSUNDEEP
sqloracle-databasedbf
Michael
Michael

Reputation: 1237

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:

There are also several limitations:

Any help is much appreciated.

Upvotes: 12

Views: 129956

Answers (8)

user13267719
user13267719

Reputation: 11

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

Upvotes: 1

arve0
arve0

Reputation: 3647

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

Upvotes: 1

Andrei Z
Andrei Z

Reputation: 21

Try this:

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

Upvotes: 1

nekperu15739
nekperu15739

Reputation: 3698

Use this sentence with system user:

  Select * from dba_data_files ;

enter image description here

Upvotes: 13

daniele daniele
daniele daniele

Reputation: 41

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

Upvotes: 4

Cyryl1972
Cyryl1972

Reputation: 624

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.

Upvotes: 10

Mark Wagoner
Mark Wagoner

Reputation: 1769

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.

Upvotes: 1

Marco Baldelli
Marco Baldelli

Reputation: 3728

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.

Upvotes: 5

Related Questions