Hello,
I currently facing a big issue on my database. I already in contact with ORACLE support team, but after 1 month, no solutions.
To see the problem I ran the followin query (DEPENDENCY$ P_TIMESTAMP MISMATCH FOR VALID OBJECTS (Doc ID 1575501.1)):
set pagesize 10000
column d_name format a20
column p_name format a20
SELECT
do.obj# d_obj,
do.name d_name,
do.type# d_type,
po.obj# p_obj,
po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po
WHERE P_OBJ#=po.obj#(+)
AND D_OBJ#=do.obj#
AND do.status=1 /*dependent is valid*/
AND po.status=1 /*parent is valid*/
AND po.stime!=p_timestamp /*parent timestamp not match*/
ORDER BY 2,1;
Results are :
D_OBJ D_NAME D_TYPE P_OBJ P_NAME
---------- -------------------- ---------- ---------- --------------------
P_Timestamp STIME X
-------------------- -------------------- --------
73618 ORDERAPPROVALHISTORY 13 73604 ORDERAPPROVALHISTORY
ENTRY_NTAB ENTRY
01-JUL-2013 12:51:30 18-SEP-2014 08:44:08 *DIFFER*
73633 OWEADMIN 13 73579 AIRCRAFT
01-JUL-2013 12:51:27 01-JUL-2013 12:51:31 *DIFFER*
73757 SYSNTzmKFBJVQWzDgRAA 2 73604 ORDERAPPROVALHISTORY
DujTPdQ== ENTRY
01-JUL-2013 12:51:30 18-SEP-2014 08:44:08 *DIFFER*
The problem is that the proposed fix doesn't work :
SQL>alter session set events '10826 trace name context forever, level 1';
SQL>alter type SMM.ORDERAPPROVALHISTORYENTRY_NTAB compile;
alter type SMM.ORDERAPPROVALHISTORYENTRY_NTAB compile
*
ERROR at line 1:
ORA-02311: cannot alter with COMPILE option a valid type with type or table
dependents
And I don't know how can I compile a type with dependances (I can't afford to drop tables because there are data inside)
Due to this issue (I think), I can't alter a type because it make my application craches.
Any help will be very useful.
Best regards.