Database Reference
In-Depth Information
19.7
Data Dictionary Views (Metadata)
Metadata views applicable for specific chapters are presented at the end of
those chapters. This section describes what the metadata views are. Oracle
Database contains a set of predefined views that contain information about
tables, views, users, storage, and more.
There are two sets of metadata views. More distinctly, there are metadata
views and performance views. The metadata views look at database dictio-
nary data or data about the data. The data about the data are the tables and
their columns, indexes, clusters, and so on. The meaning of the word
meta-
data
is data describing data. A database's metadata is all the objects created in
a database to contain your actual data about your business and applications.
In general, metadata views are named as ALL_name, DBA_name, and
USER_name. ALL_ implies all users, DBA_ implies only accessible by a
DBA user, and USER_ implies the current user. Performance views are
generally named as V$name. V$ views store and track all types of perfor-
mance statistics and data in the database. Performance views relate to tun-
ing an Oracle Database
2
and are largely out of the scope for this topic on
Oracle SQL.
All of the metadata views overlay and access the metadata from system
tables stored either in the SYS, SYSTEM, or SYSAUX schemas. The data-
base system tables are complex and can sometimes have cryptic names and
even more cryptic column names.
As a DBA or programmer, you need some of this information. How do
you find the names of users or tables in the database, for example? Oracle
Database provides a set of views that are easily accessible, with readable view
names and column names. These views are called, collectively, the
data dic-
tionary views
.
So metadata or data dictionary views can be roughly divided into four
groups, based on the prefix of the name of the view:
DBA_name.
These require special privileges to view. They generally
give information covering the entire database system. For example,
DBA_TABLES lists all tables created by any user in the database.
DBA_name views require DBA privileges to access.
USER_name.
These are accessible by any user. They give informa-
tion about the user and objects owned by the user, the currently con-
nected user. For example, USER_TABLES lists all tables created by
