SQL*Plus Index report

Report indexes for the specified table. Specify an index name to see details about that index (which table, what columns). Allows user.table, or user.index.

-- 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
 
-- 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                           hea "Index"
col uniqueness    format a7              hea "Unique?"
col index_type    format a17             hea "Type"
col status        format a8              hea "Status"
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 status on num_rows on distinct_keys 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   all_tab_columns
where  table_name = 'USER_INDEXES'
and    column_name = 'VISIBILITY';
 
exec :object_name := upper('&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);
 
   lookup_error exception;
   pragma exception_init(lookup_error, -20001);
begin
 
   begin
      select object_type, owner into v_object_type, v_owner
      from   &dba_all._objects
      where  ( owner = v_owner or v_owner is null )
      and    object_name = v_object_name
      and    object_type in ('TABLE','INDEX');
 
   exception
      when no_data_found then
         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 );
      when too_many_rows 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 );
   end;
 
   if v_object_type = 'INDEX' then
      select table_name
      into   v_table_name
      from   &dba_all._indexes
      where  index_name = v_object_name  -- v_table_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 ' || 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 &dba_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
           , decode(compression,'ENABLED','Y', 'DISABLED','N') 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   &dba_all._indexes i
 
             join &dba_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
 
-- 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 status        clear
col num_rows      clear
col distinct_keys clear
col last_analyzed clear
col column_name   clear
col visibility    clear
set term on