Databases Reference
In-Depth Information
Please note that in the default installation of Oracle Database 11
g
R2
Enterprise Edition the
mksample.sql
script is not present.
You can find it in the Companion CD.
Acquiring data using a data dictionary and
dynamic performance views
In the Oracle database, there are many views which can be queried to acquire data about the
database state. They are divided into data dictionary views, with a name similar to
DBA_*
,
and dynamic performance views, named something similar to
V$_*
.
Getting ready
When we use a standard template in Oracle DBCA to create a database, both data dictionary
views and dynamic performance views are in place after database creation. If we prefer to
use our own scripts to create the database, we need to launch at least the
catalog.sql
and
catproc.sql
scripts to populate the data dictionary with the views we need. These scripts
are located in the
rdbms/admin
subdirectory of the Oracle Home directory.
To collect timing information in the dynamic performance views, we have to set the parameter
TIMED_STATISTICS=TRUE
in the
init.ora
file of our database instance. We can also
accomplish this requirement with the following SQL statement:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE SCOPE = BOTH;
Please note that the default value for the
TIMED_STATISTICS
parameter is already
TRUE
and that there isn't any perceptible
performance gain in changing this default value to
FALSE
.
How to do it...
We can query the data dictionary views and the dynamic performance views like any other
view in the database, using SQL statements.
We can also query
DBA_VIEWS
, which is a data dictionary view showing other views in the
database:
select view_name from dba_views
where view_name like 'DBA%' order by 1
