...
- Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
- The following query lists all columns that have differing lengths or data types but that have the same column name. For example, ACCOUNT_NO may be NUMBER(9) in one table and VARCHAR(9) in another. Having different data types can cause data casting problems and result in indexes not being used.
Code Block SELECT owner, column_name , table_name||' '||data_type||'('|| DECODE(data_type, 'NUMBER', data_precision, data_length)||')' "Characteristics" FROM all_tab_columns WHERE (column_name, owner) IN (SELECT column_name, owner FROM all_tab_columns GROUP BY column_name, owner HAVING MIN(DECODE(data_type, 'NUMBER', data_precision, data_length)) < MAX(DECODE(data_type, 'NUMBER', data_precision, data_length)) ) AND owner NOT IN ('SYS', 'SYSTEM')- e.g. RAC Extermal
No Format ... SAKAI_EXTERNAL ENROLLMENTSET_EID BANNER_CM_SECTION VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID BANNER_ENROLLMENT VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID BANNER_INSTRUCTORS VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID CMT_INSTRUCTORS VARCHAR2(255) SAKAI_EXTERNAL ENROLLMENTSET_EID CMT_SECTION VARCHAR2(255) SAKAI_EXTERNAL ENROLLMENTSET_EID CM_ENROLLMENT VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID CM_INSTRUCTORS VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID COURSEMANAGEMENT_ENROLLMENT VARCHAR2(100) SAKAI_EXTERNAL ENROLLMENTSET_EID COURSEMANAGEMENT_INSTRUCTORS VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID COURSEMANAGEMENT_SECTION VARCHAR2(74) SAKAI_EXTERNAL ENROLLMENTSET_EID UNEX_ENROLLMENT VARCHAR2(100) SAKAI_EXTERNAL COURSEOFFERING_EID BANNER_CM_SECTION VARCHAR2(82) SAKAI_EXTERNAL COURSEOFFERING_EID BANNER_ENROLLMENTSET VARCHAR2(82) SAKAI_EXTERNAL COURSEOFFERING_EID BANNER_OFFERING_COURSESETS VARCHAR2(82) SAKAI_EXTERNAL COURSEOFFERING_EID CMT_SECTION VARCHAR2(255) SAKAI_EXTERNAL COURSEOFFERING_EID COURSEMANAGEMENT_MEMBERSHIP VARCHAR2(0) SAKAI_EXTERNAL COURSEOFFERING_EID COURSEMANAGEMENT_SECTION VARCHAR2(82) SAKAI_EXTERNAL SAKAI_USER_KERB_NAME UCDSAKAI_COURSE_MEMBERS VARCHAR2(18) SAKAI_EXTERNAL SAKAI_USER_KERB_NAME UCDSAKAI_USER_AFFILIATION VARCHAR2(50) SAKAI_EXTERNAL SAKAI_USER_ROLE_NAME UCDSAKAI_COURSE_MEMBERS VARCHAR2(20) SAKAI_EXTERNAL SAKAI_USER_ROLE_NAME UCDSAKAI_USER_AFFILIATION VARCHAR2(50) SAKAI_EXTERNAL SAKAI_USER_ENTITY_TYPE UCDSAKAI_COURSE_MEMBERS VARCHAR2(20) SAKAI_EXTERNAL SAKAI_USER_ENTITY_TYPE UCDSAKAI_USER_AFFILIATION VARCHAR2(50) ...
Listing Invalid Objects
- 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
Views to access AWR data
...