Handout 1a

Oracle Dictionary Tables of Interest

TABLE                   REMARKS
----------------------  -------------------------------------------
ALL_CATALOG             All tables, views, synonyms, sequences
                        accessible to the user

ALL_COL_COMMENTS        Comments on columns of accessible tables
                        and views

ALL_COL_GRANTS_MADE     Grants on columns for which the user is
                        owner or grantor

ALL_COL_GRANTS_RECD     Grants on columns for which the user or 
                        PUBLIC is the grantee

ALL_INDEXES             Descriptions of indexes on tables accessible
                        to the user

ALL_TABLES              Description of tables accessible to the user

ALL_TAB_COMMENTS        Comments on tables and views accessible to
                        the user

ALL_TAB_GRANTS_MADE     User's grants and grants on user's objects

ALL_TAB_GRANTS_RECD     Grants on objects for which the user or
                        PUBLIC is the grantee

ALL_USERS               Information about all users of the database

ALL_VIEWS               Text of views accessible to the user

COLUMN_PRIVILEGES       Grants on columns for which the user is the
                        grantor, grantee, or owner, or PUBLIC is the
                        grantee

DICTIONARY              Description of data dictionary tables and
                        views

DICT_COLUMNS            Description of columns in data dictionary
                        tables and views

USER_CATALOG            Tables, views, synonyms, sequences
                        accessible to the user

USER_COL_COMMENTS       Comments on columns of user's tables and
                        views

USER_FREE_SPACE         Free extents in tablespaces accessible to the
                        user

USER_INDEXES            Description of the user's own indexes

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_GRANTS_MADE    All grants on objects owned by the user

USER_TAB_GRANTS_RECD    Grants on objects for which the user is the
                        grantee

USER_USERS              Information about the current user

USER_VIEWS              Text of views owned by the user

Oracle Data Dictionary (System Catalog)*

Overview of Data Dictionary

Contains all the information that is used to manage the objects in the database
ORACLE's "Yellow Pages"
- grouped by function
Used by:
- DBA
- developers
- users

Common Views

DICTIONARY (DICT) & DICT_COLUMNS
Things you select from: tables, columns, views, synonyms & sequences
Constraints & comments
Indexes & clusters
Database links & snapshots
Triggers, procedures, functions, & packages
Space allocation & usage
Users & privileges
Roles
Auditing
Miscellaneous

Naming Conventions

Most object names begin with one of three prefixes:

USER
ALL
DBA

What is in each category?

USER
- usually information about objects owned by the account performing the query
ALL
- includes stuff from USER
- plus other objects on which privileges have been granted to PUBLIC or to the user
DBA
- all database objects, regardless of owner

How do you know the column names, etc.?

describe tablename;

DICTIONARY (DICT)

Descriptions for the objects that that make up the Oracle dictionary
DICT is a synonym for DICTIONARY

Example

list the names of all data dictionary views that have the string "TABLES" in their name
```
select Table_Name, Comments

from DICT

where Table_Name like '%TABLES%';
```

TABLE_NAME COMMENTS

ALL_TABLES Description of tables accessible to the user

DBA_TABLES Description of all tables in the database

DBA_TABLESPACES Description of all tablespaces

USER_TABLES Description of the user's own tables

USER_TABLESPACES Description of accessible tablespaces

DICT_COLUMNS

Has a comment column for each column of each table
Query to determine which data dictionary (DD) views you need

Example

Which DD views show space allocation (BLOCKS) information for your database objects?

select Table_Name
from DICT_COLUMNS
where Column_Name = 'BLOCKS'
and Table_Name like 'USER':

TABLE_NAME
------------------
USER_EXTENTS
USER_FREE_SPACE
USER-SEGMENTS
USER_TABLES
USER_TSQUOTAS

Catalog tables' contents

Tables
Columns
Views
Synonyms
Sequences

Types of catalog tables

USER_CATALOG
ALL_CATALOG
DBA_CATALOG

User's 'catalog'

The user's catalog is the set of objects owned by a user
One catalog per user
USER_CATALOG table
Synonym: CAT

All catalog

Contains all the stuff from user's catalog
Plus PUBLIC objects
Plus any other objects to which you have been granted access
ALL_CATLOG
Has an owner column

DBA catalog

Contains all tables, views, sequences & synonyms in the database
DBA_CATALOG
Has an owner column

Objects: USER_OBJECTS (OBJ)

Contains information for ALL objects belonging to the user

clusters
database links
functions
indexes
packages
package bodies
procedures
sequences
synonymns
tables
triggers
views

ALL_OBJECTS

Same information as USER_OBJECTS
Plus PUBLIC objects
Plus any other objects to which you have been granted access
Has ownner column

DBA_OBJECTS

Same information as USER_OBJECTS

* from here

TABLE_NAME	COMMENTS
ALL_TABLES	Description of tables accessible to the user
DBA_TABLES	Description of all tables in the database
DBA_TABLESPACES	Description of all tablespaces
USER_TABLES	Description of the user's own tables
USER_TABLESPACES	Description of accessible tablespaces