PROCEDURE: SYS.DBMS_FEATURE_ADV_TABCMP

Source

1 procedure DBMS_FEATURE_ADV_TABCMP(
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 
11 BEGIN
12     feature_boolean             := 0;
13     aux_count                   := 0;
14 
15     -- dbms_compression might create tables with
16     -- prefixes 'CMP4%' which we want to ignore
17     execute immediate
18        'select count(*) from dba_tables where ' ||
19        'compress_for = ''ADVANCED'' and table_name ' ||
20        'not like ''CMP4$%'' '
21     into num_tab;
22 
23     execute immediate
24        'select count(*) from dba_tab_partitions where ' ||
25        'compress_for = ''ADVANCED'' '
26     into num_tab_part;
27 
28     execute immediate
29        'select count(*) from dba_tab_subpartitions where ' ||
30        'compress_for = ''ADVANCED'' '
31     into num_tab_subpart;
32 
33     --Summary
34     feature_usage :=
35         ' ADVANCED Table Compression Feature Usage: ' ||
36                 ' Tables compressed for ADVANCED: ' ||
37                   to_char(num_tab) ||
38         ', ' || ' Table partitions compressed for ADVANCED: ' ||
39                   to_char(num_tab_part) ||
40         ', ' || ' Table subpartitions compressed for ADVANCED: ' ||
41                   to_char(num_tab_subpart);
42 
43      if (num_tab + num_tab_part + num_tab_subpart > 0) then
44       feature_boolean := 1;
45       feature_info := to_clob(feature_usage);
46     else
47       feature_boolean := 0;
48       feature_info := to_clob('ADVANCED Table Compression Not Detected');
49     end if;
50 END;