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

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.

Use this sentence with system user:

  Select * from dba_data_files ;

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.