“A positive
attitude may not solve all your problems, but it will annoy
enough people to make it worth the effort.” Herm Albright
(1876 - 1944) |
Read
first then play the video:
DBA-VIDEO -The
most important Dictionary Views
The most important
Dictionary Views
Introduction
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
script
Using the ALL_, DBA_, and
USER_ dictionary views
Using the DBA_VIEWS
dictionary view
Using the DBA_TABLES
dictionary view
Using the V$PWFILE_USERS
view
Connecting as SYSDBA or
SYSOPER
Using the V$PARAMETER
Using the
V$SYSTEM_PARAMETER view
Using the SHOW PARAMETER
command
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
view
Using the V$DATABASE view
CONTROLFILE TYPE
CONTROLFILE CREATED
CONTROLFILE SEQUENCE NUMBER
CONTROLFILE CHANGE NUMBER
Using the V$DATAFILE view
Using the V$LOGFILE view
Command:
START %ORACLE_HOME%.sql
Hands-on
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>
AS SYSDBA
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.
CATAUDIT.SQL that
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,
CATPART.SQL that
creates views for partitioning Oracle option,
CATADT.SQL that creates
views that support user-defined types and object components.
STANDARD.SQL that
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
DICTIONARY view
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
repository.
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_
dictionary views
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)
FROM dictionary
GROUP BY substr(table_name,1,4)
HAVING substr(table_name,1,4) in ('DBA_','ALL_','USER')
/
DBA_VIEWS view
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
complexity.
V$PWFILE_USERS view
The LOGON_REMOTE_PASSWORD
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.
V$PARAMETER view
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
V$SGA view
Query the V$SGA view to
list information about the SGA parameters.
SQL> SELECT * FROM V$SGA
/
SHOW PARAMETER
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
V$OPTION view
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%'
/
V$PROCESS view
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
/
V$SESSION view
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
sessions.
SQL> SELECT * FROM V$SESSION
/
V$VERSION view
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
/
V$INSTANCE view
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
FROM v$instance
/
V$THREAD view
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.
V$PARAMETER view
Query the V$PARAMETER view
to list information about the database controlfiles.
SQL> SELECT value
FROM V$PARAMETER
WHERE name = 'control_files'
/
V$CONTROLFILE view
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.
V$DATABASE view
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
controlfile information.
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,
controlfile_sequence#, controlfile_change#
FROM v$database
/
Notice that this view gives information that is also stored
within the control file.
V$DATAFILE view
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
FROM v$datafile
/
V$LOGFILE view
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 |
Questions:
Q: Describe a data
dictionary in the Oracle database.
Q: Describe the CATALOG.SQL
script.
Q: What are the uses of
ALL_, DBA_, and USER_ dictionary views?
Q: Describe the DBA_VIEWS
dictionary view.
Q: Describe the DBA_TABLES
dictionary view.
Q: Describe the
V$PWFILE_USERS view.
Q: Describe the V$PARAMETER
view.
Q: Describe the
V$SYSTEM_PARAMETER view.
Q: Describe the V$SGA view.
Q: Describe the V$OPTION
view.
Q: Describe the V$PROCESS
view.
Q: Describe the V$SESSION
view.
Q: Describe the V$VERSION
view.
Q: Describe the V$INSTANCE
view.
Q: Describe the V$THREAD
view.
Q: Describe the
V$CONTROLFILE view.
Q: Describe the DBA_VIEWS
dictionary view.
Q: Describe the V$DATAFILE
view.
Q: Describe the V$DATABASE
view.
Q: Describe the V$LOGFILE
view.
Q: Describe the V$LOG view.
Q: What do the following
scripts create?
CATAUDIT.SQL
CATLDR.SQL
CATEXP.SQL
CATPART.SQL
CATADT.SQL
STANDARD.SQL
Q: What does the SHOW
PARAMETER command?
|