Skip Headers
Oracle® Database 2 Day DBA
11g Release 1 (11.1)

Part Number B28301-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Managing Program Code Stored in the Database

This section describes your responsibilities as a database administrator (DBA) with respect to program code that is stored in the database. It contains the following topics:

About Program Code Stored in the Database

Oracle Database offers the ability to store program code in the database. Developers write program code in PL/SQL or Java, and store the code in schema objects. You, as the DBA, can use Oracle Enterprise Manager Database Control (Database Control) to manage program code objects such as:

  • PL/SQL packages, procedures, functions, and triggers

  • Java source code (Java sources) and compiled Java classes

The actions that you can perform include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these code objects. You access administration pages for these objects by clicking links in the Programs section of the Schema subpage.

Note that creating and managing program code objects is primarily the responsibility of application developers. However, as a DBA you might have to assist in managing these objects. Your most frequent task with respect to program code objects might be to revalidate (compile) them, because they can become invalidated if the schema objects on which they depend change or are deleted.

Note:

Other types of schema objects besides program code objects can become invalid. For example, if you delete a table, any views that reference that table become invalid.

See Also:

Validating (Compiling) Invalid Schema Objects

As a database administrator (DBA), you may be asked to revalidate schema objects that have become invalid. Schema objects (such as triggers, procedures, or views) are invalidated when changes are made to objects on which they depend. For example, if a PL/SQL procedure contains a query on a table and the table structure is modified, the PL/SQL procedure becomes invalid. You revalidate schema objects by compiling them.

Note:

It is not always possible to revalidate a schema object that stores program code by compiling it. You may have to take remedial actions first. For example, if a view becomes invalid because a table that it references is deleted, compiling the view produces an error message that indicates that the table does not exist. You cannot validate the view until you re-create the table or retrieve it from the recycle bin.

Database Control notifies you when schema objects become invalid by displaying an alert in the Alerts section of the Database Home page.

There are two ways to display schema objects that require validation: by following an alert on the Database Home page, or by viewing the appropriate object page (Views page, Procedures page, and so on) and searching for the objects.

To validate schema objects starting from an alert:

  1. Go to the Database Home page, logging in as user SYSTEM.

    See "Accessing the Database Home Page".

  2. In the Alerts section, search for alerts with the following message:

    n object(s) are invalid in the schema_name schema.
    

    An example of such a message is the following:

    4 object(s) are invalid in the HR schema.
    

    There is a separate message for each schema that contains invalid objects.

    Description of invalid_object_alerts.gif follows
    Description of the illustration invalid_object_alerts.gif

  3. In the Message column, click an invalid object message.

    The Owner's Invalid Object Count page appears.

  4. Under Related Links, click Invalid Objects Details.

    The Invalid Object Details page appears, showing a list of invalid objects.

    Description of invalid_object_details.gif follows
    Description of the illustration invalid_object_details.gif

  5. Select one or more objects, and then click Recompile.

    A confirmation message appears, and the newly validated objects are removed from the list.

To validate a schema object starting from an object page:

  1. Go to the Database Home page, logging in as user SYSTEM.

    See "Accessing the Database Home Page".

  2. At the top of the page, click Schema to view the Schema subpage.

  3. In the Database Objects section, click the link for the object type that you want to validate.

    For example, if you want to validate a view, click Views.

  4. On the object page (for example, the Views page), enter a schema name and, optionally, an object name or partial object name, and then click Go.

    The schema objects are displayed.

  5. Select the schema object that you want to validate.

  6. In the Actions list, select Compile, and then click Go.

    A confirmation message appears.