3 Oracle Data Dictionary returns all information about the columns of one’s own tables.
Each SQL query requires various internal accesses to the tables and views of the data dictionary.
The Oracle data dictionary is one of the most important components of the Oracle DBMS. Since the data dictionary itself consists of tables, Oracle has to generate numerous SQL
It contains all information about the structures and objects of the database such as tables, statements to check whether the SQL command issued by a user is correct and can be executed.
columns, users, data files etc. The data stored in the data dictionary are also often called
metadata. Although it is usually the domain of database administrators (DBAs), the data Example: The SQL query
dictionary is a valuable source of information for end users and developers. The data dictionary select ∗ from EMP
consists of two levels: the internal level contains all base tables that are used by the various where SAL > 2000;
DBMS software components and they are normally not accessible by end users. The external requires a verification whether (1) the table EMP exists, (2) the user has the privilege to access
level provides numerous views on these base tables to access information about objects and this table, (3) the column SAL is defined for this table etc.
structures at different levels of detail.
3.2 Data Dictionary Views
3.1 Data Dictionary Tables
The external level of the data dictionary provides users a front end to access information
An installation of an Oracle database always includes the creation of three standard Oracle relevant to the users. This level provides numerous views (in Oracle7 approximately 540)
users: that represent (a portion of the) data from the base tables in a readable and understandable
• SYS: This is the owner of all data dictionary tables and views. This user has the highest manner. These views can be used in SQL queries just like normal tables.
privileges to manage objects and structures of an Oracle database such as creating new
users. The views provided by the data dictionary are divided into three groups: USER, ALL, and DBA.
• SYSTEM: is the owner of tables used by different tools such SQL*Forms, SQL*Reports etc. The group name builds the prefix for each view name. For some views, there are associated
This user has less privileges than SYS. synonyms as given in brackets below.
• PUBLIC: This is a “dummy” user in an Oracle database. All privileges assigned to this
user are automatically assigned to all users known in the database.
• USER : Tuples in the USER views contain information about objects owned by the account
The tables and views provided by the data dictionary contain information about performing the SQL query (current user)
• users and their privileges, USER TABLES all tables with their name, number of columns, storage
• tables, table columns and their data types, integrity constraints, indexes, information, statistical information etc. (TABS)
• statistics about tables and indexes used by the optimizer, USER CATALOG tables, views, and synonyms (CAT)
• privileges granted on database objects, USER COL COMMENTS comments on columns
• storage structures of the database. USER CONSTRAINTS constraint definitions for tables
USER INDEXES all information about indexes created for tables (IND)
The SQL command USER OBJECTS all database objects owned by the user (OBJ)
select ∗ from DICT[IONARY]; USER TAB COLUMNS columns of the tables and views owned by the user
(COLS)
lists all tables and views of the data dictionary that are accessible to the user. The selected
USER TAB COMMENTS comments on tables and views
information includes the name and a short description of each table and view. Before issuing
USER TRIGGERS triggers defined by the user
this query, check the column definitions of DICT[IONARY] using desc DICT[IONARY] and set
USER USERS information about the current user
the appropriate values for column using the format command.
USER VIEWS views defined by the user
The query
• ALL : Rows in the ALL views include rows of the USER views and all information about
select ∗ from TAB; objects that are accessible to the current user. The structure of these views is analogous
retrieves the names of all tables owned by the user who issues this command. The query to the structure of the USER views.
select ∗ from COL;
23 24
ALL CATALOG owner, name and type of all accessible tables, views, and
synonyms
ALL TABLES owner and name of all accessible tables
ALL OBJECTS owner, type, and name of accessible database objects
ALL TRIGGERS . . .
ALL USERS ...
ALL VIEWS ...
• DBA : The DBA views encompass information about all database objects, regardless of the
owner. Only users with DBA privileges can access these views.
DBA TABLES tables of all users in the database
DBA CATALOG tables, views, and synonyms defined in the database
DBA OBJECTS object of all users
DBA DATA FILES information about data files
DBA USERS information about all users known in the database
25
