PROCEDURE: SYS.DBMS_FEATURE_LMT

Source

1 PROCEDURE dbms_feature_lmt
2      ( feature_boolean  OUT  NUMBER,
3        aux_count        OUT  NUMBER,
4        feature_info     OUT  CLOB)
5 AS
6   loc_managed boolean;
7   ts_info     varchar2(1000);
8 BEGIN
9 
10   /* initialize everything */
11   loc_managed := FALSE;
12   ts_info     := '';
13   aux_count   := 0;
14 
15   for ts_type in
16      (select extent_management, count(*) tcount, sum(size_mb) size_mb
17        from
18         (select ts.tablespace_name, extent_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, extent_management)
23        group by extent_management)
24   loop
25 
26     /* check for auto segment space management */
27     if ((ts_type.extent_management = 'LOCAL') and
28          (ts_type.tcount > 0)) then
29       loc_managed  := TRUE;
30       aux_count       := ts_type.tcount;
31     end if;
32 
33     ts_info := ts_info ||
34         '(Extent Management: ' || ts_type.extent_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 (loc_managed) 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_lmt;