Global System View

person Jason Huangfolder_openOraclelocal_offer, access_time October 10, 2009

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’)

warningComments are closed.