Database Reference
In-Depth Information
currently logged into. For example, if you wanted to see if there was a table called
MY_
DATA
in your schema, you could query the
USER_TABLES
view, as shown here:
Select table_name from user_tables;
CDB_*
With the advent of Oracle Database 12
c
, Oracle has added a completely new set
of data dictionary views. These views help you to deal with Oracle multitenant databases.
The views are prefixed with
CDB
, and they allow you to look at the various pluggable data-
bases within the Oracle container database. We will cover these views in a lot more detail
in chapter 12.
Notice in the example query against
USER_TABLES
that we removed the
owner
column. It is quite common that the
DBA_*
and
ALL_*
views will have
an
owner
column but that the
USER_*
views will not. This is because the
user in the
USER_*
views is assumed to be the user you are logged in as.
Dynamic Performance Data Dictionary Views
The
dynamic performance data dictionary
views
typically start with a
V$
prefix, such as
V$DATABASE
or
V$SESSION
. The views are often
used for database monitoring and tuning, but there are times when they will be the only
database views available to you for recovery purposes.
The data in these views source from either the database control file or C structures that are
part of the Oracle Database kernel. Typically these views are available when the database
instance is mounted (see “Oracle Database Startup and Shutdown” later in this chapter),
but some views are available only after the instance has started.
Using Data Dictionary views
In the real world, DBAs use the data dictionary a great deal. Although Oracle offers a nice
graphical database administration tool called Oracle Enterprise Manager Cloud Control
12
c
that helps reduce the DBAs' need to use the data dictionary, the typical DBA will often
need to access the data dictionary.
For example, recently we needed to know which users were on the system and what their
OS process IDs were so we could kill a process. We were already in SQL*Plus, and it was
easier to just query the data dictionary than to open a browser, log into the OEM, and surf
to the page that would give us the information we wanted.
Our boss thought we were crazy for not using OEM, so we had a race to see who could
get the information faster. Want to guess who won? Often DBAs will create their own set
of scripts to access the data dictionary views. This makes it even faster to get the infor-
mation you want without having to switch back and forth to OEM. OEM is a great tool,
but sometimes it just pays to know the data dictionary. Plus, there are some places that
don't use it at all.
Search WWH ::
Custom Search