Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-03 |
|
|
View PDF |
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:
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:
Oracle Database 2 Day Developer's Guide for more information about PL/SQL procedures, functions, packages, and triggers
Oracle Database 2 Day + Java Developer's Guide for more information about Java sources and Java classes
Oracle Database PL/SQL Language Reference to learn about PL/SQL code
Oracle Database Administrator's Guide for more information about object invalidation
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:
Go to the Database Home page, logging in as user SYSTEM
.
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.
In the Message column, click an invalid object message.
The Owner's Invalid Object Count page appears.
Under Related Links, click Invalid Objects Details.
The Invalid Object Details page appears, showing a list of invalid objects.
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:
Go to the Database Home page, logging in as user SYSTEM
.
At the top of the page, click Schema to view the Schema subpage.
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.
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.
Select the schema object that you want to validate.
In the Actions list, select Compile, and then click Go.
A confirmation message appears.