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
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