everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 02

Next >>


 

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@yourhost 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?

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.