PROCEDURE: SYS.DBMS_FEATURE_IMC

Source

1 procedure DBMS_FEATURE_IMC(
2     feature_boolean OUT NUMBER,
3     aux_count       OUT NUMBER,
4     feature_info    OUT CLOB)
5 AS
6     feature_usage               varchar2(1000);
7     num_tab                     number;
8     num_tab_part                number;
9     num_tab_subpart             number;
10     num_segs                    number;
11     inmemory_size_value         number;
12 
13 BEGIN
14     feature_boolean             := 0;
15     aux_count                   := 0;
16     inmemory_size_value         := 0;
17 
18     -- check if any segments are enabled for in-memory
19     execute immediate
20        'select count(*) from dba_tables where ' ||
21        'inmemory_compression is not null and ' ||
22        'not (tablespace_name in (''SYSTEM'', ''SYSAUX'') ' ||
23        'and owner like ''SYS'')'
24     into num_tab;
25 
26     execute immediate
27        'select count(*) from dba_tab_partitions where ' ||
28        'inmemory_compression is not null and ' ||
29        'not (tablespace_name in (''SYSTEM'', ''SYSAUX'') ' ||
30        'and table_owner like ''SYS'') and table_name ' ||
31        'not like ''BIN$%'''
32     into num_tab_part;
33 
34     execute immediate
35        'select count(*) from dba_tab_subpartitions where ' ||
36        'inmemory_compression is not null and ' ||
37        'not (tablespace_name in (''SYSTEM'', ''SYSAUX'') ' ||
38        'and table_owner like ''SYS'') and table_name ' ||
39        'not like ''BIN$%'''
40     into num_tab_subpart;
41 
42     -- check if any segments are actually in-memory
43     execute immediate
44        'select count(*) from gv$im_segments_detail where ' ||
45        'segtype=0'
46     into num_segs;
47 
48     -- check the value of the parameter "inmemory_size" from
49     -- all instances
50     execute immediate
51        'select nvl(max(value),0) from gv$parameter where ' ||
52        'name = ''inmemory_size'''
53     into inmemory_size_value;
54 
55     --Summary
56     feature_usage :=
57         ' In-Memory Column Store Feature Usage: ' ||
58                 'In-Memory Column Store Tables: ' ||
59                   to_char(num_tab) ||
60         ', ' || 'In-Memory Column Store Table Partitions: ' ||
61                   to_char(num_tab_part) ||
62         ', ' || 'In-Memory Column Store Table Subpartitions: ' ||
63                   to_char(num_tab_subpart) ||
64         ', ' || 'Total In-Memory Column Store Segments Populated: ' ||
65                   to_char(num_segs);
66 
67      if ((num_tab + num_tab_part + num_tab_subpart + num_segs > 0)
68 	  AND (inmemory_size_value > 0))
69 	then
70       feature_boolean := 1;
71       feature_info := to_clob(feature_usage);
72     else
73       feature_boolean := 0;
74       feature_info := to_clob('In-Memory Column Store Not Detected');
75     end if;
76 END;