Part 12
SQL for Oracle System Tables
System Tables
All “truly” relational systems must have the system information stored in tables.
These system tables are accessible in the same way as any other table.
This means that a relational system will have:
A
table containing the list of tables that make up the data dictionary. In Oracle this table is
named
DICTIONARY
A
table containing the columns in each table of the data dictionary. In Oracle this table is
named
DICT_COLUMNS
It will also have a table containing a list of all tables, and a table containing a list of all columns in all tables. However, these can be found by examining the data dictionary
Examining the Data Dictionary
The following queries are useful for examining the data dictionary:
SQL> describe dictionary
Name
Null?
Type
-------------------------------
--------
----
TABLE_NAME
VARCHAR2(30)
COMMENTS
VARCHAR2(2000)
SQL> |
Set pagesize
60 |
|
Column table_name
format a22 |
|
Column comments format
a57 |
|
Select * from
DICTIONARY |
|
Where comments not
like ‘%ynonym for %’ |
|
Order by
table_name; |
|
|
SQL> |
Set pagesize
60 |
|
Column table_name
format a22 |
|
Column column_name
format a22 |
|
Column comments format
a34 |
|
Select * from
DICT_COLUMNS |
|
Where table_name like
‘USER_%’ |
|
Order by table_name,
column_name; |
|
|
SQL> |
Set pagesize
60 |
|
Column table_name
format a22 |
|
Column owner format
a12 |
|
Column comments format
a44 |
|
Select
all_tables.table_name, all_tables.owner,
comments |
|
From all_tables,
all_tab_comments |
|
Where
all_tables.table_name =
all_tab_comments.table_name |
|
and all_tables.owner =
all_tab_comments.owner |
|
order by owner,
table_name; |
Contents of the DICTIONARY table
Table
Name |
Comments |
ALL_CATALOG |
All tables, views,
synonyms, sequences accessible to the user |
ALL_COL_COMMENTS |
Comments on columns of
accessible tables and views |
ALL_COL_PRIVS |
Grants on columns for
which the user is the grantor, grantee, owner, or an enabled role or
PUBLIC is the grantee |
ALL_COL_PRIVS_MADE |
Grants on columns for
which the user is owner or grantor |
ALL_COL_PRIVS_RECD |
Grants on columns for
which the user, PUBLIC or enabled role is the
grantee |
ALL_CONSTRAINTS |
Constraint definitions
on accessible tables |
ALL_CONS_COLUMNS |
Information about
accessible columns in constraint definitions |
ALL_DB_LINKS |
Database links
accessible to the user |
ALL_DEF_AUDIT_OPTS |
Auditing options for
newly created objects |
ALL_DEPENDENCIES |
Dependencies to and
from objects accessible to the user |
ALL_ERRORS |
Current errors on
stored objects that user is allowed to create |
ALL_INDEXES |
Descriptions of
indexes on tables accessible to the user |
ALL_IND_COLUMNS |
COLUMNs comprising
INDEXes on accessible TABLES |
ALL_OBJECTS |
Objects accessible to
the user |
ALL_REFRESH |
All the refresh groups
that the user can touch |
ALL_REFRESH_CHILDREN |
All the objects in
refresh groups, where the user can touch the
group |
ALL_SEQUENCES |
Description of
SEQUENCEs accessible to the user |
ALL_SNAPSHOTS |
Snapshots the user can
look at |
ALL_SOURCE |
Current source on
stored objects that user is allowed to create |
ALL_SYNONYMS |
All synonyms
accessible to the user |
ALL_TABLES |
Description of tables
accessible to the user |
ALL_TAB_COLUMNS |
Columns of all tables,
views and clusters |
ALL_TAB_COMMENTS |
Comments on tables and
views accessible to the user |
ALL_TAB_PRIVS |
Grants on objects for
which the user is the grantor, grantee, owner, or an enabled role or
PUBLIC is the grantee |
ALL_TAB_PRIVS_MADE |
User's grants and
grants on user's objects |
ALL_TAB_PRIVS_RECD |
Grants on objects for
which the user, PUBLIC or enabled role is the
grantee |
ALL_TRIGGERS |
Triggers accessible to
the current user |
ALL_TRIGGER_COLS |
Column usage in user's
triggers or in triggers on user's tables |
ALL_USERS |
Information about all
users of the database |
ALL_VIEWS |
Text of views
accessible to the user |
AUDIT_ACTIONS |
Description table for
audit trail action type codes.
Maps action type numbers to action type
names |
COLUMN_PRIVILEGES |
Grants on columns for
which the user is the grantor, grantee, owner, or an enabled role or
PUBLIC is the grantee |
DBA_2PC_NEIGHBORS |
information about
incoming and outgoing connections for pending
transactions |
DBA_2PC_PENDING |
info about distributed
transactions awaiting recovery |
DBA_AUDIT_EXISTS |
Lists audit trail
entries produced by AUDIT NOT EXISTS and AUDIT
EXISTS |
DBA_AUDIT_OBJECT |
Audit trail records
for statements concerning objects, specifically: table, cluster, view,
index, sequence, [public]
database link, [public] synonym, procedure, trigger, rollback segment,
tablespace, role, user |
DBA_AUDIT_STATEMENT |
Audit trail records
concerning grant, revoke,
audit, noaudit and alter system |
DBA_AUDIT_TRAIL |
All audit trail
entries |
DBA_CATALOG |
All database Tables,
Views, Synonyms, Sequences |
DBA_CLUSTERS |
Description of all
clusters in the database |
DBA_CLU_COLUMNS |
Mapping of table
columns to cluster columns |
DBA_COL_COMMENTS |
Comments on columns of
all tables and views |
DBA_COL_PRIVS |
All grants on columns
in the database |
DBA_CONSTRAINTS |
Constraint definitions
on all tables |
DBA_CONS_COLUMNS |
Information about
accessible columns in constraint definitions |
DBA_DATA_FILES |
Information about
database files |
DBA_DB_LINKS |
All database links in
the database |
DBA_DEPENDENCIES |
Dependencies to and
from objects |
DBA_ERRORS |
Current errors on all
stored objects in the database |
DBA_EXP_FILES |
Description of export
files |
DBA_EXP_OBJECTS |
Objects that have been
incrementally exported |
DBA_EXP_VERSION |
Version number of the
last export session |
DBA_EXTENTS |
Extents comprising all
segments in the database |
DBA_FREE_SPACE |
Free extents in all
tablespaces |
DBA_INDEXES |
Description for all
indexes in the database |
DBA_IND_COLUMNS |
COLUMNs comprising
INDEXes on all TABLEs and CLUSTERs |
DBA_JOBS |
All jobs in the
database |
DBA_JOBS_RUNNING |
All jobs in the
database which are currently running, join v$lock and
job$ |
DBA_OBJECTS |
All objects in the
database |
DBA_OBJECT_SIZE |
Sizes, in bytes, of
various pl/sql objects
|
DBA_OBJ_AUDIT_OPTS |
Auditing options for
all tables and views |
DBA_PRIV_AUDIT_OPTS |
Describes current
system privileges being audited across the system and by
user |
DBA_PROFILES |
Display all profiles
and their limits |
DBA_RCHILD |
All the children in
any refresh group. This view
is not a join. |
DBA_REFRESH |
All the refresh
groups |
DBA_REFRESH_CHILDREN |
All the objects in
refresh groups |
DBA_RGROUP |
All refresh
groups. This view is not a
join. |
DBA_ROLES |
All Roles which exist
in the database |
DBA_ROLE_PRIVS |
Roles granted to users
and roles |
DBA_ROLLBACK_SEGS |
Description of
rollback segments |
DBA_SEGMENTS |
Storage allocated for
all database segments |
DBA_SEQUENCES |
Description of all
SEQUENCEs in the database |
DBA_SNAPSHOTS |
All snapshots in the
database |
DBA_SNAPSHOT_LOGS |
All snapshot logs in
the database |
DBA_SOURCE |
Source of all stored
objects in the database |
DBA_STMT_AUDIT_OPTS |
Describes current
system auditing options across the system and by
user |
DBA_SYNONYMS |
All synonyms in the
database |
DBA_SYS_PRIVS |
System privileges
granted to users and roles |
DBA_TABLES |
Description of all
tables in the database |
DBA_TABLESPACES |
Description of all
tablespaces |
DBA_TAB_COLUMNS |
Columns of all tables,
views and clusters |
DBA_TAB_COMMENTS |
Comments on all tables
and views in the database |
DBA_TAB_PRIVS |
All grants on objects
in the database |
DBA_TRIGGERS |
All triggers in the
database |
DBA_TRIGGER_COLS |
Column usage in all
triggers |
DBA_TS_QUOTAS |
Tablespace quotas for
all users |
DBA_USERS |
Information about all
users of the database |
DBA_VIEWS |
Text of all views in
the database |
DICTIONARY |
Description of data
dictionary tables and views |
DICT_COLUMNS |
Description of columns
in data dictionary tables and views |
GLOBAL_NAME |
global database
name |
INDEX_HISTOGRAM |
statistics on keys
with repeat count |
INDEX_STATS |
statistics on the
b-tree |
RESOURCE_COST |
Cost for each
resource |
ROLE_ROLE_PRIVS |
Roles which are
granted to roles |
ROLE_SYS_PRIVS |
System privileges
granted to roles |
ROLE_TAB_PRIVS |
Table privileges
granted to roles |
SESSION_PRIVS |
Privileges which the
user currently has set |
SESSION_ROLES |
Roles which the user
currently has enabled |
TABLE_PRIVILEGES |
Grants on objects for
which the user is the grantor, grantee, owner, or an enabled role or
PUBLIC is the grantee |
USER_AUDIT_OBJECT |
Audit trail records
for statements concerning objects, specifically: table, cluster, view,
index, sequence, [public]
database link, [public] synonym, procedure, trigger, rollback segment,
tablespace, role, user |
USER_AUDIT_STATEMENT |
Audit trail records
concerning grant, revoke,
audit, noaudit and alter system |
USER_AUDIT_TRAIL |
Audit trail entries
relevant to the user |
USER_CATALOG |
Tables, Views,
Synonyms and Sequences owned by the user |
USER_CLUSTERS |
Descriptions of user's
own clusters |
USER_CLU_COLUMNS |
Mapping of table
columns to cluster columns |
USER_COL_COMMENTS |
Comments on columns of
user's tables and views |
USER_COL_PRIVS |
Grants on columns for
which the user is the owner, grantor or
grantee |
USER_COL_PRIVS_MADE |
All grants on columns
of objects owned by the user |
USER_COL_PRIVS_RECD |
Grants on columns for
which the user is the grantee |
USER_CONSTRAINTS |
Constraint definitions
on user's own tables |
USER_CONS_COLUMNS |
Information about
accessible columns in constraint definitions |
USER_DB_LINKS |
Database links owned
by the user |
USER_DEPENDENCIES |
Dependencies to and
from a users objects |
USER_ERRORS |
Current errors on
stored objects owned by the user |
USER_EXTENTS |
Extents comprising
segments owned by the user |
USER_FREE_SPACE |
Free extents in
tablespaces accessible to the user |
USER_INDEXES |
Description of the
user's own indexes |
USER_IND_COLUMNS |
COLUMNs comprising
user's INDEXes or on user's TABLES |
USER_JOBS |
All jobs owned by this
user |
USER_OBJECTS |
Objects owned by the
user |
USER_OBJECT_SIZE |
Sizes, in bytes, of
various pl/sql objects
|
USER_OBJ_AUDIT_OPTS |
Auditing options for
user's own tables and views |
USER_REFRESH |
All the refresh
groups |
USER_REFRESH_CHILDREN |
All the objects in
refresh groups, where the user owns the refresh
group |
USER_RESOURCE_LIMITS |
Display resource limit
of the user |
USER_ROLE_PRIVS |
Roles granted to
current user |
USER_SEGMENTS |
Storage allocated for
all database segments |
USER_SEQUENCES |
Description of the
user's own SEQUENCEs |
USER_SNAPSHOTS |
Snapshots the user can
look at |
USER_SNAPSHOT_LOGS |
All snapshot logs
owned by the user |
USER_SOURCE |
Source of stored
objects accessible to the user |
USER_SYNONYMS |
The user's private
synonyms |
USER_SYS_PRIVS |
System privileges
granted to current user |
USER_TABLES |
Description of the
user's own tables |
USER_TABLESPACES |
Description of
accessible tablespaces |
USER_TAB_COLUMNS |
Columns of user's
tables, views and clusters |
USER_TAB_COMMENTS |
Comments on the tables
and views owned by the user |
USER_TAB_PRIVS |
Grants on objects for
which the user is the owner, grantor or
grantee |
USER_TAB_PRIVS_MADE |
All grants on objects
owned by the user |
USER_TAB_PRIVS_RECD |
Grants on objects for
which the user is the grantee |
USER_TRIGGERS |
Triggers owned by the
user |
USER_TRIGGER_COLS |
Column usage in user's
triggers |
USER_TS_QUOTAS |
Tablespace quotas for
the user |
USER_USERS |
Information about the
current user |
USER_VIEWS |
Text of views owned by
the user |