...
- Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
- Having invalid objects in your database usually indicates that your underlying tables have been altered to add a new column or have had DDL operations performed on them. The most common objects that become invalid are views, packages, and procedures. Invalid packages and procedures can cause a long response delay because they have to be recompiled. The user is forced to wait for the recompilation to complete. If you do alter your tables, you should always recompile your invalid packages and procedures to avoid user frustration.
Code Block SELECT owner, object_type, object_name, status FROM all_objects WHERE status = 'INVALID' ORDER BY owner, object_type, object_name
- e.g. RAC Extermal
No Format OWNER OBJECT_TYPE OBJECT_NAME STATUS -------------- -------------- ----------------------- --------- SAKAI_EXTERNAL VIEW UCDSAKAI_CLASS_SCHEDULE INVALID SAKAI_EXTERNAL VIEW UCDSAKAI_COURSE INVALID SAKAI_EXTERNAL VIEW UCDSAKAI_COURSE_INFO INVALID
Checking the Number of Objects
- Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
- This listing provides you with a list of the number of objects on a per-user basis. It can be run regularly to make sure that your database is operating as you expect it to.
Code Block SELECT username, COUNT(DECODE(o.type#, 2, o.obj#, '')) Tab, COUNT(DECODE(o.type#, 1, o.obj#, '')) Ind, COUNT(DECODE(o.type#, 5, o.obj#, '')) Syn, COUNT(DECODE(o.type#, 4, o.obj#, '')) Vew, COUNT(DECODE(o.type#, 6, o.obj#, '')) Seq, COUNT(DECODE(o.type#, 7, o.obj#, '')) Prc, COUNT(DECODE(o.type#, 8, o.obj#, '')) Fun, COUNT(DECODE(o.type#, 9, o.obj#, '')) Pck, COUNT(DECODE(o.type#,12, o.obj#, '')) Trg, COUNT(DECODE(o.type#,10, o.obj#, '')) Dep FROM sys.obj$ o, sys.dba_users U WHERE u.user_id = o.owner# (+) GROUP BY username;
Views to access AWR data
...