Data Dictionary

Oracle Dynamic Performance Views

Oracle Data Dictionary Tables

Data Dictionary

A repository of information describing the data in the database. This includes the name and structures of all tables, constraints, indices, views, synonyms, sequences triggers, procedures, functions, and packages.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’
and OWNER = ‘SCOTT’

 

DBA_TABLES
select TABLE_NAME,LAST_ANALYZED,ROW_LEN, AVG_ROW_LEN,NUM_ROWS,,STATUS,PCTFREE, PCTUSED,
CACHE, BUFFER_POOL,
from dba_tables
where table_name = ‘EMPLOYEE’

SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE =’FKHALID’

 

Table and Index

SELECT DT.TABLE_NAME, DI.INDEX_NAME,DT.CACHE TBL_CACHE, DT.BUFFER_POOL TBL_BUFFER_POOL,
BLEVEL,DI.LOGGING, DI.LAST_ANALYZED "INDX LAST ANALYZED"
FROM DBA_TABLES DT, DBA_INDEXES DI
WHERE DT.OWNER=’HR_DUP’ AND DT.TABLE_NAME= DI.TABLE_NAME

CONSTRAINTS

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = ‘EMPLOYEES’

SELECT Owner,Constraint_name, Table_name, Column_name 
FROM dba_cons_columns
where  table_name = ‘EMPLOYEES’
and owner = ‘HR_DUP’

 

SESSIONS

SELECT sid,serial#,process,username,status,machine,terminal,
lockwait,machine,terminal,blocking_session,blocking_session_status,state,
program,type,logon_time
FROM v$session

  

 

 

Some Useful Data Dictionary Tables

USER_TABLES
Lists each table that belongs to your Oracle user.

USER_TAB_COMMENTS
Shows comments on the tables and views.

USER_TAB_COLUMNS
Tells you the names, data types, default values, etc. of each column in each table.

USER_COL_COMMENTS
Shows comments on the columns.

USER_CONSTRAINTS
Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc.

USER_CONS_COLUMNS
Maps constraints to columns (since a constraint can act on one or many columns).

USER_INDEXES
Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys).

USER_IND_COLUMNS
Maps indexes to columns.

USER_VIEWS
Lists all views, along with the text used to originally create them.

USER_SYNONYMS
Lists the synonyms and original table names.

USER_SEQUENCES
Lists all sequences, including min value, max value, and amount by which to increment.

USER_TRIGGERS
Contains trigger names, criteria for activating each trigger, and the code that is run.

USER_SOURCE
Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: