Creating Data Dictionary Views and Standard Packages

1
5
Creating Data Dictionary Views and Standard
Packages
2
Objectives

• Constructing the data dictionary views
• Using the data dictionary
• Preparing the PL/SQL environment using the
  administrative scripts
• Administering stored procedures and packages

3
Using the Data Dictionary

• The data dictionary provides information about
• Logical and physical database structure
• Names, definitions, and space allocation of
  schema objects
• Integrity constraints
• Database users and privileges
• Auditing

4
Base Tables and Data Dictionary Views
Data dictionary views- Views simplify the base
table information- Created, as user SYS, with
the catalog.sql script
Base tables- Normalized- Created, as user SYS,
with the sql.bsq script
5
Data Dictionary Views
DBA_xxx
objects of the entire database
ALL_xxx
objects can be accessed by the user
USER_xxx
objects owned by the user
6
Data Dictionary Views Examples and Categories
Description
Views
Dictionary, dba_viewsdict_columns
General overview
dba_tablesdba_objectsdba_lobsdba_tab_columnsdb
a_constraints
Information related to the user objects such as
tables, constraints, large objects and columns
dba_usersdba_sys_privsdba_roles
Information about user privilegesand roles
7
Data Dictionary Views Examples and Categories
Description
Views
dba_extentsdba_free_spacedba_segments
Space allocation for database objects
dba_rollback_segsdba_data_filesdba_tablespaces
General database structures
dba_audit_traildba_audit_objectsdba_audit_obj_op
ts
Auditing information
8
Creating Data Dictionary Views
Remember run the scripts as user SYS
9
Administrative Scripts
The following naming conventions exist for the
sql scripts
Convention
Description
cat.sql
Catalog and data dictionary information
dbms.sql
Database package specifications
prvt.plb
Wrapped database package code
utl.sql
Views and tables for database utilities
10
Stored Procedures and Packages
Instance
Database applications
SGA
Shared poolDBMS_SESSION
begin ... dbms_session.set_role(..) ... end
SET_ROLEbegin...end
PLUSgtexecute dbms_session.set_role(..)
SVRMGRgtexecute dbms_session.set_role(..)
11
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

12
What Are Packages?

• Group logically related PL/SQL types, items, and
  subprograms
• Have two parts
• A specification
• A body
• Allow Oracle to read multiple objects into memory
  at once

13
Package
Package specification
Procedure Adeclaration
Procedure Bdefinition
Package body
Procedure A definition
Local variable
14
Example
Package specificationfrom dbmsutil.sql
create or replace package dbms_session is
procedure set_role(role_cmd varchar2)
create or replace package body dbms_session
wrapped 0abcdabcdabcdabcd ...
Package body fromprvtutil.plb
15
Oracle-Supplied Packages

• DBMS_LOBProvides routines for operations on
  BLOB and CLOB datatypes
• DBMS_SESSIONGenerates SQL commands like ALTER
  SESSION or SET ROLE
• DBMS_UTILITYProvides various utility routines
• DBMS_SPACEProvides segment space availability
  information
• DBMS_ROWIDProvides ROWID information
• DBMS_SHARED_POOLKeeps and unkeeps information in
  the shared pool

16
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
17
Troubleshooting

• The status of dependent objects may be INVALID
• If DDL commands are executed on referenced
  objects
• After creating the objects using the IMPORT
  utility

18
Summary

• Creating and using the data dictionary views
• Using the administrative scripts
• Obtaining information about stored
  procedures and packages