PROCEDURE: SYS.DBMS_FEATURE_AUM

Source

1 PROCEDURE dbms_feature_aum
2      ( feature_boolean  OUT  NUMBER,
3        aux_count        OUT  NUMBER,
4        feature_info     OUT  CLOB)
5 AS
6   ts_info         varchar2(1000);
7   undo_blocks     number;
8   max_concurrency number;
9 BEGIN
10 
11   select count(*) into feature_boolean from v$system_parameter where
12     name = 'undo_management' and upper(value) = 'AUTO';
13 
14   if (feature_boolean = 0) then
15     /* not automatic undo management */
16     aux_count    := 0;
17     feature_info := null;
18   else
19 
20     aux_count := 0;
21 
22     /* undo tablespace information */
23     for ts_type in
24       (select retention, count(*) tcount, sum(size_mb) size_mb
25         from
26          (select ts.tablespace_name, retention, sum(bytes)/1048576 size_mb
27            from dba_data_files df, dba_tablespaces ts
28           where df.tablespace_name = ts.tablespace_name
29             and ts.contents = 'UNDO'
30           group by ts.tablespace_name, retention)
31         group by retention)
32     loop
33 
34       /* track total number of tablespaces */
35       aux_count := aux_count + ts_type.tcount;
36 
37       ts_info := ts_info ||
38           '(Retention: ' || ts_type.retention ||
39          ', TS Count: ' || ts_type.tcount ||
40          ', Size MB: '  || ts_type.size_mb || ') ';
41 
42     end loop;
43 
44     /* get some more information */
45     select sum(undoblks), max(maxconcurrency)
46       into undo_blocks, max_concurrency
47       from v$undostat
48       where begin_time >=
49              (SELECT nvl(max(last_sample_date), sysdate-7)
50                 FROM dba_feature_usage_statistics);
51 
52     ts_info := ts_info || '(Undo Blocks: ' || undo_blocks ||
53                          ', Max Concurrency: ' || max_concurrency || ') ';
54 
55     for ssold in
56       (select to_char(min(begin_time), 'YYYY-MM-DD HH24:MI:SS') btime,
57               to_char(max(end_time),   'YYYY-MM-DD HH24:MI:SS') etime,
58               sum(SSOLDERRCNT) errcnt
59         from v$undostat
60         where (begin_time >=
61                (SELECT nvl(max(last_sample_date), sysdate-7)
62                   FROM dba_feature_usage_statistics)))
63     loop
64       ts_info := ts_info ||
65           '(Snapshot Old Info - Begin Time: ' || ssold.btime ||
66                         ', End Time: '   || ssold.etime ||
67                         ', SSOLD Error Count: ' || ssold.errcnt || ') ';
68     end loop;
69 
70     feature_boolean := 1;
71     feature_info    := to_clob(ts_info);
72 
73   end if;
74 
75 END dbms_feature_aum;