Oracle8: Database Administration 5-5.
Data Dictionary Structure
The data dictionary, located in the SYSTEM tablespace and owned by the
user SYS, contains two parts:
• Base tables
• Data dictionary views
Base Tables
The foundation of the data dictionary is a set of base or underlying tables.
The Oracle server writes and reads these tables. Database users rarely access
them directly because they are normalized and the information is encoded.
For example, you would query the IND$ table to get information about the
indexes that are defined in the database, or select from the OBJ$ table to
display the objects defined in the database.
Never use DML commands such as INSERT, UPDATE, and DELETE to
update the base data dictionary tables directly, with the exception of the
AUD$ table (see the lesson “Auditing”).
5-4
Copyright  Oracle Corporation, 1998. All rights reserved.
Base Tables and Data
Dictionary Views
Base tables:
- Normalized
- Created with the
sql.bsq
script
Data dictionary views:
- Views simplify the base table information
- Created with the

Views with the prefix ALL are accessible by any user and usually include the column
OWNER. These views return information about objects towhich the user has access via
public or explicit grants of privileges and roles, including the objects that the user owns.
The Prefix DBA
Viewswiththe prefixDBAgiveinformationonallthe objectsin thedatabaseandusually
include the column OWNER. These views are queried by the database administrator or
anyuser granted thesystemprivilegeSELECTANY TABLE(see thelesson“Managing
Privileges”). Synonyms are created for these views so that every user with the SELECT
ANY TABLE privilege can query them.
5-5
Copyright  Oracle Corporation, 1998. All rights reserved.
Data Dictionary Views
USER_
xxx
ALL_
xxx
DBA_
xxx
objects owned by the user
objects can be accessed by the user
objects of the entire database
5-8 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages
5-6
Copyright  Oracle Corporation, 1998. All rights reserved.
Data Dictionary: Views
Examples and Categories

dba_data_files
dba_tablespaces
General database structures
Auditing information
dba_audit_trail
dba_audit_objects
dba_audit_obj_opts
Oracle8: Database Administration 5-9.
Data Dictionary Structure
To get an overview of the data dictionary views, their columns, and the
dynamic performance views, you can query the DICTIONARY or
DICT_COLUMNS view.
SVRMGR>SELECT *
2> FROM dictionary
3> WHERE table_name LIKE ’%TABLE%’;
TABLE_NAME COMMENTS

ALL_ALL_TABLES Description of all object and relational
tables accessible to the user
ALL_NESTED_TABLES Description of nested tables in tables
accessible to the user
ALL_OBJECT_TABLES Description of all object tables
accessible to the user
ALL_PART_TABLES
ALL_TABLES Description of relational tables accessible
to the user
ALL_UPDATABLE_COLUMNS Descriptionofallupdatablecolumns
.
Constructing the Data Dictionary
Constructing the Data Dictionary
After database creation, the catalog.sql and catproc.sql scripts must be run
as the user SYS. They are located in the $ORACLE_HOME/rdbms/admin
directory on UNIX and in the %ORACLE_HOME%\rdbms80\admin
directory on NT.
The
catalog.sql
Script
The catalog.sql script creates the views on the base tables, views on the
dynamic performance views and their synonyms. It starts scripts, such as
those to create views and objects for the Server Manager utility, for auditing,
for the Export and Import utility, and for the partitioning and object options.
It runs the standard.sql script, which creates the basic PL/SQL environment.
The standard.sql script declares types, exceptions, and subprograms, which
are automatically available to every PL/SQL program. For example, it
declares the built-in function named BITAND, which returns the result of
the bit operation and of its arguments:
function BITAND (LEFT binary_integer, RIGHT binary_integer)
return binary_integer;
5-8
Copyright  Oracle Corporation, 1998. All rights reserved.
Script
catalog.sql
catproc.sql
Purpose
Creates commonly used data dictionary

• cat*.sql
• dbms*.sql
• prvt*.plb
The
utl*.sql
Scripts
The utl*.sql scripts must be run when the database needs additional views
and tables.
For example, the script utlsampl.sql creates and populates the sample tables
EMP, DEPT, SALGRADE, and BONUS under the user SCOTT.
5-9
Copyright  Oracle Corporation, 1998. All rights reserved.
Administrative Scripts
cat*.sql
dbms*.sql
prvt*.plb
utl*.sql
Catalog and data dictionary information
Database package specifications
Views and tables for database utilities
Wrapped database package code
The following naming conventions exist
for the sql scripts:
Convention
Description
5-14 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages

• %ORACLE_HOME%\RDBMS80\LOADER
• %ORACLE_HOME%\RDBMS80\RMAN
These explain the advanced queuing, Loader, and Recovery Manager
functionality.
On UNIX, these scripts are located in the $ORACLE_HOME/rdbms/demo
directory.
Oracle8: Database Administration 5-15.
Administering Stored Procedures and Packages
Administering Stored Procedures and Packages
Users can store PL/SQL program units in the database and execute them
using Oracle tools such as SQL*Plus, Server Manager, or Enterprise
Manager, or to execute them from an Oracle application. Stored procedures,
including procedures and functions, and packages are examples of PL/SQL
program units.
Stored procedures and packages are database objects that are created and
removed from the schema of a user with a CREATE or DROP command.
Packages are very useful for administrative tasks.
In the example, the DBMS_SESSION.SET_ROLE procedure is executed
using SQL*Plus and Server Manager, and from an Oracle application.
Note
The procedure DBMS_SESSION.SET_ROLE will be explained in a
subsequent section.
5-10
Copyright  Oracle Corporation, 1998. All rights reserved.
Stored Procedures
and Packages
Instance

Oracle8: Database Administration 5-17.
Administering Stored Procedures and Packages
A stored procedure is a procedure or function that is created and stored in
the data dictionary as a schema object. It consists of a set of SQL and
PL/SQL constructs. Once created and compiled, it is a named object that can
be executed without recompiling. Additionally, dependency information is
stored in the data dictionary to verify the validity of each stored procedure.
The user can also include stored functions in SQL expressions. They are
used in the same manner as built-in Oracle functions such as UPPER and
SUBSTR.
Procedures and functions provide parameters that can be input only (IN),
output only (OUT), or both input and output parameters (IN OUT). The IN
mode is the default.
5-11
Copyright  Oracle Corporation, 1998. All rights reserved.
What Are Stored Procedures?
• Are procedures or functions
• Are stored in the data dictionary
• Can be used by many users
• Can accept and return parameters
• Can be used in SQL functions
5-18 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages
A package usually has a specification and a body stored separately in the

Package
specification
Package
body
Procedure A
definition
Local
variable
Package
5-14
Copyright  Oracle Corporation, 1998. All rights reserved.
Package
specification
from
dbmsutil.sql
Package
body from
prvtutil.plb
Example
create or replace package
dbms_session is
procedure set_role
(role_cmd varchar2);
create or replace package body
dbms_session wrapped
0
abcd
abcd
abcd
abcd

DBMS_SPACE, DBMS_UTILITY, DBMS_ROWID, and
DBMS_SESSION are defined by the scripts dbmsutil.sql and prvtutil.plb,
while DBMS_LOB is defined by dbmslob.sql and prvtlob.plb.
Another utility, the DBMS_SHARED_POOL package, keeps objects in the
shared pool so that they will not be aged out with the normal LRU
algorithm. It is created by running the dbmspool.sql script.
5-15
Copyright  Oracle Corporation, 1998. All rights reserved.
Oracle-Supplied Packages
• DBMS_LOB—Provides routines for operations on
BLOB and CLOB datatypes
• DBMS_SESSION—Generates SQL commands like
ALTER SESSION or SET ROLE
• DBMS_UTILITY—Provides various utility routines
• DBMS_SPACE—Provides segment space
availability information
• DBMS_ROWID—Provides ROWID information
• DBMS_SHARED_POOL—Keeps and unkeeps
information in the shared pool
5-22 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages
Examples for Package Procedures
Note
Most of the Oracle supplied packages are explained in detail in
Oracle-supplied Oracle8 Server Application Developer’s Guide and in
Oracle8 PL/SQL User’s Guide and Reference.
Package Package procedures

occurred.
5-16
Copyright  Oracle Corporation, 1998. All rights reserved.
Obtaining Information About
Stored Objects
• Data dictionary view DBA_OBJECTS:
– OWNER
– OBJECT_NAME
– OBJECT_TYPE
– STATUS (VALID, INVALID)
• DESCRIBE command:
describe dbms_session.set_role
5-24 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages
Query the data dictionary view DBA_OBJECTS to obtain information about
the owner, name, type, and status of the objects in the database.
SVRMGR> SELECT object_name, object_type, status
2> FROM dba_objects WHERE object_name like ’DBMS_%’
OBJECT_NAME OBJECT_TYPE STATUS

DBMS_ALERT PACKAGE VALID
DBMS_ALERT PACKAGE BODY VALID
DBMS_ALERT_INFO TABLE VALID
DBMS_APPLICATION_INF PACKAGE VALID
DBMS_APPLICATION_INF PACKAGE BODY VALID
DBMS_AQ PACKAGE VALID
DBMS_AQ PACKAGE BODY VALID

Equivalent to SQL "ALTER SESSION SET SQL_TRACE "
Input arguments:
sql_trace
TRUE or FALSE. Turns tracing on or off.
procedure set_nls(param varchar2, value varchar2);
5-26 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages
Troubleshooting
After the execution of DDL commands such as ALTER TABLE ADD,
RENAME, DROP, and CREATE OR REPLACE, the status of the dependent
objects changes to INVALID.
Also, loading dependent views and stored procedures with the Import utility
may lead to INVALID object status, because the Import utility may not be
able to create the dependent objects after creating the referenced objects.
The Oracle server automatically recompiles an invalid view or PL/SQL
program unit the next time it is used. In addition, the user can force the
Oracle server to recompile a view, stored procedure, or package by using the
appropriate SQL command.
Note
• Using the Import utility is covered in more detail in the lesson “Loading
and Reorganizing Data.”
• Recompiling stored procedures and packages is covered in the course
PL/SQL Program Units.
5-17
Copyright  Oracle Corporation, 1998. All rights reserved.
Troubleshooting
The status of dependent objects may be

procedures and packages
5-28 Oracle8: Database Administration.
Lesson 5: Creating Data Dictionary Views and Standard Packages

6
Maintaining the
Control File