SQL> desc dba_ind_columns. name null? type ----------------------------------------- -------- ------------------------- index_owner not null varchar2(30) index_name not null varchar2(30) table_owner not null varchar2(30) table_name not null varchar2(30) column_name varchar2(4000) column_position not null number column_length not null number descend varchar2(4)
SQL> create view index_nouniq_column_num as 0select t1.owner,t1.index_name,count(0) as column_num from dba_indexes t1,dba_ind_columns t2 where t1.uniqueness= NONUNIQUE and instr(t1.owner, sys )=0 and t1.owner=t2.index_owner and t1.index_name=t2.index_name group by t1.owner,t1.index_name order by t1.owner,column_num.
SQL> create table index_columns( index_owner varchar2(30) not null, index_name varchar2(30) not null, column_names varchar2(512) not null) tablespace users.
步骤三:把只有一个字段的索引内容插入索引字段临时表index_columns。
SQL> insert into index_columns 0select t1.owner,t1.index_name,t2.column_name from index_nouniq_column_num t1,dba_ind_columns t2 where t1.column_num=1 and t1.owner=t2.index_owner and t1.index_name=t2.index_name order by t1.owner,t1.index_name. SQL> commit.
函数getcloumns: create or replace function getcloumns( index_owner1 in varchar2, index_name1 in varchar2, column_nums1 in number) return varchar2 is all_columns varchar2(512). total_num number. i number. cursor c1 is 0select column_name from dba_ind_columns where index_owner=index_owner1 and index_name=index_name1 order by column_position. dummy c1%rowtype. begin total_num:=column_nums1. open c1. fetch c1 into dummy. i:=0. while c1%found loop i:=i 1. if (i=total_num) then all_columns:= all_columns||dummy.column_name. else all_columns:= all_columns||dummy.column_name|| , . end if. fetch c1 into dummy. end loop. close c1. return all_columns. exception when no_data_found then return all_columns. end. /