DBA Fundamentals

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:

Hands-on
In this exercise you will learn how to perform some of the most important dictionary views.

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
/
 

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')
/
 

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.
 

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
/
 

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%'
/
 

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
/
 

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

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.
 

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?