-- Report indexes on table
-- William Robertson www.williamrobertson.net
-- Usage:
--    @ind tablename
--    @ind indexname
--    Either name may be prefixed with schema
--
-- Note output is 198 chars wide: script sets SQL*Plus linesize but you may need to adjust the terminal width.
 
def table = &1
undef 1
 
-- Set to 'all' if you do not have access to dba views (typically granted via SELECT_CATALOG_ROLE):
def dba_all = dba
 
col index_name         format a30              hea "Index"
col uniqueness         format a7               hea "Unique?"
col index_type         format a17              hea "Type"
col num_rows           format 99g999g999g990   hea "Rows"
col distinct_keys      like num_rows           hea "Distinct|keys"
col clustering_factor  format 9g999g999g990    hea "Clustering|factor"
col last_analyzed      format a11              hea "Last|analyzed"
col column_name        format a50 word         hea "Column name/expression"
col visibility         format a8               hea "Visible?"
col compressed         format a11              hea "Compressed?"
col partitioned        format a12              hea "Partitioned?"
 
set term off
set tab off
cl bre
break on index_name on uniqueness on index_type on compressed on partitioned on visibility on num_rows on distinct_keys on clustering_factor skip1
 
-- Save existing settings -
-- Need to be in a directory you can write to (see http://www.williamrobertson.net/documents/sqlplus-setup.html)
store set sqlplus_settings.sql replace
 
set feed off serverout on autoprint off verify off
set linesize 200
var object_name varchar2(61)
var output refcursor
 
-- Save contents of SQL buffer
-- (adding dummy line at line 0 i.e. before query, to avoid "SP2-0107: Nothing to save" error when buffer is empty)
0 -- ;
save ind.buf repl
 
-- include new column 'VISIBILITY' if present in user_indexes:
column invisible_indexes new_value invisible_indexes
 
select q'[, case visibility when 'VISIBLE' then 'Y' else 'N' end as visibility]' as invisible_indexes
from   &dba_all._tab_columns
where  table_name = 'USER_INDEXES'
and    column_name = 'VISIBILITY';
 
exec :object_name := upper('&table')
undef table
 
set term on

declare
    -- Attempt to parse 'owner.object' into v_owner, v_object_name
    v_owner        all_objects.owner%type := upper(substr(:object_name,1,instr(:object_name,'.') -1));
    v_object_name  varchar2(40) := upper(substr(:object_name,instr(:object_name,'.') +1));
    v_object_type  all_objects.object_type%type;
    v_table_name   all_objects.object_name%type := substr(v_object_name,instr(v_object_name,'.') +1);
    v_multiple_objects_found boolean := false;
 
    lookup_error exception;
    pragma exception_init(lookup_error, -20001);

    procedure resolve_name
        ( p_object   in varchar2
        , out_owner  out all_objects.owner%type
        , out_name   out all_objects.object_name%type
        , out_type   out all_objects.object_type%type
        , out_multiple_objects_found out boolean)
    is
        v_part2      all_objects.object_name%type;
        v_dblink     all_objects.object_name%type;
        v_part1_type number;
        v_object_id  number;

        more_than_one_object_found exception;
        pragma exception_init(more_than_one_object_found, -4047);

        no_matching_object_found exception;
        pragma exception_init(no_matching_object_found, -06564);
    begin
        for i in 1..9 loop
            begin
                continue when i between 3 and 8;
                dbms_utility.name_resolve(p_object, i, out_owner, out_name, v_part2, v_dblink, v_dblink, v_object_id);
            exception
                when no_matching_object_found then null;
                when more_than_one_object_found then  -- (could treat as TABLE here - case where table and index have same name leads here)
                    out_multiple_objects_found := true;
            end;
            
            if out_owner is not null then
                out_type := case when i in (1,2) or out_multiple_objects_found then 'TABLE' when i = 9 then 'INDEX' end;
                exit;
            end if;
        end loop;

    end resolve_name;
begin
    resolve_name(:object_name, v_owner, v_object_name, v_object_type, v_multiple_objects_found );

    if v_object_type is null then
        if v_multiple_objects_found then
            raise_application_error
            ( -20001
            , 'More than one object found named ' || v_object_name ||
                case
                    when v_owner is null then ' - please qualify with schema name, e.g. ' || sys_context('USERENV','SESSION_USER') || '.' || v_object_name
                    else ' in schema ' || v_owner
                end );
        else
            raise_application_error
            ( -20001
            , 'No object found named ' || v_object_name ||
               case
                   when v_owner is null then null
                   else ' in schema ' || v_owner
               end );
        end if;
    end if;
 
    if v_object_type = 'INDEX' then
        select table_name
        into   v_table_name
        from   all_indexes
        where  index_name = v_object_name
        and    table_owner = v_owner;
 
        dbms_output.put_line('Index ' || v_object_name || ' on ' || v_owner || '.' || v_table_name || ':');
    else
        dbms_output.put_line('Indexes for table ' || v_owner || '.' || v_table_name || ':');
    end if;
 
    open :output for
        with fbi_xml as  -- Cast LONG column from dba_ind_expressions, if there are function based indexes for the table
              ( select extractvalue(xs.object_value, '/ROW/INDEX_OWNER')         as index_owner
                        , extractvalue(xs.object_value, '/ROW/INDEX_NAME')          as index_name
                        , extractvalue(xs.object_value, '/ROW/COLUMN_EXPRESSION') as column_expression
                        , extractvalue(xs.object_value, '/ROW/COLUMN_POSITION')    as column_position
                 from    ( select xmltype(
                                          dbms_xmlgen.getxml
                                          ( 'select index_owner, index_name, column_expression, column_position from all_ind_expressions where table_owner = ''' || v_owner ||
                                             ''' and table_name = '''|| v_table_name ||'''' ||
                                             case when v_object_type = 'INDEX' then ' and index_name = ''' || v_object_name || '''' end ||
                                             ' union all select null, null, null, null from dual'  -- dummy row to ensure xml wrapper has something to work with
                                          )
                                      ) as xml
                           from   dual ) x
                        , table(xmlsequence(extract(x.xml, '/ROWSET/ROW'))) xs )
        select i.index_name
             , decode(i.uniqueness,
                      'UNIQUE', 'Y', 'N') as uniqueness
             , case i.index_type when 'FUNCTION-BASED NORMAL' then 'FUNCTION-BASED' else i.index_type end as index_type
             , case when compression ='ENABLED' or compression like 'ADVANCED%' then 'Y' else 'N' end as compressed
             , decode(partitioned,'YES','Y', 'NO','N') as partitioned
             &invisible_indexes
             , decode(sign(i.num_rows),
                           -1, to_number(null),  -- Have seen this in 8i
                           i.num_rows ) as num_rows
             , i.distinct_keys
             , i.clustering_factor
             , to_char(i.last_analyzed,'DD-MON-YYYY') as last_analyzed
             , nvl(x.column_expression,c.column_name) as column_name
        from   all_indexes i
 
               join all_ind_columns c
                    on    c.index_name = i.index_name
                    and  c.index_owner = i.owner
                    and  c.table_name = i.table_name
                    and  c.table_owner = i.table_owner
         
               left join fbi_xml x  -- already filtered for table owner/name
                    on    x.index_name = i.index_name
                    and  x.index_owner = i.owner
                    and  x.column_position = c.column_position
      
        where  i.table_name = v_table_name
        and     i.owner = v_owner
        and     (     ( v_object_type = 'INDEX' and i.index_name = v_object_name )
                  or  v_object_type = 'TABLE' )
        order by i.index_name, c.column_position;
 
exception
    when lookup_error then
        dbms_output.put_line(sqlerrm);
        open :output for
            select 'No such table or index ' || ltrim(v_owner || '.' || v_object_name,'.') || '' as error
            from dual;
end;
/
 
-- Restore saved SQL buffer:
set term off
get ind.buf nolist
clear buffer
 
-- Remove dummy line added earlier and delete file (assumes Windows - change to 'rm' for linux):
1
del
host del ind.buf
 
set term on
print :output
cl bre
@sqlplus_settings.sql
 
col index_name     clear
col uniqueness     clear
col index_type     clear
col num_rows       clear
col distinct_keys  clear
col last_analyzed  clear
col column_name    clear
col visibility     clear
set term on
