Turn from: http://tong7858-126-com.iteye.com/blog/829323
1. In versions above Oracle8, you can use the following statements to index names and queries that create INDEX statements.
SELECT index_name,dbms_metadata.get_ddl (' INDEX ', Index_name,table_owner) index_ddl
From User_indexes
WHERE table_name like ' kj_% '; --' kj_% ' represents a set of tables that begin with KJ and can be based on
--Change to the appropriate table name, indicating that the letter is case-sensitive
Query results are as follows
In this picture, the blog is the statement that creates the index.
2. In the oracle10g version, you can use User_ind_columns to query information such as the table name of the database, the index name, the field where the index was created, and so on.
SELECT * FROM User_ind_columns t
where t.table_name like ' kj_% '; --a set of tables that begin with KJ, and table names are case-sensitive.
Query results Picture
3. You can display fields according to User_ind_columns, to change the query conditions to display the corresponding field contents, as follows:
Select T.table_name,t.index_name,t.column_name from User_ind_columns t
where t.table_name like ' kj_% ';
The results of the query are shown in the following picture
4. You can also try the following statement query to display the database users that the index belongs to.
SELECT * FROM User_ind_statistics