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