find orphaned ASM files

SET VERIFY OFF

SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000
SET TRIMSPOOL ON


BEGIN
   FOR c IN (SELECT name Diskgroup
               FROM VASM_DISKGROUP)
   LOOP
      FOR l
         IN (SELECT 'rm ' || files files
               FROM
                    (SELECT '+' || c.Diskgroup || files files, TYPE
                       FROM (    SELECT UPPER
                                        (
                                           SYS_CONNECT_BY_PATH (aa.name, '/')
                                        )
                                           files
                                      , aa.reference_index
                                      , b.TYPE
                                   FROM (SELECT file_number
                                              , alias_directory
                                              , name
                                              , reference_index
                                              , parent_index
                                           FROM vasm_alias
                                           where ALIAS_INDEX NOT IN (
                                               select ALIAS_INDEX from vasm_alias                                               where file_number IN (
                                                    select file_number from vasm_alias 
                                                    where SYSTEM_CREATED = 'N'
                                                         and group_number =
                                                           (SELECT group_number
                                                              FROM vasm_diskgroup
                                                             WHERE name = c.Diskgroup
                                                             )
                                                    )                                                    and SYSTEM_CREATED = 'Y'
                                                    and group_number =
                                                           (SELECT group_number
                                                              FROM vasm_diskgroup
                                                             WHERE name = c.Diskgroup
                                                             )
                                            ) and group_number =
                                                    (SELECT group_number
                                                        FROM vasm_diskgroup
                                                      WHERE name = c.Diskgroup
                                                     )
                                        ) aa
                                      , (SELECT parent_index
                                           FROM (SELECT distinct parent_index
                                                   FROM vasm_alias
                                                  WHERE     group_number =
                                                               (SELECT group_number
                                                                  FROM vasm_diskgroup
                                                                 WHERE name =
                                                                          c.Diskgroup)
                                                        AND alias_index<50)) a
                                      , (SELECT file_number, TYPE
                                           FROM (SELECT file_number, TYPE
                                                   FROM vasm_file
                                                  WHERE group_number =
                                                           (SELECT group_number
                                                              FROM vasm_diskgroup
                                                             WHERE name =
                                                                      c.Diskgroup)))
                                        b
                                  WHERE     aa.file_number = b.file_number(+)
                                        AND aa.alias_directory = 'N'
                                        AND aa.file_number != 4294967295
                                        AND b.TYPE IN
                                               ('DATAFILE'
                                              , 'ONLINELOG'
                                              , 'CONTROLFILE'
                                              , 'TEMPFILE')
                             START WITH aa.PARENT_INDEX = a.parent_index
                             CONNECT BY PRIOR aa.reference_index =
                                           aa.parent_index)
                      WHERE SUBSTR
                            (
                               files
                             , INSTR (files, '/', 1, 1)
                             ,   INSTR (files, '/', 1, 2)
                               - INSTR (files, '/', 1, 1)
                               + 1
                            ) =
                               (SELECT dbname
                                  FROM (SELECT    '/'
                                               || UPPER (db_unique_name)
                                               || '/'
                                                  dbname
                                          FROM vdatabase))
                     MINUS
                     (SELECT UPPER (name) files, 'DATAFILE' TYPE
                        FROM vdatafile
                      UNION ALL
                      SELECT UPPER (name) files, 'TEMPFILE' TYPE
                        FROM vtempfile
                      UNION ALL
                      SELECT UPPER (name) files, 'CONTROLFILE' TYPE
                        FROM vcontrolfile
                       WHERE name LIKE '+' || c.Diskgroup || '%'
                      UNION ALL
                      SELECT UPPER (name), 'CONTROLFILE' TYPE
                        FROM vdatafile_copy
                       WHERE deleted = 'NO'
                      UNION ALL
                      SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE
                        FROM v$logfile
                       WHERE MEMBER LIKE '+' || c.Diskgroup || '%')))
      LOOP
         DBMS_OUTPUT.put_line (l.files);
      END LOOP;
   END LOOP;
END;
/