Oracle system level views are broken into two separate sections, namely metadata and performance views. Metadata views allow a window into the metadata of a database. Metadata is the data about the data. The data about the data is the table structures and column descriptions, amongst many other things. For example shown below is the DBA_TABLES metadata view.
set linesize 40; desc dba_tables; Name Null? Type ----------------- -------- ------------ OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIS NUMBER T_BLOCKS NUM_FREELIST_BLOC NUMBER KS DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8)
Users must be granted the SELECT_CATALOG_ROLE privilege to give full access to metadata and performance views. Without DBA privileges users can see only the USER_ views. USER_ views allow access only to metadata and performance information applicable to the currently logged in user. Thus for instance logged in as the Books user the following scripts can be used to examine index and constraint metadata for the Books user.
set wrap off linesize 132 pages 80;
column pos format 990;
column col format a20;
column ind format a25;
column tab format a25;
column typ format a5;
column tbs format a25;
select t.table_name "Tab"
,decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap','FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Typ"
,t.index_name "Ind"
,c.column_name "Col"
,c.column_position "Pos"
,t.tablespace_name "Tbs"
from user_indexes t, user_ind_columns c
where t.table_name = c.table_name
and t.index_name = c.index_name
and t.index_type not in ('IOT - TOP','LOB')
order by t.table_name, t.index_name, c.column_position;
Tab Typ Ind Col Pos Tbs
------------------------- ----- ------------------------- -------------------- ---- ------
AUTHOR BTree XPK_AUTHOR AUTHOR_ID 1 DATA
AUTHOR BTree XUK_A_NAME NAME 1 DATA
COAUTHOR BTree XFK_CA_AUTHOR PUBLICATION_ID 1 DATA
COAUTHOR BTree XFK_CA_PUBLICATION COAUTHOR_ID 1 DATA
COAUTHOR BTree XPK_COAUTHOR COAUTHOR_ID 1 DATA
COAUTHOR BTree XPK_COAUTHOR PUBLICATION_ID 2 DATA
CRITIC BTree XPK_CRITIC CRITIC_ID 1 DATA
CRITIC BTree XUK_C_NAME NAME 1 DATA
EDITION BTree XFK_E_PUBLICATION PUBLICATION_ID 1 DATA
EDITION BTree XFK_E_PUBLISHER PUBLISHER_ID 1 DATA
EDITION BTree XPK_EDITION ISBN 1 DATA
PROFESSIONALREVIEWS BTree XFK_PR_CRITIC CRITIC_ID 1 DATA
PROFESSIONALREVIEWS BTree XFK_PR_PUBLISHER PUBLISHER_ID 1 DATA
PROFESSIONALREVIEWS BTree XPK_PROFESSIONALREVIEWS PUBLISHER_ID 1 DATA
PROFESSIONALREVIEWS BTree XPK_PROFESSIONALREVIEWS CRITIC_ID 2 DATA
PUBLICATION BTree XFK_P_AUTHOR AUTHOR_ID 1 DATA
PUBLICATION BTree XFK_P_PUBLISHER SUBJECT_ID 1 DATA
PUBLICATION BTree XPK_PUBLICATION PUBLICATION_ID 1 DATA
PUBLICATION BTree XUK_P_TITLE TITLE 1 DATA
PUBLISHER BTree XPK_PUBLISHER PUBLISHER_ID 1 DATA
PUBLISHER BTree XUK_P_NAME NAME 1 DATA
REVIEW BTree XPK_REVIEW REVIEW_ID 1 DATA
REVIEWER BTree XFK_RW_AUTHOR CRITIC_ID 1 DATA
REVIEWER BTree XFK_RW_CRITIC AUTHOR_ID 1 DATA
REVIEWER BTree XFK_RW_REVIEW REVIEW_ID 1 DATA
REVIEWER BTree XPK_REVIEWER REVIEWER_ID 1 DATA
SUBJECT BTree XPK_SUBJECT SUBJECT_ID 1 DATA
SUBJECT BTree XUK_S_NAME NAME 1 DATA
28 rows selected.
Similarly the following script can be used to examine constraints.
set wrap off linesize 132 pages 80;
column key format a10;
column pos format 990;
column col format a10;
column cons format a20;
column tab format a20;
column own format a10;
select decode(t.constraint_type,'P','Primary','R','Foreign','U','Alternate','Unknown') "Key"
,t.table_name "Tab"
,t.constraint_name "Cons"
,c.column_name "Col"
,c.position "Pos"
from user_constraints t, user_cons_columns c
where t.constraint_type in ('P','R','U')
and t.table_name = c.table_name
and t.constraint_name = c.constraint_name
order by t.table_name, t.constraint_type, c.position;
Key Tab Cons Col Pos
---------- -------------------- -------------------- ---------- ----
Primary AUTHOR XPK_AUTHOR AUTHOR_ID 1
Alternate AUTHOR XUK_A_NAME NAME 1
Primary COAUTHOR XPK_COAUTHOR COAUTHOR_I 1
Primary COAUTHOR XPK_COAUTHOR PUBLICATIO 2
Foreign COAUTHOR FK_CA_AUTHOR COAUTHOR_I 1
Foreign COAUTHOR FK_CA_PUBLICATION PUBLICATIO 1
Primary CRITIC XPK_CRITIC CRITIC_ID 1
Alternate CRITIC XUK_C_NAME NAME 1
Primary EDITION XPK_EDITION ISBN 1
Foreign EDITION FK_E_PUBLICATION PUBLICATIO 1
Foreign EDITION FK_E_PUBLISHER PUBLISHER_ 1
Primary PROFESSIONALREVIEWS XPK_PROFESSIONALREVI PUBLISHER_ 1
Primary PROFESSIONALREVIEWS XPK_PROFESSIONALREVI CRITIC_ID 2
Primary PUBLICATION XPK_PUBLICATION PUBLICATIO 1
Foreign PUBLICATION FK_P_AUTHOR AUTHOR_ID 1
Foreign PUBLICATION FK_P_SUBJECT SUBJECT_ID 1
Alternate PUBLICATION XUK_P_TITLE TITLE 1
Primary PUBLISHER XPK_PUBLISHER PUBLISHER_ 1
Alternate PUBLISHER XUK_P_NAME NAME 1
Primary REVIEW XPK_REVIEW REVIEW_ID 1
Foreign REVIEW FK_R_PUBLICATION PUBLICATIO 1
Primary REVIEWER XPK_REVIEWER REVIEWER_I 1
Foreign REVIEWER FK_RW_CRITIC CRITIC_ID 1
Primary SUBJECT XPK_SUBJECT SUBJECT_ID 1
Foreign SUBJECT FK_S_SUBJECT SUBJECT_ID 1
Alternate SUBJECT XUK_S_NAME NAME 1
26 rows selected.
Find all the Metadata views for a user using the following query. In Oracle9i there are 197 USER_ views so they are not listed here.
SELECT table_name FROM dictionary WHERE table_name LIKE 'USER_%';
Metadata views for user database objects can generally and simply be divided up as follows.
Performance views are used to assess, track and help performance. Performance views are mostly prefixed by a V$. Thus the view V$SGASTAT shows statistics for the SGA (shared global area). Find all performane views using the following query. Once again there are a large number of these views so they are not all listed here. The following query simply lists all performance views available to the Books schema user.
SELECT table_name FROM dictionary WHERE table_name LIKE 'V$%' ORDER BY 1; TABLE_NAME ------------------------------ V$ACTIVE_INSTANCES V$ACTIVE_SESS_POOL_MTH V$AW_CALC V$AW_OLAP V$AW_SESSION_INFO V$BH V$LOADISTAT V$LOADPSTAT V$LOCK_ACTIVITY V$MAX_ACTIVE_SESS_TARGET_MTH V$MLS_PARAMETERS V$NLS_PARAMETERS V$NLS_VALID_VALUES V$OPTION V$PARALLEL_DEGREE_LIMIT_MTH V$PQ_SESSTAT V$PQ_TQSTAT V$QUEUEING_MTH V$RSRC_CONSUMER_GROUP V$RSRC_CONSUMER_GROUP_CPU_MTH V$RSRC_PLAN V$RSRC_PLAN_CPU_MTH V$SESSION_CONNECT_INFO V$SESSION_LONGOPS V$TEMPORARY_LOBS V$TIMEZONE_NAMES V$VERSION 27 rows selected.