Skip to Main Content
  • Questions

  • about directory location of oracle database files,has it haven one parameter?

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Zhao.

Asked: June 07, 2017 - 6:01 am UTC

Last updated: June 09, 2017 - 2:17 am UTC

Version: 11.2.0.4.0

Viewed 50K+ times! This question is

You Asked

SYS@orcl28> col banner for a90
SYS@orcl28> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SYS@orcl28> set lines 200 pages 200
SYS@orcl28> col name for a50
SYS@orcl28> select name from v$datafile;

NAME
--------------------------------------------------
/u01/oradata/orcl28/system01.dbf
/u01/oradata/orcl28/sysaux01.dbf
/u01/oradata/orcl28/undotbs01.dbf
/u01/oradata/orcl28/users01.dbf
/u01/oradata/orcl28/szd_resource_v2_01.dbf
/u01/oradata/orcl28/szd_resource_v2_02.dbf
/u01/oradata/orcl28/szd_resource_v2_03.dbf
/u01/oradata/orcl28/szd_resource_v3_01.dbf
/u01/oradata/orcl28/szd_resource_v2_04.dbf
/u01/oradata/orcl28/sysaux02.dbf
/u01/oradata/orcl28/system02.dbf
/u01/oradata/orcl28/szd_resource_v2_05.dbf
/u01/oradata/orcl28/szd_resource_v2_06.dbf
/u01/oradata/orcl28/szd_resource_v2_07.dbf
/u01/oradata/orcl28/sysaux03.dbf
/u01/oradata/orcl28/szd_resource_v2_08.dbf
/u01/oradata/orcl28/system03.dbf
/u01/oradata/orcl28/szd_resource_v2_09.dbf

Above,datafile's directory is "/u01/oradata/orcl28",so I write a shell script 'query_db_file_dir.sh' for query datafile's directory,script file as follow:

# +-----------------------------------------------------------------------+
# | |
# | QUERY PATH OF DB FILE (DATA FILE AND TEMP FILE) |
# | |
# +-----------------------------------------------------------------------+

###########################################################################

function query_db_file () {

export DB_FILE=

export DB_FILE_PATH=

export DBFILE_PATH='/tmp/dbfile_path.log'

DB_FILE=`$SQLPLUS -S / as sysdba << EOF
set echo off feedback off heading off underline off;
select name from v\\$datafile union all select name from v\\$tempfile;
exit;
EOF`

for name in ${DB_FILE[@]}
do
$ECHO ${name%/*} >> $DBFILE_PATH
done

DB_FILE_PATH=`$CAT $DBFILE_PATH | $UNIQ | $TR '\n' ' '`

###########################################################################

Execute above shell script file,and then cat file '/tmp/dbfile_path.log',content as follow:

/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28
/u01/oradata/orcl28

I want to know,has Oracle database a parameter about set database files' directory ? If this parameter exist, I wrote shell script to query has to be very simple. I query that parameter to be ok.

and Connor said...

There is not parameter as such, because we dont set that limit. Even if all *existing* datafiles are in a single directory, there is no restriction that would mean the *next* datafile that a DBA creates is in there as well - they could put it wherever they like.

You might this SQL easier

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------
C:\ORACLE\ORADATA\DB122\SYSTEM01.DBF
C:\ORACLE\ORADATA\DB122\SYSAUX01.DBF
C:\ORACLE\ORADATA\DB122\UNDOTBS01.DBF
C:\ORACLE\ORADATA\DB122\USERS01.DBF
C:\ORACLE\ORADATA\DB122\DEMO.DBF
C:\ORACLE\ORADATA\DB122\ASKTOM.DBF
C:\ORACLE\ORADATA\DB122\LARGETS.DBF

7 rows selected.

SQL> select distinct regexp_substr(name,'^.*\\') from v$datafile;

REGEXP_SUBSTR(NAME,'^.*\\')
-------------------------------------------------------------------
C:\ORACLE\ORADATA\DB122\


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Zhao Quanwen, June 08, 2017 - 6:29 am UTC

Thanks a lot, Mr Connor! Your suggestion is very persuasive.
That simply SQL query finally is my expected result.
You answer to this question is beyond my imaginaiton,I learn from you from now on,my idol!
Connor McDonald
June 09, 2017 - 2:17 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here