PROCEDURE: SYS.DBMS_FEATURE_IOT

Source

1 procedure DBMS_FEATURE_IOT(
2     feature_boolean OUT NUMBER,
3     aux_count       OUT NUMBER,
4     feature_info    OUT CLOB)
5 AS
6     feature_usage                 varchar2(1000);
7     num_iot                       number;
8     num_iotpart_index_segments    number;
9     num_iotpart_overflow_segments number;
10 
11 BEGIN
12     feature_boolean             := 0;
13     aux_count                   := 0;
14 
15     -- If the IOT or its overflow segment is stored in a tablespace other than
16     -- SYSTEM or SYSAUX, count it.
17     execute immediate
18        'select count(*) from dba_tables, dba_indexes where ' ||
19        'dba_tables.iot_type is not null and dba_indexes.table_name ' ||
20        '= dba_tables.table_name and (dba_indexes.tablespace_name ' ||
21        'not in (''SYSTEM'', ''SYSAUX'', ''TEMP'', ''SYSEXT'')' ||
22        'or dba_tables.tablespace_name ' ||
23        'not in (''SYSTEM'', ''SYSAUX'', ''TEMP'', ''SYSEXT''))'
24     into num_iot;
25 
26     -- Partitioned IOT/overflow segment
27     -- for the IOT overflow segment partitions
28     execute immediate
29        'select count(*) from dba_tables, dba_tab_partitions where ' ||
30        'dba_tables.iot_type is not null and dba_tables.table_name = ' ||
31        'dba_tab_partitions.table_name ' ||
32        'and dba_tab_partitions.tablespace_name ' ||
33        'not in (''SYSTEM'', ''SYSAUX'', ''TEMP'', ''SYSEXT'')'
34     into num_iotpart_overflow_segments;
35 
36     -- for the IOT index segment partitions
37     execute immediate
38        'select count(*) ' ||
39        'from dba_tables, dba_indexes, dba_ind_partitions where ' ||
40        'dba_tables.iot_type is not null and dba_tables.table_name = ' ||
41        'dba_indexes.table_name and dba_indexes.index_name = ' ||
42        'dba_ind_partitions.index_name and ' ||
43        'dba_ind_partitions.tablespace_name ' ||
44        'not in (''SYSTEM'', ''SYSAUX'', ''TEMP'', ''SYSEXT'')'
45     into num_iotpart_index_segments;
46 
47     -- Composite partitioning is not supported for IOTs
48 
49     --Summary
50     feature_usage :=
51         ' Index Organized Table Feature Usage: ' ||
52                 'Index Organized Tables: ' ||
53                   to_char(num_iot) ||
54         ', ' || 'Index Organized Table Partitions ' ||
55         '(Index and Overflow Partitions) ' ||
56                   to_char(num_iotpart_index_segments +
57                           num_iotpart_overflow_segments);
58 
59      if (num_iot + num_iotpart_index_segments +
60          num_iotpart_overflow_segments > 0) then
61       feature_boolean := 1;
62       feature_info := to_clob(feature_usage);
63     else
64       feature_boolean := 0;
65       feature_info := to_clob('Index Organized Tables Not Detected');
66     end if;
67 
68 END;