Procedures are invalid after import when Spotfire database is in Oracle

Procedures are invalid after import when Spotfire database is in Oracle

When the Spotfire Database have been treated with export and import, for e.g. restoring a backup or moving the environment elsewhere (testing, validation etc), the procedures can become invalid.
The reason for this is that Oracle compiles each procedure during the import and their dependencies might be imported afterwards.
There is no way of controlling the order the objects are exported (if the export is done in one job), and the import is done in the order objects appear in the dmp file.

Connected to the oracle database with sysdba right you can list any invalid objects:
 

SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID' and owner = 'SPOTFIRESCHEMAOWNER'
ORDER BY owner, object_type, object_name;

This can be the view of the procedure when you look at them in SQL Developer (logged in as SpotfireSchemaOwner).



Normally you see ORA-06550 PL/SQL errors when invalid procedures are used, and these procedures are used for several activities in Spotfire. But issues might not be obvious immediately.
  If you are logged in to SQL Developer and see the invalid procedures, you can right-click on each of them and choose "Compile".
If you want to do this in a more scripted way after each import you can choose to compile all invalid objects in the whole schema by executing the following as sysdba:

EXEC UTL_RECOMP.recomp_serial('SPOTFIRESCHEMAOWNER');

If all procedures (and maybe other invalid objects) compile correctly the issue should be fixed. Restarting the Spotfire Server(s) is a good idea to clear out sessions with issues. https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects