Name several data dictionary views and their purpose.
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:
What are the names of my tables and columns?
What is the maximum size of this table?
Who can view this table?
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.
1) Views that begin with USER list only those objects that you have created. For example, USER_INDEXES lists all the indexes that you created.
2) Views that begin with ALL contain information about all objects of that type that you either created yourself or to which you have access.
3) Views that begin with DBA can only be used by an Oracle user that has been given the DBA role. These views contain information about objects created by every user in the database. In addition,
these views tend to contain more detailed information than similar views that begin with ALL or USER.
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
each object,
the object owner,
a definition, and
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.
Data Dictionary Views
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.
Five Data Dictionary views
This page shows the elements of five data dictionary views .
The correct matches are:
ALL_CATALOG, What tables can I access that were created by other Oracle users?
DBA_ROLES, How many roles were created in the entire database?
USER_ROLE_PRIVS, What roles do I have assigned to me?
USER_TAB_COL_STATISTICS, In my DAILY_SALES table, what is the average value in the column called TOTAL_SALES?
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
usernames,
segment information,
profile data,
tablespace information, and
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.
