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.

  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"

  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.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  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;

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

    Tamil

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

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