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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>