How to select directory name from datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to select directory name from datafile

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50

    How to select directory name from datafile

    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.
    An elephant is a mouse with an operating system.
    Share on Google+

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Collect all the datafile names and edit in the vi editor, Its easy and fast.
    "What is past is PROLOGUE"
    Share on Google+

  3. #3
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    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
    An elephant is a mouse with an operating system.
    Share on Google+

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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
    Share on Google+

  5. #5
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    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;
    Share on Google+

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    select substr(file_name, 1, instr(file_name, '/',-1)) PATH
    from dba_data_files ;

    Tamil
    Share on Google+

  7. #7
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    Thanks dbtoo and tamilselvan for the help. I am closing this thread,
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width