attitude may not solve all your problems, but it will annoy
enough people to make it worth the effort.” Herm Albright
(1876 - 1944)
first then play the video:
most important Dictionary Views
The most important
As a DBA, you are
responsible for obtaining the most important dictionary views and
report them to the senior DBA of your organization. You need to know
how to obtain these requirements by using simple SQL statements. Your
job’s responsibilities dictate that you should at least be informed
of the following basic fundamental dictionary views:
Creating a data dictionary
Using the CATALOG.SQL
Using the ALL_, DBA_, and
USER_ dictionary views
Using the DBA_VIEWS
Using the DBA_TABLES
Using the V$PWFILE_USERS
Connecting as SYSDBA or
Using the V$PARAMETER
Using the SHOW PARAMETER
Using the V$SGA view
Using the V$OPTION view
Using the V$PROCESS view
Using the V$SESSION view
Using the V$VERSION view
Using the V$INSTANCE view
Using the V$THREAD view
Using the V$PARAMETER view
Using the V$CONTROLFILE
Using the V$DATABASE view
CONTROLFILE SEQUENCE NUMBER
CONTROLFILE CHANGE NUMBER
Using the V$DATAFILE view
Using the V$LOGFILE view
In this exercise you will learn how to perform some of the most
important dictionary views.
Connect to a database
First, connect to SQLPlus
as the system/manager user.
SQL> CONNECT system/manager@school <mailto:system/manager@school>
Create a data dictionary
You can create or recreate
a data dictionary, by running the catalog.sql script from within
SQL*Plus while connected to SYS or any users as the administrative
privilege SYSDBA. This script is located at %ORACLE_HOME%. Within the
catalog.sql script, the following scripts are called.
creates the sys.aud$ dictionary table,
CATLDR.SQL that creates
views for the SQL*Loader tool,
CATEXP.SQL that creates
views for the IMPORT/EXPORT utilities,
creates views for partitioning Oracle option,
CATADT.SQL that creates
views that support user-defined types and object components.
creates the STANDARD package, which stores all Oracle datatype such as
VARCHAR2, BLOB and built-in SQL functions such as SUM, DECODE, etc.
SQL> START %ORACLE_HOME%\b0 catalog.sql
The objects of a database will be stored in a place call
repository or dictionary. All the database objects, access security to
an object, objects’ relationships, etc can be viewed from
Now, let's to get the list
of all dictionary tables that you created from the catalog.sql.
SQL> SELECT table_name FROM dictionary
ORDER BY 1
ALL_, DBA_, and USER_
You use the ALL_ dictionary
views such as ALL_TABLES, etc to query all user tables plus all the
granted tables. You use the DBA_ dictionary views such as DBA_TABLES
to display the entire database tables and USER_ dictionary view such
as USER_TABLES to list only tables that created by current user.
Query the count for the
number of views that you have in the dictionary for the ALL_, DBA_,
and USER_ dictionary views.
SQL> SELECT SUBSTR(table_name,1,4) , count(1)
GROUP BY substr(table_name,1,4)
HAVING substr(table_name,1,4) in ('DBA_','ALL_','USER')
You use the DBA_VIEWS view
to query all sql script, status, etc. The status column shows that if
a view is INVALID or VALID. If the view is invalid you can compile the
view or correct the error.
Set the LONG size to 9999
and the pagesize to 100 and then query the DBA_VIEWS dictionary view
where the VIEW_NAME value is DBA_TABLES.
SQL> SET LONG 9999
SQL> SET PAGESIZE 100
SQL> SELECT text FROM dba_views
WHERE view_name = 'DBA_TABLES'
This is an example the DBA_TABLES view source code. Notice
that the view can be quite complex. One can appreciate this hidden
parameter set to the EXCLUSIVE or SHARED value enforces the user enter
a password. A DBA can logon to the database as SYSDBA or SYSOPER
privilege if he knows the password.
Use the V$PWFILE_USERS view
to query the users that are in the database password file.
SQL> SELECT * FROM V$PWFILE_USERS
Notice that any object that is created by anyone logging in
as SYSDBA or SYSOPER will be owned by the SYS user.
The V$PARAMETER view shows
all the parameters value in the database.
Query the V$PARAMETER or
V$SYSTEM_PARAMETER view to list information about the modified
parameters that contain the word SHARE.
SQL> SELECT * FROM V$PARAMETER
WHERE NAME LIKE '%share%'
Also, you can use the SHOW PARAMETER command. For example:
SQL> SHO PARAMETER share
Query the V$SGA view to
list information about the SGA parameters.
SQL> SELECT * FROM V$SGA
If you don’t want to use
the V$PARAMETER view, then use the SHOW PARAMETER command (SHO …).
It is easier and less typing.
Also, you can use the SHOW
PARAMETER command to list the sga information.
SQL> SHOW PARAMETER sga
The V$OPTION view shows the
installation options Use this view to make sure that you are not
violating any option license agreement.
Query the V$OPTION view to
check if the partition option was selected or not.
SQL> SELECT * FROM V$OPTION
WHERE parameter like 'Partition%'
The V$PROCESS view contains
the database background processes and server processes.
Use the V$PROCESS view to
list information about all the database processes.
SQL> SELECT * FROM V$PROCESS
The V$SESSION view shows all the sessions that are inactive or
active. A DBA may use this view to list the username, sid, and serial#
of a user to kill it’s session.
Use the V$SESSION view to
list information about all of the database inactive and active
SQL> SELECT * FROM V$SESSION
Oracle contains so many
different components. The V$VERSION view is an excellent view to
display all of its component releases.
Use the V$VERSION view to
list all of Oracle's component releases.
SQL> SELECT * FROM V$VERSION
Most of the time, you may
have multiple instances in your server or machine. Using the
V$INSTANCE view, ensure that if you are in the right instance before
performing a database structure changes.
Use the V$INSTANCE view to
list the instance information such as number of instances, instance
name, database version, archive mode, database status, etc.
SQL> SELECT thread#, instance_name,
version, archiver, database_status
If you have a parallel
server, the V$THREAD view tells you that what instance you are in.
Query the V$THREAD view to
list the status of your parallel servers.
SQL> SELECT * FROM V$THREAD
Multiple lines will appear if you have the parallel
servers. We do not have a parallel server and that is the reason that
you see only one line of output.
Query the V$PARAMETER view
to list information about the database controlfiles.
SQL> SELECT value
WHERE name = 'control_files'
The V$CONTROLFILE view
shows the location of the controlfiles and the status of it.
Query the V$CONTROLFILE
view to list information about the database controlfiles.
SQL> SELECT * FROM V$CONTROLFILE
Notice that if the controlfile name cannot be determined then the
STATUS value is INVALID; otherwise, it will be NULL.
If you have multiple
database on your server, the V$DATABASE view show what database you
are login plus lots of information about the database such as
Query the V$DATABASE view
to list information about the database.
SQL> SELECT * FROM V$DATABASE
Query the V$DATABASE view
to list information about the control files of the database, such as
CONTROLFILE TYPE, CONTROLFILE CREATED, CONTROLFILE SEQUENCE NUMBER,
and CONTROLFILE CHANGE NUMBER.
SQL> SELECT controlfile_type as type,
controlfile_created as created,
Notice that this view gives information that is also stored
within the control file.
The V$DATAFILE view show
information about when a datafile was created, what is its status,
when was the last SCN, what is its block size, etc.
Query the V$DATAFILE view
to list information about the datafile names.
SQL> SELECT name FROM V$DATAFILE
Query the V$DATAFILE view to list information about the datafiles'
creation, status, checkpoint, number of blocks, and block size.
SQL> SELECT creation_time created, status,
checkpoint_change#, blocks, block_size
Query the V$LOGFILE view to
list information about the log files.
SQL> SELECT * FROM V$LOGFILE
Later on the subject we will talk about the log files status.
“I take it as a
man's duty to restrain himself.” Lois McMaster Bujold, Ethan
of Athos, 1986
Q: Describe a data
dictionary in the Oracle database.
Q: Describe the CATALOG.SQL
Q: What are the uses of
ALL_, DBA_, and USER_ dictionary views?
Q: Describe the DBA_VIEWS
Q: Describe the DBA_TABLES
Q: Describe the
Q: Describe the V$PARAMETER
Q: Describe the
Q: Describe the V$SGA view.
Q: Describe the V$OPTION
Q: Describe the V$PROCESS
Q: Describe the V$SESSION
Q: Describe the V$VERSION
Q: Describe the V$INSTANCE
Q: Describe the V$THREAD
Q: Describe the
Q: Describe the DBA_VIEWS
Q: Describe the V$DATAFILE
Q: Describe the V$DATABASE
Q: Describe the V$LOGFILE
Q: Describe the V$LOG view.
Q: What do the following
Q: What does the SHOW