you can easily use these global views to get specific oracle system information:
select * from [VIEW name]
ALL_ALL_TABLES: shows all tables even with DBA system tables
USER_ALL_TABLES : shows all tables available to user
DBA_ALL_TABLES : all dba objects and system tables
The view USER_TAB_COLUMNS lists all the columns detail information on the all tables
select column_id, table_name, column_name, Data_type, data_length from USER_TAB_COLUMNS order by table_name, column_id;
select column_id, table_name, column_name, Data_type, data_length from USER_TAB_COLUMNS
where table_name in (select table_name from user_tables)
order by table_name, column_id;
To find all the columns that defines the unique record constraint in a table:
The constraint info is defined in system view ALL_CONSTRAINTS (header), ALL_CONS_COLUMNS(detail defines the columns, link key is CONSTRAINT_NAME)
SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN
(SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = ‘MLP_HEADER’
AND OWNER = ‘MIGPRJ’
AND CONSTRAINT_TYPE = ‘U’)
