Troubleshoot invalid objects in Oracle

Most of the procedural objects like procedures, functions, triggers, objects refer to data objects, such as table.

-If the code refers to a column, the column must exist or the code will not compile.
-If any of the data objects to which a procedural object refers change after the procedural object has been compiled, then the procedure will be marked INVALID.
-Procedural objects may also be invalid for more mundane reasons: perhaps the
programmer made a simple syntactical mistake.

The same situation can occur with views. When created they may be fine, but they will be invalidated if the tables on which they are based have their definitions changed.

Identifying Invalid Objects
--------------------------------
To identify all of the invalid objects , run the following query,

SQL> select owner,object_name,object_type from dba_objects where
status='INVALID';

How to Handle Invalid Objects:
-------------------------------
1)Compile the invalid objects:
-------------------------------
The first steps is to compile the invalid objects. Though, the first time an invalid object is accessed, Oracle will attempt to compile it automatically, but if the compilation fails, the user will receive an error. Clearly, it is better for the DBA to compile it first; then, if there is an error, he can try to fix it before a user notices.

2)If compilation is ok then there is no problem. But if it fails then check the object and object type. Suppose if the object type is procedure then you can use SHOW ERRORS to identify the the errors.

3)If the object type is view then SHOW ERRORS will not work then try to see the creation script of the view.

SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME='YOUR VIEW';

Generally you can use DBA_DEPENDENCIES object and query column REFERENCED_OWNER and REFERENCED_NAME.

4)There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix,
SQL> @?/rdbms/admin/utlrp

Workaround Example:
---------------------
1)Create one table, one invalid view and one valid view:
----------------------------------------------------------

SQL> create table first_table ( col1 number, col2 number);
Table created.

SQL> create force view first_view as select col3 from first_table;
Warning: View created with compilation errors.

SQL> create view second_view as select col2 from first_table;
View created.

2)Check Status:
----------------
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID

3)Drop Column col2 from Table and Check Status:
-----------------------------------------------
SQL> alter table first_table drop column col2;
Table altered.

SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID

4)Add column col3 and Check Status:
--------------------------------------
SQL> alter table FIRST_TABLE add (col3 number);
Table altered.

SQL> alter view FIRST_VIEW compile;
View altered.

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW INVALID

5)Troubleshoot Second View:
-----------------------------
SQL> select text from user_views where view_name='SECOND_VIEW';
TEXT
--------------------------------------------------------------------------------
select col2 from first_table

SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';

REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
COL2 NON-EXISTENT
COL2 NON-EXISTENT
FIRST_TABLE TABLE

So col2 is non-existent or missing.

SQL> alter table FIRST_TABLE add col2 number;
Table altered.
SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID

Interestingly when I add col2 FIRST_VIEW becomes invalid.

And also when deleting a referenced column status of dependent objects become invalid. But when we add the status does not changes.

6)Now compile on second_view or any access will marked the dictionary ok.

SQL> select * from second_view;
no rows selected

SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID

But First_view become INVALID.

7)Fix Error:
---------------
SQL> alter view FIRST_VIEW compile;

View altered.

SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW VALID

SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';

REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
FIRST_TABLE TABLE

Interval partitioning

Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.
The following restrictions apply:

• You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
• Interval partitioning is NOT supported for index-organized tables.
• You can NOT create a domain index on an interval-partitioned table.

Test case

Let start it from here:

SQL> conn hr/hr
Connected.

Here we create a table with range partition on the SALARY column.

SQL> create table test
 2      (sno number(6),
 3      last_name varchar2(30),
 4      salary number(6))
 5      partition by range(salary)
 6     (
 7  partition p1 values less than (5000),
 8  partition p2 values less than (10000),
 9  partition p3 values less than (15000),
10  partition p4 values less than (20000));

Table created.

Let insert some records into test table.

SQL> insert into test
    select employee_id,last_name,salary from employees
    where last_name not like '%K%';

101 rows created.

SQL> select table_name,partition_name,num_rows
 2  from user_tab_partitions
 3  where table_name='TEST';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
TEST                           P1
TEST                           P2
TEST                           P4
TEST                           P3

SQL> exec dbms_stats.gather_table_stats('HR','TEST');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
    from user_tab_partitions
    where table_name='TEST';

TABLE_NAME                     PARTITION_NAME                            NUM_ROWS
------------------------------ ------------------------------      ----------
TEST                P1                                     48
TEST                P2                                     37
TEST                           P4                                      1
TEST                           P3                                     15

As we know the data which we inserted obeyed all rules defined for partitions. Let see what happens:

SQL> insert into test
         values
         (1,'Michel',25000);
insert into test *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Note the 14400 error.
ORA-14400:
inserted partition key does not map to any partition
Cause:
An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.
Action:
Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification.
Now in 11g, Oracle introduced new partition type called INTERVAL PARTITIONING. So now I am going to check what the benefits of interval partitioning are.
First I will drop mine existing TEST table.

SQL> drop table test purge;
Table dropped.

SQL> create table test
 2          (sno number(6),
 3          last_name varchar2(30),
 4          salary number(6))
 5          partition by range(salary)
 6          Interval  (5000)
 7         (
 8      partition p1 values less than (5000),
 9      partition p2 values less than (10000),
10     partition p3 values less than (15000),
11     partition p4 values less than (20000));

Table created.

Let check the partition names.

SQL> select table_name,partition_name
   From user_tab_partitions
   Where table_name='TEST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                             P4
TEST                             P1
TEST                             P2
TEST                             P3

Here we can see we created four partition during table creation now how oracle will use this INTERVAL. Let's try to insert records into test table.

SQL> insert into test
 2       select employee_id,last_name,salary from employees
 3       where last_name not like '%K%';
101 rows created.

I inserted the data which already obeyed the existing partitions limit.

SQL> exec dbms_stats.gather_table_stats('HR','TEST');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
 2       from user_tab_partitions
 3       where table_name='TEST';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ -----------------                ----------
TEST                             P4                               1
TEST                                P1                              48
TEST                                P2                              37
TEST                                P3                              15

Now I’m going to insert the data which is not mentioned for any partition.

SQL> insert into test
 2  values
 3  (1,'Michel',25000);
1 row created.

SQL> insert into test
 2  values
 3  (2,'Michel',30000);
1 row created.

SQL> insert into test
 2  values
 3  (3,'Michel',35000);
1 row created.

SQL> insert into test
 2  values
 3  (4,'Michel',40000);
1 row created.

You can see that this time it didn’t generate the ORA_14400 errors. Let see what oracle did to insert the data over the partitions limit.
Here we go.

SQL> select table_name,partition_name
 2  from user_tab_partitions
 3  where table_name='TEST';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                              P4
TEST                                 SYS_P41
TEST                               SYS_P42
TEST                             P1
TEST                             P2
TEST                             P3
TEST                                    SYS_P43
TEST                               SYS_P44
8 rows selected.

I created 4 partitions but now we can see there are total 8 partitions ,four extra partitions with system generated names. Now its clear like water what INTERVAL did, as I specify the INTERVAL limit of (5000) and I inserted 4 records with the interval of 5000 each so oracle created new system generated partition for each that partition which was in the interval of 5000.

SQL> select table_name,partition_name,num_rows
 2  from user_tab_partitions
 3  where table_name='TEST' order by partition_name;

TABLE_NAME                     PARTITION_NAME                            NUM_ROWS
------------------------------ ------------------------------           ----------
TEST                             P1                                  48
TEST                             P2                                  37
TEST                       P3                                  15
TEST                            P4                                   1
TEST                            SYS_P41                              2
TEST                             SYS_P42                              1
TEST                                    SYS_P43                              1
TEST                                   SYS_P44                              1

8 rows selected.

SQL> select salary from test where salary>20000;

   SALARY
----------
    25000
    25000
    30000
    35000
    40000

We can easily check that salary column have 2 values for 25000 that’s why we have num_rows 2 for SYS_P41



BY FAQ