Topics: The most important Dictionary
Views
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
|
More Resources by
Google: |
|
|
|
|
-- 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
|