Hands-On 01 (The most important Dictionary Views)

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%\rdbms\admin\catalog.sql

-- Hands-On 01 (The most important Dictionary Views)
-- Preparation
set echo on
connect system/manager@school as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10

pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- 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.

-- SQL> START %ORACLE_HOME%\rdbms\admin\catalog.sql


-- We do not run this command since we have already created 
-- the data dictionary views when we created the database.

-- Now, let's to get the list of all dictionary tables that
-- you created.

pause


SELECT table_name FROM dictionary
ORDER BY 1
/

pause

CLEAR SCR
-- Query the count for the number of views that you have in the 
-- dictionary for the ALL_, DBA_, and USER_ dictionary views.

pause


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

pause 

CLEAR SCR
-- 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.

pause 


SET LONG 9999
SET PAGESIZE 100

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.

pause

CLEAR SCR
-- Use the V$PWFILE_USERS view to query the users that 
-- are in the database password file.

pause


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.

pause


CLEAR SCR
-- Query the V$PARAMETER or V$SYSTEM_PARAMETER view to 
-- list information about the modified parameters that 
-- contain the word SHARE.

pause


SELECT * FROM V$PARAMETER WHERE NAME LIKE '%share%'
/

-- Also, you can use the SHOW PARAMETER command.

pause
CLEAR SCR
-- Query the V$SGA view to list information about the SGA 
-- parameters.

pause


SELECT * FROM V$SGA
/

-- Also, you can use the SHOW PARAMETER command.

pause


CLEAR SCR
-- Query the V$OPTION view to check if the partition option 
-- was selected or not.

pause


SELECT * FROM V$OPTION WHERE parameter like 'Partition%'
/


pause

CLEAR SCR
-- Use the V$PROCESS view to list information about all the
-- database processes.

pause


SELECT * FROM V$PROCESS
/

pause

CLEAR SCR
-- Use the V$SESSION view to list information about all of 
-- the database inactive and active sessions.

pause


SELECT * FROM V$SESSION
/

pause

CLEAR SCR
-- Use the V$VERSION view to list all of Oracle's component 
-- releases.

pause


SELECT * FROM V$VERSION
/

pause


CLEAR SCR
-- Use the V$INSTANCE view to list the instance information 
-- such as number of instances, instance name, database version,
-- archive mode, database status, etc.

pause


SELECT thread#, instance_name, version, archiver, database_status
FROM v$instance
/

pause

CLEAR SCR
-- Query the V$THREAD view to list the status of your parallel 
-- servers.

pause


SELECT * FROM V$THREAD
/

-- Multiple lines will appear on the parallel servers.
-- We do not have a parallel server and that is the reason
-- that you see only one line of output.

pause

CLEAR SCR
-- Query the V$PARAMETER view to list information about the 
-- database controlfiles.

pause


SELECT value FROM V$PARAMETER WHERE name = 'control_files'
/

pause

CLEAR SCR
-- Query the V$CONTROLFILE view to list information about 
-- the database controlfiles.

pause


SELECT * FROM V$CONTROLFILE
/

-- Notice that if the controlfile name cannot be determined 
-- then the STATUS value is INVALID; otherwise, it will be NULL.

pause


CLEAR SCR
-- Query the V$DATABASE view to list information about the 
-- database.

Pause


SELECT * FROM V$DATABASE
/

pause

CLEAR SCR
-- 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.

Pause


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.

pause

CLEAR SCR
-- Query the V$DATAFILE view to list information about the 
-- datafile names.

pause


SELECT name FROM V$DATAFILE
/

pause

CLEAR SCR
-- Query the V$DATAFILE view to list information about the 
-- datafiles' creation, status, checkpoint, number of blocks,
-- and block size.

pause


SELECT creation_time created, status,
checkpoint_change#, blocks, block_size
FROM v$datafile
/


pause


CLEAR SCR
-- Query the V$LOGFILE view to list information about the 
-- log files.

pause


SELECT * FROM V$LOGFILE
/

-- Later on the subject we will talk about the log files status.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause