1. Find all indexes of the table (including index name, type, constituent column):
Select T.*,i.index_type from User_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = I.tabl E_name and t.table_name = table to query
2. lookup table's primary key (including name, constituent column):
Select cu.* from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.constraint _type = ' P ' and au.table_name = table to query
3, find the uniqueness of the table constraints (including the name, the constituent column):
Select column_name from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.con Straint_type = ' U ' and au.table_name = table to query
4. Find the foreign key of the table (including the name, the table name of the reference table and the corresponding key name, the following is divided into multi-step query):
SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name = table to query
Query the column name of the FOREIGN KEY constraint:
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name
Query the column name of the key referencing the table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name
5. Querying all columns of the table and their properties
Select t.*,c.comments from User_tab_columns t,user_col_comments c where t.table_name = C.table_name and T.column_name = c. column_name and t.table_name = table to query
6. Table name and number of rows per user table in Oracle
Select Table_name,num_rows from User_tables;
7. Oracle Query User tablespace: SELECT * from User_all_tables
8. Oracle queries all functions and stored procedures: SELECT * FROM User_source
9. Oracle Queries All users:
SELECT * from All_users;
SELECT * FROM Dba_users
10. Oracle View Current User connection: SELECT * from V$session
11. Oracle View Current User rights: SELECT * FROM Session_privs
12. Oracle View User table space usage:
Select a.file_id "Fileno", A.tablespace_name
"Tablespace_name",
A.bytes "bytes", A.bytes-sum (NVL (b.bytes,0)) "used",
Sum (NVL (b.bytes,0)) "Free",
Sum (NVL (b.bytes,0))/a.bytes*100 "%free"
From Dba_data_files A, dba_free_space b
where a.file_id=b.file_id (+)
Group BY A.tablespace_name,
A.file_id,a.bytes ORDER by A.tablespace_name;
Oracle queries all user tables for table names, primary key names, indexes, foreign keys, and more