Find and fix Oracle INVALID objects for Symantec DLP

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

1. Navigate to %ORACLE_HOME%\rdbms\admin.
2. Log into SQLPLUS as sys as sysdba.
3. Type the following:
   

   @utlrp.sql

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

Determine why remaining uncompiled objects failed to compile

1. From the SQL prompt type, list the invalid objects:
   

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

2. 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

3. 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';