Oracle Database Object invalid

VPD is used for development in the project. The creation of Database User B objects depends on 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.

You can perform the following operations:

1. Find the invalid object

1. SelectObject_type, object_id, object_name
2. FromUser_objects
3. WhereStatus ='Invalid' Order ByObject_type

2. After judgment, you can recompile these objects.

Compilation methods include:

1) DBMS_DDL

2.) DBMS_UTILITY
3.) UTL_RECOMP
4) UTLRP. SQL
5) Manually Recompile

The best solution is to manually compile these objects. You can refer to the following SQL script:

1. Spool recompile. SQL
3. Select'Alter'Object _ type' 'object _ name' compile ;'
4. FromUser_objects
5. WhereStatus <> 'valid'
6. AndObject_typeIN('VIEW', 'Synonym ',
7. 'PROCEDURE','FUNCTION',
8. 'Package ','TRIGGER');
10. SpoolOff
11. @ Recompile. SQL
14. Note:VIEW, SYNONYM,PROCEDURE, PACKAGE,FUNCTION,TRIGGER
17. Spool pkg_body. SQL
19. Select'AlterPackage 'object _ name' compile body ;'
20. FromUser_objects
21. WhereStatus <> 'valid'
22. AndObject_type = 'package body ';
24. SpoolOff
25. @ Pkg_body. SQL
28. Spool undefined. SQL
30. Select'AlterMaterizlizedView'Object _ name' compile ;'
31. FromUser_objects
32. WhereStatus <> 'valid'
33. AndObject_type = 'undefined ';
35. SpoolOff
36. @ Undefined. SQL
39. Spool javaclass. SQL
41. Select'AlterJava class 'object _ name' resolve ;'
42. FromUser_objects
43. WhereStatus <> 'valid'
44. AndObject_type = 'java class ';
46. SpoolOff
47. @ Javaclass. SQL
50. Spool typebody. SQL
52. Select'AlterType 'object _ name' compile body ;'
53. FromUser_objects
54. WhereStatus <> 'valid'
55. AndObject_type = 'Type body ';
57. SpoolOff
58. @ Typebody. SQL
61. Spool public_synonym. SQL
63. Select'Alter PublicSynonym 'object _ name' compile ;'
64. FromUser_objects
65. WhereStatus <> 'valid'
66. AndOwner ='PUBLIC'
67. AndObject_type = 'synonym ';
69. SpoolOff
70. @ Public_synonym. SQL