得到索引名称列表以及对应的表名和列名
create or replace function fn_getindexcols(iv_indexname varchar2,iv_tablename varchar2)
return varchar2
is
result varchar2(1000);
begin
for rec in (select column_name,descend,column_position
from user_ind_columns
where index_name = upper(iv_indexname)
and table_name = upper(iv_tablename)
order by column_position asc)
loop
if rec.column_position = 1
then
result := lower(rec.column_name);
else
result := result || ',' || ' ' || lower(rec.column_name);
end if;
end loop;
if result is null
then
result := '';
end if;
return(result);
end;
/
set feedback off
set timing off
set pagesize 0
set linesize 300
spool d:indexlist.txt
select rpad('index name',40)||' ' || rpad('table name',40) || ' column name list' from dual
;
select rpad('=',40,'=')||' ' || rpad('=',40,'=') || ' ' || rpad('=',40,'=') from dual
;
select rpad(lower(index_name),40)|| ' ' ||rpad(lower(table_name),40) || ' ' || fn_getindexcols(index_name,table_name)
from user_indexes
order by table_name
;
spool off
set feedback on
set timing on
set pagesize 14
drop function fn_getindexcols;
exit
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/802415/viewspace-823030/ ，如需转载，请注明出处，否则将追究法律责任。
