Oracle Data Dictionary Views and Meta-data

Perhaps you have heard the term meta-data^[1]. Meta-data is literally beyond data. In an Oracle database, meta-data is the description of your data.
Questions answered by meta-data include:

1. What are the names of my tables and columns?
2. What is the maximum size of this table?
3. Who can view this table?
4. When was this user created?

• Oracle Meta-data stored in Relational Tables
  Oracle stores meta-data in its own set of relational tables. These tables cannot be seen or modified. However, Oracle provides a set of views called the data dictionary views to allow you access to the information. Data dictionary views contain rows and columns of data, just like an ordinary view. This means that you can use them in SQL queries. The name of the data dictionary view helps you decipher what the view contains. There are three general groups of views, based on the first word in the name.

Nearly all the data dictionary views come in sets of three views, one for USER, one for ALL, and one for DBA.
For example, one set of views is USER_VIEWS, another is ALL_VIEWS, and the third is DBA_VIEWS.

Oracle maintains a data dictionary that records metadata about

1. each object,
2. the object owner,
3. a definition, and
4. related privileges.

For objects that require physical storage space of their own, Oracle will allocate space within a tablespace. Tablespaces can be either dictionary managed or locally managed. In a dictionary-managed tablespace, space management is recorded in the data dictionary. In a locally managed tablespace (the default), Oracle maintains a bitmap in each datafile of the tablespace to track space availability.Only quotas are managed in the data dictionary, dramatically reducing the contention for data dictionary tables. Prior to performing a direct upgrade, you should analyze the data dictionary tables. During the upgrade process to Oracle Database 11g, the data dictionary will be analyzed if it has not been analyzed already, so performing this step in advance will aid the performance of the upgrade.

• Aliases in View Creation
  Internally, Oracle works with all column and table names in uppercase. This is how they are stored in its data dictionary, and this is how it always expects them to be. When aliases are typed to create a view, they should always exist without quotation marks around them. Putting double quotation marks around an alias can force the column name stored internally by Oracle to be in mixed case. If you do this, Oracle will not be able to find the column when you execute a select unless you enclose the column name within quotes during all your queries. Never use double quotation marks in creating aliases for a view.

The following table lists a few of the hundreds of data dictionary views available to you.

Data dictionary view name	Description
ALL_CATALOG	All tables, views, and synonyms that the current user is allowed to access
ALL_USERS	This includes names of all Oracle users. This view is available to the Database Administrator. Passwords are never displayed.
DBA_CONSTRAINTS	All constraints in the database
DBA_ROLES	All roles that exist in the database
USER_CATALOG	Tables, sequences, views, and other objects owned by the current user
USER_ROLE_PRIVS	Roles assigned to the current user
USER_TAB_COL_STATISTICS	Low, high, and average values in a table's columns
USER_TAB_PRIVS	Privileges granted to the current user
USER_TAB_PRIVS_MADE	Privileges granted to others by the current user
USER_TABLES	This shows tables owned by the current user. This view includes statistics such as the number of rows and space allocated and space used.
USER_TYPES	Object types created by the current user

You can use the DESC command within SQL*Plus to display a list of the columns contained in any table or view, including the above data dictionary views. In the next lesson, you will begin looking at database tables using SQL*Plus.
The following text discusses five data dictionary views that can be answered by that view.

This page shows the elements of five data dictionary views .
The correct matches are:

1. ALL_CATALOG, What tables can I access that were created by other Oracle users?
2. DBA_ROLES, How many roles were created in the entire database?
3. USER_ROLE_PRIVS, What roles do I have assigned to me?
4. USER_TAB_COL_STATISTICS, In my DAILY_SALES table, what is the average value in the column called TOTAL_SALES?
5. USER_TAB_PRIVS_MADE, Did I grant the SELECT privilege to the MANAGER role?

• Dictionary Cache Concepts:
  Information stored in the data dictionary cache includes
  1. usernames,
  2. segment information,
  3. profile data,
  4. tablespace information, and
  5. sequence numbers.
  The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs

[1]Meta-data: Information (data) about database structures. For example, the meta-data about a table includes its name, its column names, and the tablespace in which it resides.