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