Script:找出ASM中的Spfile参数文件

以下脚本可以用于找出ASM存储中的Spfile参数文件，因为使用asmcmd去查找很不方便，而spfile丢失又是很头大的事情， 所以有一个脚本代劳可以省不少功夫呢！

--- listspfiles.sql
--- Purpose: Sample script to list spfiles kept in ASM instance
--- Usage: This should be run against an ASM instance,
--- not a database instance.
---
--- cut here --%<----%<----%<----%<----%<----%<--

--list all spfiles

set lines 120
col full_path for a110
SELECT full_path, dir, sys
FROM
(SELECT
CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT g.name gname,
a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%SPFILE%'
/

Sample output:

FULL_PATH                                                                                                      D S
-------------------------------------------------------------------------------------------------------------- - -
+DATA/Aspfile.ora                                                                                              N N
+DATA/VPROD/PARAMETERFILE/spfile.273.766620265                                                                 N Y
+DATA/VPROD/PARAMETERFILE/spfile.365.773976489                                                                 N Y
+DATA/VPROD/spfileVPROD.ora                                                                                    N N