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_columnsdba_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_opts Auditing information

8. Script catalog.sql catproc.sql Purpose Creates commonly used data dictionary views Runs all scripts required for PL/SQL on the server 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; PLUS>execute dbms_session.set_role(..) SVRMGR>execute 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. 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 Oracle-Supplied Packages

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