How the Data Dictionary Is Used


The Oracle Server, users (DBAs and regular users alike), and programs access the data dictionary to find information about users, schema objects, and storage structures. The data contained in the data dictionary are needed for the server to function properly. This is why you should never, ever update the information in these tables manually. The data is read whenever the database is operational to determine whether objects exist and what users have what access to those objects. Information in the data dictionary is modified whenever a Data Definition Language (DDL) statement is executed in the instance.

Users and database administrators can use the data dictionary views as a reference. Some views are accessible to any Oracle user; others are intended to be viewed by database administrators only.

The data dictionary is not a single entity. It is made of individual components, some of which are supporting program units; others are tables and views on tables.

Program Units

Program units, also often known as stored procedures, are PL/SQL programs stored in the database rather than as external programs at the operating system level. Program units include functions, procedures, and packages. The data dictionary includes many Oracle-provided program units for use in the database. Many are there only for the support of other components of the database, such as PL/SQL, Java programs stored in the database, and even Java Enterprise Beans, whereas others are there for you to use in your day-to-day life as a DBA. Still others allow programmers and developers to access and use many of the useful functions of the Oracle Server.

Views on Base Tables

Different types of views can be found in the Oracle data dictionary, including static views and dynamic views. The following sections discuss the differences between these two types of views and what those different views are used for.

Static Views

The data dictionary views are static views that answer specific questions about objects, users, and privileges. These static views change only when there is an alteration, addition, or deletion of objects from the database. These views have the distinct characteristics of being prefixed with either the USER_, ALL_, or DBA_ prefix. Examples of questions that these views answer are as follows:

  • When was the object created?

  • What is this object a part of?

  • Who owns the object?

  • Is the object valid?

  • What privileges does this user have?

  • When was this user created?

  • What profile does this user have?

Dynamic Performance Views

Dynamic performance views, also known as the V$ views or performance views, provide an actively changing view into current database activity. Throughout the duration of the instance being operational, the Oracle Server maintains these virtual tables (virtual in that they reside in memory rather than on disk and are defined as views) with information reflecting real-time conditions in the database. These views, which are owned by the SYS user, are continually updated while the database is operational with information accessed from memory and the control file, among other places. DML on these views is not permitted.

DBAs use these views to monitor and tune the database. From the V$ views you could answer the following kinds of questions:

  • Is this object online?

  • Is this object available?

  • Is this object open?

  • Is this service active?

  • Is this user logged on?

  • What query is this user running that is running away?

The data dictionary has three main types of static views. These views fall into three main categories that can be distinguished from each other by their scope as follows:

  • DBA_ (DBA_TABLES, for example) Provide information about the objects in all schemas in the database. They contain references to all objects within the database instance.

  • All_ (ALL_TABLES) Provide information on all the objects accessible to the user who is making the query. For every DBA_ view, there is a corresponding ALL_ view that allows users to see objects to which they have been granted access. For example, the following command set shows how to use DBA_TABLES, USER_TABLES, and ALL_TABLES to see what tables that user SCOTT has access to. First, find out what table SCOTT owns from DBA_TABLES:

     SQL> SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'SCOTT' OWNER                          TABLE_NAME ------------------------------ ------------------------------ SCOTT                          BONUS SCOTT                          DEPT SCOTT                          EMP SCOTT                          SALGRADE 

    Now, let's look at what shows up when you select from ALL_TABLES when logged in as SCOTT:

     SQL> SELECT OWNER, TABLE_NAME FROM ALL_TABLES; OWNER                          TABLE_NAME ------------------------------ ------------------------------ SYS                            DUAL SYS                            SYSTEM_PRIVILEGE_MAP SYS                            TABLE_PRIVILEGE_MAP SYS                            STMT_AUDIT_OPTION_MAP SYSTEM                         DEF$_TEMP$LOB WMSYS                          WM$WORKSPACES_TABLE SYSTEM                         HELP MDSYS                          OGIS_SPATIAL_REFERENCE_SYSTEMS MDSYS                          USER_CS_SRS MDSYS                          USER_TRANSFORM_MAP MDSYS                          SDO_DIST_UNITS MDSYS                          SDO_AREA_UNITS MDSYS                          SDO_ANGLE_UNITS CTXSYS                         DR$POLICY_TAB MDSYS                          CS_SRS MDSYS                          SDO_ELLIPSOIDS MDSYS                          SDO_PROJECTIONS MDSYS                          SDO_DATUMS WKSYS                          WK$SYS_CONFIG WKSYS                          WK$CRAWLER_CONFIG_DEFAULT WKSYS                          WK$MIMETYPES WKSYS                          WK$CHARSET WKSYS                          WK$LANG SCOTT                          DEPT SCOTT                          EMP SCOTT                          BONUS SCOTT                          SALGRADE SYS                            AUDIT_ACTIONS SYS                            PSTUBTBL SYS                            ODCI_SECOBJ$ SYS                            ODCI_WARNINGS$ 

    And now we look at USER_TABLES when logged in as user SCOTT:

     SQL> SELECT TABLE_NAME FROM USER_TABLES TABLE_NAME ------------------------------ BONUS DEPT EMP SALGRADE Select * from ALL_TABLES; 

    These objects include not only those that the user owns, but all those to which the user has been given privileges.

  • USER_ Provide information on objects owned by the current user. For every DBA_ view and every ALL_ view, there is a corresponding USER_ view designed to show those objects that the given user owns.

Now that we have discussed the different kinds of views in the data dictionary, we will look at retrieving information from this valuable asset.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net