Just Display Data File Directories 1

[tekdudedude]

Hello All,

This command shows the full path to all the data files in the database:

select file_name from dba_data_files;

How can I output just all the unique full paths and not the file names (system.dbf etc.)?

I want to do this so I know what physical directories need to be created upon a complete system restore (or moving database to another system via RMAN). I ultimately hope to create a script to recreate the required directories.

Thanks for you help,

TD

 

[SantaMufasa]

TekDude,

Here is my ShowDBPaths.sql script, which does what you want (without modification) for either Windows or any *nix operating system:

(On Windows):
select distinct substr(file_name,1,instr(file_name,delimiter,-1)) paths from
(select file_name from dba_data_files
 union
 select member from v$logfile
 union
 select name from v$controlfile)
,(select decode((length(file_name)-length(replace(file_name,'/',null))),0,'\','/') delimiter
  from dba_data_files
 where rownum <= 1)
/

PATHS
---------------------------------
C:\DHUNT\ORACLEDATA\DBDATA\DHUNT\
F:\ORADATA\

2 rows selected.

(On *nix):
select distinct substr(file_name,1,instr(file_name,delimiter,-1)) paths from
(select file_name from dba_data_files
 union
 select member from v$logfile
 union
 select name from v$controlfile)
,(select decode((length(file_name)-length(replace(file_name,'/',null))),0,'\','/') delimiter
  from dba_data_files
 where rownum <= 1)
/

PATHS
-------------------------------------
/home/oracle/hamconf/
/home/oracle/product/10.2.0/db_1/dbs/

2 rows selected.

Let us know how you like it.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tekdudedude]

Santa,

Great script! Thanks for posting.

TD