PROCEDURE: SYS.DBMS_FEATURE_AUTO_SSM

Source

1 PROCEDURE dbms_feature_auto_ssm
2      ( feature_boolean  OUT  NUMBER,
3        aux_count        OUT  NUMBER,
4        feature_info     OUT  CLOB)
5 AS
6   auto_seg_space boolean;
7   ts_info        varchar2(1000);
8 BEGIN
9 
10   /* initialize everything */
11   auto_seg_space := FALSE;
12   ts_info        := '';
13   aux_count      := 0;
14 
15   for ts_type in
16      (select segment_space_management, count(*) tcount, sum(size_mb) size_mb
17        from
18         (select ts.tablespace_name, segment_space_management,
19               sum(bytes)/1048576 size_mb
20           from dba_data_files df, dba_tablespaces ts
21          where df.tablespace_name = ts.tablespace_name
22          group by ts.tablespace_name, segment_space_management)
23        group by segment_space_management)
24   loop
25 
26     /* check for auto segment space management */
27     if ((ts_type.segment_space_management = 'AUTO') and
28          (ts_type.tcount > 0)) then
29       auto_seg_space  := TRUE;
30       aux_count       := ts_type.tcount;
31     end if;
32 
33     ts_info := ts_info ||
34         '(Segment Space Management: ' || ts_type.segment_space_management ||
35        ', TS Count: ' || ts_type.tcount ||
36        ', Size MB: '  || ts_type.size_mb || ') ';
37 
38   end loop;
39 
40   /* set the boolean and feature info.  the aux count is already set above */
41   if (auto_seg_space) then
42     feature_boolean := 1;
43     feature_info    := to_clob(ts_info);
44   else
45     feature_boolean := 0;
46     feature_info    := null;
47   end if;
48 
49 END dbms_feature_auto_ssm;