-- Oracle throws ORA-06508 error when there is synchronization issue between PL/SQL -- user objects such as packages, even when all objects are compiled and in valid -- state. The query below shows how to identify the objects that are not -- synchronized. This is a PL/SQL error and is identified by having EXCEPTION -- handling and error logging in the code. -- Check the object status using query below. SELECT do.name d_name, u.name owner FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po, user$ u 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*/ AND do.owner#=u.user# AND do.type# = 5 ORDER BY u.name, do.name; -- Other best way to solve this issue is to recompile all objects by one-by-one -- or code shown below from reference [1] -- PL/SQL code to be executed by SYSDBA DECLARE CURSOR cur_syn IS SELECT do.name d_name, u.name owner FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po, user$ u 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*/ AND do.owner#=u.user# AND do.type# = 5 ORDER BY u.name, do.name; v_syn_name obj$.name%TYPE; v_tab_own user$.name%TYPE; BEGIN OPEN cur_syn; LOOP FETCH cur_syn INTO v_syn_name,v_tab_own; EXIT WHEN cur_syn%notfound; IF v_tab_own = 'PUBLIC' THEN EXECUTE IMMEDIATE 'ALTER PUBLIC SYNONYM '||v_syn_name||' COMPILE'; ELSE EXECUTE IMMEDIATE 'ALTER SYNONYM '||v_tab_own||'.'||v_syn_name||' COMPILE'; END IF; END LOOP; CLOSE cur_syn; END; / Reference 1. Ask Tom Reference 2. Compile Objects 3. Autonomous Transaction 4. Exception Handling 5. Error Logging
54998