Check whether the table index in Oracle exists

Use the user_indexes and user_ind_columns system tables to view existing indexes.

For existing indexes in the system, we can use the following two system views (user_indexes and user_ind_columns) to view the specific content, for example, which table belongs to, which column and, specific parameters.

User_indexes: The System View stores the index name and whether the index is a unique index.

User_ind_column: The System View stores the index name, corresponding tables, and columns.

View the number and category of indexes:

SQL> select * from user_indexes where table = 'table name ';

View the indexed fields:

SQL> select * from user_ind_columns where index_name = upper ('& index_name ');

We can use statements similar to the following to view the basic information of a table index:

Select user_ind_columns.index_name, user_ind_columns.column_name,

User_ind_columns.column_position, user_indexes.uniqueness

From user_ind_columns, user_indexes

Where user_ind_columns.index_name = user_indexes.index_name

And user_ind_columns.table_name = 'name of the table you want to query ';

Through this SQL statement, we can see the specific index of a table, if you want to further explore the index of this table, you should go to user_indexes to see the basic information of this index.

Integrity constraints
DBA_CONSTRAINTS, ALL_CONSTRAINTS, and USER_CONSTRAINST display general information about constraints.
DBA_CONS_COLUMNS, ALL_CONS_COLUMNS, and USER_CONS_COLUMNS display general information about column-related constraints.

The ALL_CONS_COLUMNS view and DBA_CONS_COLUMNS view have the same column definitions as USER_CONS_COLUMNS.

The ALL_CONS_COLUMNS view displays the column information of all the table constraints that the user can access, regardless of the owner.
The DBA_CONS_COLUMNS view lists the column-level constraints of the entire database.
USER_CONS_COLUMNS

Functions and relationships of user_constraints and user_cons_columns tables

User_constraints: View of table constraints, which describes what the constraint type (constraint_type) is and which tables (table_name) belong to. If the constraint type is R (foreign key, the r_constraint_name field stores the primary key constraint name in the referenced master table.

User_cons_columns: View of Table constraint fields, which indicates which constraints are involved in columns related to constraints in the table. These constraints include primary key constraints, foreign key constraints, and index constraints.

The two can be associated through (owner, constraint_name, table_name:

Select
A. owner foreign key owner,
A. table_name foreign key table,
Substr (c. column_name, 1,127) foreign key column,
B. owner primary key owner,
B. table_name primary key table,
Substr (d. column_name, 1,127) primary key column
From
User_constraints,
User_constraints B,
User_cons_columns c,
User_cons_columns d
Where
A. r_constraint_name = B. constraint_name
And a. constraint_type = 'R'
And B. constraint_type = 'P'
And a. r_owner = B. owner
And a. constraint_name = c. constraint_name
And B. constraint_name = d. constraint_name
And a. owner = c. owner
And a. table_name = c. table_name
And B. owner = d. owner
And B. table_name = d. table_name

1 2 3 Next Page