1 / 18

Creating Data Dictionary Views and Standard Packages

5. Creating Data Dictionary Views and Standard Packages. Objectives. Constructing the data dictionary views Using the data dictionary Preparing the PL/SQL environment using the administrative scripts Administering stored procedures and packages. Using the Data Dictionary.

mimis
Télécharger la présentation

Creating Data Dictionary Views and Standard Packages

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related