How to select directory name from datafile

[anujdutt]

I want to select Directory name from the datafile name in V$datafile view.
e.g.
SQL> select name from v$datafile where file#=1;
SQL> /misc01/u50/ORACLE/RMANP1/system_01_RMANP1.dbf

I want the result as
/misc01/u50/ORACLE/RMANP1/

I cannot give
SQL> select replace(name,'system_01_RMANP1.dbf','') from v$datafile where file#=1;

Because RMANP1 is ORACLE_SID and I need to run the sql in a common script for 100 databases so it should be generic solution for all databases.

I tried
SQL> select replace(name,'system_01_%','')from v$datafile where file#=1;
SQL> /misc01/u50/ORACLE/RMANP1/system_01_RMANP1.dbf

but it is not working and giving me the whole datafile name.

Please Help.

[dbasan]

Collect all the datafile names and edit in the vi editor, Its easy and fast.

[anujdutt]

For 100 databases it is neither easy nor fast. Moreover I want it to happen dynamically in script so manual intervention is out of question.

I just found a way using this simple sql select.

select substr(name,1,instr(name,'system')-1) from v$datafile where file#=1;

I forgot to mention I wanted to know the directory of the system datafile.

Anyway thanks for the help

[tamilselvan]

SQL> L
1 select substr(file_name, instr(file_name, '/',-1)+1) df
2 from dba_data_files
3* where rownum < 3
SQL> /

DF
--------------------------------------------
users01.dbf
sys01.dbf

Tamil

[dbtoo]

I pulled this out of one of my hot backup scripts. It will give you some food for thought. Written for a u-nix flavor, modify directories as needed.


rem run as system
set echo off termout off feedback off verify off pages 0 linesize 130
column instnc new_value v_inst noprint
column instdate new_value v_instdate noprint
SELECT TO_CHAR(sysdate,'-dd-Mon-yyyy-hh24-mi-ss') instdate FROM dual;
SELECT name instnc FROM v$database;
alter system checkpoint;
alter system switch logfile;
spool /tmp/DB_BEGIN_HOTBACKUP_&v_inst.sql;
select distinct 'alter tablespace '||tablespace_name||' begin backup;'
from dba_data_files, (select rtrim(lower(name)) dbname from v$database);
spool off;
spool /tmp/DBBACKUP_&v_inst;
select distinct tablespace_name||' '||rtrim(file_name)||' '||
substr(file_name,instr(file_name,'/',-1)+1)
from dba_data_files, (select rtrim(lower(name)) dbname from v$database);
spool off;
spool /tmp/CTLFILES_&v_inst;
select rtrim(name) from v$controlfile;
spool off;
spool /tmp/ARCHLOGS_&v_inst;
select decode(((replace(rtrim(value),', ',''''||'
'||''''))||''''),'''''',NULL,
substr(
substr(value,instr(value,'=')+1),1,instr(substr(value,instr(value,'=')+1),' ')-1)
) from v$parameter where name like 'log_archive_dest_1%'
and name not like 'log_archive_dest_state%'
and value is not null and value not like '%?%';
spool off;
spool /tmp/ALERTLOGS_&v_inst.sql;
select 'host cp ' || rtrim(value) || '/alert*.log /uidprd/oradata/uidprd/backup/.' from v$parameter where name='background_dump_de
spool off;
select rtrim(value) from v$parameter where name='background_dump_dest';
spool off;
spool /tmp/REDOLOGS_&v_inst;
select rtrim(member) from v$logfile;
spool off;
spool /tmp/IFILE_&v_inst;
select rtrim(value) from v$parameter where name='ifile';
spool off;
spool /tmp/restore_datafiles_&v_inst;
select distinct
'uncompress < '||
substr(file_name,instr(file_name,'/',-1)+1) ||'.Z > '||
rtrim(file_name)
from dba_data_files, (select rtrim(lower(name)) dbname from v$database);
spool off;
spool /tmp/restore_controlfiles_&v_inst.sql;
select distinct
'host cp '||
rtrim(name) || ' /uidprd/oradata/uidprd/backup/.' from v$controlfile;
spool off;
spool /tmp/restore_controlfiles_&v_inst;
select distinct
'cp '||' '||
substr(name,instr(name,'/',-1)+1) ||' '||
rtrim(name) from v$controlfile;
spool off;
spool /tmp/restore_redo_&v_inst;
select distinct
'uncompress < '||' '||
substr(member,instr(member,'/',-1)+1) ||'.Z > '||
rtrim(member)
from v$logfile;
spool off;
spool /tmp/restore_ifile_&v_inst.sql;
select distinct
'host cp '||' '||
rtrim(value) || ' /uidprd/oradata/uidprd/backup/. '
from v$parameter where name='ifile';
spool off;
spool /tmp/restore_ifile_&v_inst;
select distinct
'cp '||' '||
substr(value,instr(value,'/',-1)+1) ||' '||
rtrim(value) from v$parameter where name='ifile';
spool off;
spool /tmp/restore_pfile_&v_inst.sql;
select distinct 'host cp ${ORACLE_HOME}/dbs/init'||lower(name)||'.ora /uidprd/oradata/uidprd/backup/.' from v$database;
spool off;
spool /tmp/restore_pfile_&v_inst;
select distinct 'cp init'||lower(name)||'.ora ${ORACLE_HOME}/dbs/.' from v$database;
spool off;

[tamilselvan]

select substr(file_name, 1, instr(file_name, '/',-1)) PATH
from dba_data_files ;

Tamil

[anujdutt]

Thanks dbtoo and tamilselvan for the help. I am closing this thread,