asmdu.sql

SET linesize 200
SET trimspool ON
SET serveroutput ON format wrapped

spool asmdu.txt

DECLARE
   -- asmdu.sql
   -- ASM directory usage
   -- 2014-03-01
   -- http://pastebin.com/VU7Hj9UV

   TYPE typ_rec_dglist IS RECORD (dgdir v$asm_alias.reference_index%TYPE);

   TYPE brian_fitzgerald IS TABLE OF typ_rec_dglist
                                   INDEX BY BINARY_INTEGER;

   tab_dglist     brian_fitzgerald;

   TYPE ExaGridDba
      IS RECORD (subdiridx v$asm_alias.reference_index%TYPE);

   TYPE typ_tab_dirlist IS TABLE OF ExaGridDba
                              INDEX BY BINARY_INTEGER;

   TYPE typ_rec_dir IS RECORD
   (
      dgname     v$asm_diskgroup.name%TYPE,
      pindex     v$asm_alias.parent_index%TYPE,
      dirname    v$asm_alias.name%TYPE,
      subdirs    typ_tab_dirlist,
      numfiles   NUMBER,
      bytes      v$asm_file.bytes%TYPE,
      DEPTH      NUMBER,
      PATH       VARCHAR2 (500)
   );

   TYPE typ_tbl_dir IS TABLE OF typ_rec_dir
                          INDEX BY BINARY_INTEGER;

   tbl_dir        typ_tbl_dir;

   TYPE typ_rec_results IS RECORD
   (
      numfiles   NUMBER,
      bytes      v$asm_file.bytes%TYPE
   );

   g_rootrindex   NUMBER := 0;

   SUBTYPE qsortval IS v$asm_alias.name%TYPE;

   SUBTYPE qsortarr IS typ_tab_dirlist;

   SUBTYPE qsortidx IS v$asm_alias.reference_index%TYPE;

   PROCEDURE printdir (rec_dir IN typ_rec_dir)
   IS
   BEGIN
      dbms_output.put_line (
         RPAD ('dgname:', 15) || LPAD (rec_dir.dgname, 30));
      dbms_output.put_line (
         RPAD ('pindex:', 15) || LPAD (rec_dir.pindex, 30));
      dbms_output.put_line (
         RPAD ('dirname:', 15) || LPAD (rec_dir.dirname, 30));
      dbms_output.put_line (
         RPAD ('subdirs:', 15) || LPAD (rec_dir.subdirs.COUNT, 30));
      dbms_output.put_line (
         RPAD ('numfiles:', 15) || LPAD (rec_dir.numfiles, 30));
      dbms_output.put_line (RPAD ('bytes:', 15) || LPAD (rec_dir.bytes, 30));
      dbms_output.put_line (RPAD ('depth:', 15) || LPAD (rec_dir.DEPTH, 30));
   END printdir;

   PROCEDURE root
   IS
      rec_dir   typ_rec_dir;
   BEGIN
      rec_dir.pindex := NULL;
      rec_dir.numfiles := 0;
      rec_dir.bytes := 0;
      rec_dir.PATH := '+';

      tbl_dir (g_rootrindex) := rec_dir;
   END root;

   PROCEDURE diskgroups
   IS
      CURSOR cur_dg
      IS
           SELECT dg.group_number dgnumber, dg.name dgname
             FROM v$asm_diskgroup dg
         ORDER BY dgname;

      rec_cur_dg   cur_dg%ROWTYPE;
      i            BINARY_INTEGER := 1;
   BEGIN
      OPEN cur_dg;

      LOOP
         FETCH cur_dg INTO rec_cur_dg;

         EXIT WHEN cur_dg%notfound;

         DECLARE
            rec_dir   typ_rec_dir;
         BEGIN
            rec_dir.dgname := rec_cur_dg.dgname;
            rec_dir.pindex := g_rootrindex;
            rec_dir.dirname := rec_cur_dg.dgname;
            rec_dir.numfiles := 0;
            rec_dir.bytes := 0;
            rec_dir.DEPTH := 1;

            tbl_dir (rec_cur_dg.dgnumber * 16777216) := rec_dir;
         END;

         tab_dglist (i).dgdir := rec_cur_dg.dgnumber * 16777216;
         i := i + 1;
      END LOOP;

      CLOSE cur_dg;
   END diskgroups;

   PROCEDURE asmvols
   IS
      CURSOR cur_vol
      IS
           SELECT dg.name dgname,
                  f.group_number dgnumber,
                  f.TYPE file_type,
                  SUM (f.bytes) bytes,
                  COUNT (*) numfiles
             FROM    v$asm_file f
                  join
                     v$asm_diskgroup dg
                  ON f.group_number = dg.group_number
            WHERE (f.group_number, f.file_number, f.incarnation) NOT IN
                     (SELECT a.group_number, a.file_number, a.file_incarnation
                        FROM v$asm_alias a)
         GROUP BY dg.name, f.group_number, f.TYPE;

      rec_cur_vol     cur_vol%ROWTYPE;
      rec_dir         typ_rec_dir;

      l_dummyrindex   NUMBER := -1;
   BEGIN
      OPEN cur_vol;

      LOOP
         FETCH cur_vol INTO rec_cur_vol;

         EXIT WHEN cur_vol%notfound;
         rec_dir.dgname := rec_cur_vol.dgname;
         rec_dir.pindex := rec_cur_vol.dgnumber * 16777216;
         rec_dir.dirname := rec_cur_vol.file_type;
         rec_dir.numfiles := rec_cur_vol.numfiles;
         rec_dir.bytes := rec_cur_vol.bytes;

         tbl_dir (l_dummyrindex) := rec_dir;
         l_dummyrindex := l_dummyrindex - 1;
      END LOOP;

      CLOSE cur_vol;
   END asmvols;

   PROCEDURE directories
   IS
      CURSOR cur_dir
      IS
           SELECT dg.name dgname,
                  a.reference_index rindex,
                  a.parent_index pindex,
                  a.name dirname
             FROM    v$asm_alias a
                  join
                     v$asm_diskgroup dg
                  ON a.group_number = dg.group_number
            WHERE a.alias_directory = 'Y'
         ORDER BY dgname, dirname;

      rec_cur_dir   cur_dir%ROWTYPE;
      rec_dir       typ_rec_dir;
   BEGIN
      OPEN cur_dir;

      LOOP
         FETCH cur_dir INTO rec_cur_dir;

         EXIT WHEN cur_dir%notfound;
         rec_dir.dgname := rec_cur_dir.dgname;
         rec_dir.pindex := rec_cur_dir.pindex;
         rec_dir.dirname := rec_cur_dir.dirname;
         rec_dir.numfiles := 0;
         rec_dir.bytes := 0;

         tbl_dir (rec_cur_dir.rindex) := rec_dir;
      END LOOP;

      CLOSE cur_dir;
   END directories;

   PROCEDURE populatedirs
   IS
      l_diridx      v$asm_alias.reference_index%TYPE;
      l_parentidx   v$asm_alias.parent_index%TYPE;
      i             BINARY_INTEGER;
   BEGIN
      l_diridx := tbl_dir.FIRST;

      WHILE l_diridx IS NOT NULL
      LOOP
         l_parentidx := tbl_dir (l_diridx).pindex;

         IF tbl_dir.EXISTS (l_parentidx)
         THEN
            IF tbl_dir (l_parentidx).subdirs.FIRST IS NULL
            THEN
               i := 1;
            ELSE
               i := tbl_dir (l_parentidx).subdirs.LAST + 1;
            END IF;

            tbl_dir (l_parentidx).subdirs (i).subdiridx := l_diridx;
         END IF;

         l_diridx := tbl_dir.next (l_diridx);
      END LOOP;
   END populatedirs;

   PROCEDURE files
   IS
      CURSOR cur_file
      IS
         SELECT a.group_number,
                a.parent_index pindex,
                f.bytes bytes,
                a.name filename
           FROM    v$asm_file f
                join
                   v$asm_alias a
                ON     f.group_number = a.group_number
                   AND f.file_number = a.file_number
                   AND f.incarnation = a.file_incarnation
                   AND a.system_created = 'Y';

      rec_cur_file   cur_file%ROWTYPE;
   BEGIN
      OPEN cur_file;

      LOOP
         FETCH cur_file INTO rec_cur_file;

         EXIT WHEN cur_file%notfound;

         IF tbl_dir.EXISTS (rec_cur_file.pindex)
         THEN
            tbl_dir (rec_cur_file.pindex).numfiles :=
               tbl_dir (rec_cur_file.pindex).numfiles + 1;
            tbl_dir (rec_cur_file.pindex).bytes :=
               tbl_dir (rec_cur_file.pindex).bytes + rec_cur_file.bytes;
         ELSE                                                         -- debug
            dbms_output.put_line (
                  'not exists: '
               || rec_cur_file.pindex
               || ' name:'
               || rec_cur_file.filename);
         END IF;
      END LOOP;

      CLOSE cur_file;
   END files;

   PROCEDURE printresults (rec_dir       IN typ_rec_dir,
                           rec_results   IN typ_rec_results)
   IS
   BEGIN
      dbms_output.put_line (
            LPAD (rec_results.numfiles, 6)
         || ' '
         || LPAD (ROUND (rec_results.bytes / 1024 / 1024), 9)
         || ' '
         || LPAD (' ', 2 * rec_dir.DEPTH, ' ')
         || rec_dir.PATH);
   END printresults;

   FUNCTION arrval (arr IN OUT qsortarr, p_idx IN BINARY_INTEGER)
      RETURN qsortval
   IS
   BEGIN
      RETURN tbl_dir (arr (p_idx).subdiridx).dirname;
   END arrval;

   PROCEDURE swap (arr   IN OUT qsortarr,
                   l     IN     BINARY_INTEGER,
                   r     IN     BINARY_INTEGER)
   IS
      l_idxtmp   qsortidx;
   BEGIN
      l_idxtmp := arr (l).subdiridx;
      arr (l).subdiridx := arr (r).subdiridx;
      arr (r).subdiridx := l_idxtmp;
   END swap;

   PROCEDURE qsort (arr         IN OUT qsortarr,
                    p_lowidx    IN     BINARY_INTEGER,
                    p_highidx   IN     BINARY_INTEGER)
   IS
      l         BINARY_INTEGER := p_lowidx;
      r         BINARY_INTEGER := p_highidx;
      l_pivot   qsortval;
   BEGIN
      l_pivot := arrval (arr, (p_lowidx + p_highidx) / 2);

      LOOP
         WHILE arrval (arr, l) < l_pivot
         LOOP
            l := l + 1;
         END LOOP;

         WHILE arrval (arr, r) > l_pivot
         LOOP
            r := r - 1;
         END LOOP;

         IF l <= r
         THEN
            swap (arr, l, r);
            l := l + 1;
            r := r - 1;
         END IF;

         EXIT WHEN l > r;
      END LOOP;

      IF p_lowidx < r
      THEN
         qsort (arr, p_lowidx, r);
      END IF;

      IF l < p_highidx
      THEN
         qsort (arr, l, p_highidx);
      END IF;
   END qsort;

   FUNCTION descend (rec_dir IN OUT typ_rec_dir)
      RETURN typ_rec_results
   IS
      rec_results   typ_rec_results;
   BEGIN
      rec_results.numfiles := rec_dir.numfiles;
      rec_results.bytes := rec_dir.bytes;

      IF rec_dir.subdirs.FIRST IS NOT NULL
      THEN
         qsort (rec_dir.subdirs, rec_dir.subdirs.FIRST, rec_dir.subdirs.LAST);

         FOR i IN rec_dir.subdirs.FIRST .. rec_dir.subdirs.LAST
         LOOP
            DECLARE
               rec_subresults   typ_rec_results;
               l_subdiridx      v$asm_alias.reference_index%TYPE;
            BEGIN
               l_subdiridx := rec_dir.subdirs (i).subdiridx;

               IF rec_dir.PATH = '+'
               THEN
                  tbl_dir (l_subdiridx).PATH :=
                     rec_dir.PATH || tbl_dir (l_subdiridx).dirname;
               ELSE
                  tbl_dir (l_subdiridx).PATH :=
                     rec_dir.PATH || '/' || tbl_dir (l_subdiridx).dirname;
               END IF;

               tbl_dir (l_subdiridx).DEPTH := rec_dir.DEPTH + 1;
               rec_subresults := descend (tbl_dir (l_subdiridx));
               rec_results.numfiles :=
                  rec_results.numfiles + rec_subresults.numfiles;
               rec_results.bytes := rec_results.bytes + rec_subresults.bytes;
            END;
         END LOOP;
      END IF;

      printresults (rec_dir, rec_results);
      RETURN rec_results;
   END descend;

   PROCEDURE traverse
   IS
      rec_subresults   typ_rec_results;
   BEGIN
      rec_subresults := descend (tbl_dir (g_rootrindex));
   END traverse;
BEGIN
   root;
   diskgroups;
   asmvols;
   directories;
   populatedirs;
   files;
   traverse;
END;
/

quit