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!

June 09, 2017 - 2:17 am UTC

Glad we could help

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

CLOB/BLOB/etc

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

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

CLOB/BLOB/etc