Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.

The Data Dictionary tables contain meta data on objects, privileges, roles, users etc. Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary. All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.

At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)

Through static dictionary views users can get at information kept in data dictionary tables. And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.

But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime. Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views. As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!

This is a compilation of most important static dictionary and dynamic performance views. There are hundreds more. The views in this compilation you will most likely use in your daily life as a DBA. So learn them well.

STATIC DATA DICTIONARY VIEWS
_____________________________________

There are three different kinds of static data dictionary views. Views with the prefix DBA_, ALL_ or USER_. Lets query table names.

SELECT table_name FROM dba_tables

returns all table_names existing in the database. The same query on ALL_TABLES returns only tables on which the user issuing the query has privileges. And on USER_TABLES the query returns only tables owned by the user issuing the query.

Statc data dictionary views join together one or more data dictionary tables. Both views and tables are owned by user SYS and reside in the SYSTEM tablespace. Due to public synonyms on static dictionary views you don’t have to type SYS.DBA_TABLES but only DBA_TABLES.

To see all available column names of a view use SQL> desc {view name}

Data Dictionary Views for Managing Users, Roles, Privileges and Resources

asd

asd

DBA_USERS
Information about all users of the database. This query returns all users who own tables:

SELECT DISTINCT u.username FROM dba_users u, dba_objects o WHERE u.username = o.owner AND o.object_type=’TABLE’;

In Oracle 11g there is a new dictionary view DBA_USERS_WITH_DEFPWD which lists all users with default passwords. This query returns all users with default passwords whose accounts are open:

SELECT username FROM dba_users_with_defpwd MINUS SELECT username FROM DBA_USERS WHERE account_status IN (’LOCKED’, ‘EXPIRED & LOCKED’);

DBA_ROLES
This Query returns all roles defined in the database, except the PUBLIC role:

SELECT role FROM dba_roles;

DBA_ROLE_PRIVS
Roles granted to users or roles. This query returns all roles granted to user HPFUCHS:

SELECT granted_role FROM dba_role_privs WHERE grantee=’HPFUCHS’;

ROLE_ROLE_PRIVS
Roles granted to roles. This query returns all roles granted to DBA role:

SELECT granted_role FROM role_role_privs WHERE role=’DBA’;

ROLE_TAB_PRIVS
Privileges on database objects granted to roles. The column TABLE_NAME contains not only table names but object names (tables, views, procedures, …). ROLE_TAB_PRIVS shows only privileges on database objects on which the user has privileges (there is no DBA_, ALL_ and USER_). This query returns all roles with privileges on table HPFUCHS.TAB1:

SELECT role, privilege FROM role_tab_privs WHERE table_name=’TAB1′ AND owner=’HPFUCHS’ ODER BY role, privilege;

ROLE_SYS_PRIVS
System privileges granted to roles. This query returns all system privileges granted to HPADM role:

SELECT privilege FROM role_sys_privs WHERE role=’HPADM’;

DBA_SYS_PRIVS
System privileges granted to roles and users. This query returns all users whom system privileges were granted directly (not trough roles):

SELECT grantee AS user_name FROM dba_sys_privs MINUS SELECT role AS user_name FROM role_sys_privs;

DBA_TAB_PRIVS
Privileges on database objects granted to users or roles. The column TABLE_NAME contains not only table names but object names (tables, views, procedures, …). Therefore DBA_TAB_PRIVS contains all privileges on any object granted to any user/role; a very important dictionary view. This query returns all users who have EXECUTE privilege on HPFUCHS.HPPROC procedure:

select grantee from dba_tab_privs where table_name=’HPPROC’ and owner=’HPFUCHS’ and privilege=’EXECUTE’;

DBA_COL_PRIVS
Privileges on columns granted to roles and users. Privileges on whole tables are not contained in this view. This query returns OWNER.TABLE.COLUMN and PRIVILEGE for all column privileges granted to user or role HPFUCHS:

SELECT owner || ‘.’ || table_name || ‘.’ || column_name AS column_name, privilege FROM dba_col_privs WHERE grantee=’HPFUCHS’ ORDER BY owner, table_name;

DBA_PROFILES, DBA_POLICIES, DBA_RSRC_PLANS, DBA_OUTLINES

Data Dictionary Views for Managing Tables, Views, Indices and Constraints

[Grafik?]

DBA_TABLES
Information on all tables in the database. This query returns OWNER.TABLE for all tables in USERS tablespace:

SELECT owner || ‘.’ || table_name AS table_name FROM dba_tables WHERE tablespace_name=’USERS’ ORDER BY owner, table_name;

DBA_TAB_MODIFICATIONS
Lists tables which have been modified by DML operations since last statistics collection.

SELECT table_owner || ‘.’ || table_name AS tab, inserts, updates, deletes, timestamp FROM dba_tab_modifications ORDER BY timestamp, table_owner, table_name;

DBA_TAB_COLUMNS

Information on columns of tables, views and clusters. This query returns OWNER.OBJECT.COLUMN for all tables, views and clusters which contain large objects (LOB):

SELECT owner || ‘.’ || table_name || ‘.’ || column_name AS col, data_type FROM dba_tab_columns WHERE data_type LIKE ‘%LOB’ ORDER BY owner, table_name, column_name;

DBA_EXTERNAL_TABLES
Information on externl tables.

DBA_VIEWS
Information on all views in the database. Source code of views cannot be retrieved through DBA_SOURCES. This query returns the source code of a view:

SELECT text FROM dba_views WHERE view_name=’DBA_VIEWS’;

DBA_MVIEWS

asd

DBA_INDEXES

asd

DBA_IND_COLUMNS

asd

INDEX_STATS

asd

DBA_CONSTRAINTS

asd

DBA_CONS_COLUMNS

asd

Data Dictionary Views for Managing Packages, Procedures, Functions, Triggers and Types

DBA_OBJECTS

asd

DBA_SOURCE

asd

DBA_TRIGGERS

asd

Data Dictionary Views for Managing Sequences, Synonyms and DB-Links

DBA_SEQUENCES

asd

DBA_SYNONYMS

asd

DBA_DB_LINKS

asd

Data Dictionary Views for Managing Storage

The smallest unit of storage Oracle allocates is a block. . More information on blocks, extents and segments can be found here.

[Grafik]

A table consists of its table definition and its actual data which is stored in the tables segment. One table has one segment *2). In terms of storage you could say a table is a segment. Segments are stored in tablespaces. A segment can be stored in one tablespace which can hold many segments. And a tablespace consists of one or more datafiles to store data on disk. Logically data is stored in tablespaces and physically it is stored in datafiles. More information on tablespaces and datafiles can be found here.

As physical storage is needed more datafiles can be added to a tablespace.

ALTER TABLESPACE users ADD DATAFILE ‘/orats/hpdb/users03.dbf’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

These data dictionary views

DBA_EXTENTS

asd

DBA_SEGMENTS

asd

DBA_FREE_SPACE

asd

DBA_DATA_FILES

asd

DBA_TEMP_FILES

asd

DBA_TABLESPACES

asd

Data Dictionary Views for Managing the Scheduler

[Grafik]

DBA_SCHEDULER_JOBS

asd

DBA_SCHEDULER_PROGRAMS

asd

DBA_SCHEDULER_SCHEDULES

asd

DBA_SCHEDULER_WINDOWS

asd

DBA_SCHEDULER_RUNNING_JOBS

asd

DBA_SCHEDULER_JOB_RUN_DETAILS

asd

DBA_SCHEDULER_JOB_LOG

asd

DBA_JOBS

asd

DBA_DATAPUMP_JOBS

asd

Data Dictionary Views for Managing Transactions

DBA_BLOCKERS

asd

DBA_WAITERS

asd

Data Dictionary Views for Auditing

DBA_AUDIT_OBJECT

asd

DBA_AUDIT_SESSION

asd

DBA_AUDIT_STATEMENT

asd

DBA_AUDIT_TRAIL

asd

DBA_COMMON_AUDIT_TRAIL

asd

DBA_AUDIT_POLICIES (fine-grained auditing)

asd

DBA_AUDIT_POLICY_COLUMNS (fine-grained auditing)

asd

DBA_FGA_AUDIT_TRAIL (fine-grained auditing)

asd

Data Dictionary Views for Managing Advisors, AWR and ADDM

asd

Other Data Dictionary Views

asd

DYNAMIC PERFORMANCE VIEWS
_____________________________________________

select maximum_connections as max_con, maximum_sessions as max_ses, servers_started as srvs_started, servers_terminated as srvs_termted, servers_highwater as srvrs_highwtr from v$shared_server_monitor;

MAX_CON    MAX_SES SRVS_STARTED SRVS_TERMTED SRVRS_HIGHWTR
———- ———- ———— ———— ————-
568        526           26           26            21

AND ALL OTHER VIEWS?
_____________________________________________

When you’re not sure about the name of a certain view you can look it up.

SELECT table_name, comments FROM dict WHERE table_name LIKE ‘%PRIV%’;

FOOTNOTES
_____________________________________________

*1) Since Oracle Database 10g a snapshot of information contained in dynamic performance tables is saved to the Automatic Workload Repository (AWR) on a hourly interval. AWR is persistent.

*2) One table has one segments. Unless it is a partitioned table. In this case every partition has one segment.