Oracle Database Object invalidation Solution

In the Oracle database object invalidation solution project, VPD is used for development. The creation of Database User B objects depends on data user A. Because user A objects perform DDL, migration, dump, and other operations, this results in the INVALID of user B. User B is used as the data source of the application system, which leads to application system errors. In this case, you can perform the following processing: 1. Find the INVALID object [SQL] select object_type, object_id, object_name from user_objects www.2cto.com where status = 'invalid' order by object_type 2, you can recompile these objects. There are multiple compilation methods: 1) DBMS_DDL2 .) DBMS_UTILITY3 .) UTL_RECOMP4) UTLRP. SQL5) Manually Recompile the best solution is to Manually compile these objects. You can refer to the following SQL Script: [SQL] Spool recompile. SQL Select 'alter 'object _ type' 'object _ name' compile; 'From user_objects Where status <> 'valid' And object_type IN ('view', 'synonym ', 'processed', 'function', 'package', 'trigger'); Spool off @ recompile. SQL www.2cto.com Note: VIEW, SYNONYM, PROCEDURE, PACKAGE, FUNCTION, TRIGGER Spool pkg_body. SQL Select 'alter package' object _ name' compile body; 'From user_objects where status <> 'valid' And object_type = 'package body'; Spool off @ pkg_body. SQL Spool undefined. SQL select 'alter materizlized view 'object _ name' compile; 'From user_objects where status <> 'valid' And object_type = 'undefined'; Spool off @ UNDEFINED. SQL www.2cto.com Spool javaclass. SQL Select 'alter java class' object _ name' resolve; 'from user_objects where status <> 'valid' And object_type = 'java class'; Spool off @ javaclass. SQL www.2cto.com Spool typebody. SQL Select 'alter type' object _ name' compile body; 'From user_objects where status <> 'valid' And object_type = 'Type body'; Spool off @ typebody. SQL Spool public_synonym. SQL www.2cto.com Select 'alter public synonym' object _ name' compile; 'From user_objects Where status <> 'valid' And owner = 'public' And object_type = 'synonym '; spool off @ public_synonym. SQL can be referred: http://dbataj.blogspot.com/2007/08/how-to-compile-invalid-objects.html Author: luyushuang