iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 02

“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?