Find and fix Oracle INVALID objects for Symantec DLP

book

Article ID: 159600

calendar_today

Updated On:

Products

Data Loss Prevention Enforce Data Loss Prevention

Issue/Introduction

Oracle database objects appear as "INVALID", which you find when performing various actions in Symantec Data Loss Prevention (DLP), such as:

Upgrading DLP
Performing database checks using the DLP Update Readiness Tool (URT)
Performing data schema validation

Cause

The Oracle database contains a wide variety of objects: tables, indexes, views, procedures, packages, functions, and sequences. These objects are reliant upon each other to be compiled and functional; such an object state or status is "VALID".

If an object has not been compiled or has changed in some way not readily apparent to other reliant objects, that object's status is "INVALID". Attempting to use that object causes errors.

Resolution

Recompile invalid objects using the utlrp.sql script

Navigate to %ORACLE_HOME%\rdbms\admin.
Log into SQLPLUS as sys as sysdba.
Type the following:
```
@utlrp.sql
```

The output will tell you the number of objects left uncompiled.

Determine why remaining uncompiled objects failed to compile

From the SQL prompt type, list the invalid objects:

set pagesize 100

select object_name, object_type, owner from dba_objects where status<>'VALID';

Manually compile the individual objects:
```
alter <object_type> <owner>.<object_name> compile;
```
If the object type is "Package Body", specify the compilation of the body:
```
alter package <owner>.<object_name> compile body;
```
If you see an error, determine the cause:
```
show errors
```
You will need to address any errors individually, which is not covered in this article.

Additional Information

Create a list of all invalid objects

Check a database for invalid objects by logging into sqlplus as sys as sysdba and running the following script:

set pagesize 100

select object_name, object_type, owner from dba_objects where status<>'VALID';