PROCEDURE: SYS.DBMS_FEATURE_ASM

Source

1 PROCEDURE dbms_feature_asm
2       (is_used OUT number, total_diskgroup_size OUT number, summary OUT clob)
3 AS
4    redundancy_type    clob;
5    max_diskgroup_size number;
6    min_diskgroup_size number;
7    num_disk           number;
8    num_diskgroup      number;
9    min_disk_size      number;
10    max_disk_size      number;
11    num_failgroup      number;
12    min_failgroup_size number;
13    max_failgroup_size number;
14 
15 BEGIN
16   -- initialize
17   redundancy_type      := 'Redundancy';
18   max_diskgroup_size   := NULL;
19   min_diskgroup_size   := NULL;
20   total_diskgroup_size := NULL;
21   num_disk             := NULL;
22   num_diskgroup        := NULL;
23   min_disk_size        := NULL;
24   max_disk_size        := NULL;
25   num_failgroup        := NULL;
26   min_failgroup_size   := NULL;
27   max_failgroup_size   := NULL;
28 
29   select count(*) into is_used from v$asm_client;
30   -- if asm is used
31   if (is_used >= 1) then
32 
33        select max(total_mb), min(total_mb), sum(total_mb), count(*)
34          into max_diskgroup_size, min_diskgroup_size,
35               total_diskgroup_size, num_diskgroup
36          from v$asm_diskgroup;
37 
38        select max(total_mb), min(total_mb), count(*)
39          into max_disk_size, min_disk_size, num_disk
40          from v$asm_disk;
41 
42        select max(total_fg_mb), min(total_fg_mb), count(*)
43          into max_failgroup_size, min_failgroup_size, num_failgroup
44          from (select sum(total_mb) as total_fg_mb
45                  from v$asm_disk
46                  group by failgroup);
47 
48 
49 
50        for item in (select type, count(*) as rcount from v$asm_diskgroup group by type)
51        loop
52          redundancy_type:=redundancy_type||':'||item.type||'='||item.rcount;
53        end loop;
54 
55        summary :=redundancy_type||':total_diskgroup_size:'||total_diskgroup_size
56                 ||':max_diskgroup_size:'||max_diskgroup_size
57                 ||':min_diskgroup_size:'||min_diskgroup_size
58                 ||':num_diskgroup:'||num_diskgroup
59                 ||':max_disk_size:'||max_disk_size
60                 ||':min_disk_size:'||min_disk_size
61                 ||':num_disk:'||num_disk
62                 ||':max_failgroup_size:'||max_failgroup_size
63                 ||':min_failgroup_size:'||min_failgroup_size
64                 ||':num_failgroup:'||num_failgroup;
65 
66   end if;
67 END;