Tuesday, August 20, 2013

How to find out ASM file locations for a Database

column full_alias_path format a70
column file_type format a15


select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
       system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
              a.reference_index rindex , a.system_created, a.alias_directory,
              c.type file_type
       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
       where a.group_number = b.group_number
             and a.group_number = c.group_number(+)
             and a.file_number = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     )
start with (mod(pindex, power(2, 24))) = 0
            and rindex in
                ( select a.reference_index
                  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = '&DATABASENAME'
                )
connect by prior rindex = pindex;


You can use this query with any Database running on the same server for a Database.



4 comments:

  1. Hi, Nice Sql Query in Oracle.Thanks.....

    -Hari
    Theosoft

    ReplyDelete
  2. Did this query work?

    Just asking...cos

    and a.name = '&DATABASENAME'

    ReplyDelete
  3. I have used this query many time ... :) i dont know what`s wrong with you. If you have any issues with the same , Please paste your error. Instead of giving '&DATABASENAME' you need to specify the DB Name(ASM) .

    Thanks,
    Jyothish

    ReplyDelete
  4. Please paste your error , if you have , I am waiting ... :)

    ReplyDelete