ORA-00604: error occurred at recursive SQL level 1, ora-00604recursive

ORA-00604: error occurred at recursive SQL level 1, ora-00604recursive

When you use an account ESCMOWNER in the test environment to ALTER the database, the following error is always reported:

ORA-00604: error occurred at recursive SQL level 1

ORA-20016: (ALTER) Action denied as it is a protected object!

ORA-06512: at line 7

I don't know what happened, so I want to track the current session, view the specific reason, and check that the SID of the current session is 967

SELECT USERENV('SID') FROM DUAL;

Log on to the system and view the SID of session ID = 967, SERIAL #

SELECT SID, SERIAL# FROM V$SESSION WHERE SID=967;

 

Trace sessions with the session ID and SERIAL #967,2276 as follows:

SQL> exec dbms_system.set_sql_trace_in_session(967,2276,true);

PL/SQL procedure successfully completed.

Enable the trace identifier in session 967, as shown below:

ALTER SESSION SET TRACEFILE_IDENTIFIER='kerry_test'

The ORACLE 10g trace file is located under $ ORACLE_BASE/admin/SID/udump to view the trace file,

[Oracle @ get-linux01 udump] $ ls * kerry *

Epps_ora_1960_kerry_test.trc

[Oracle @ get-linux01 udump] $ more epps_ora_1960_kerry_test.trc

The specific content is as follows:

View SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = 'Protect _ ESCMOWNER_BTR 'and find PROTECT_ESCMOWNER_BTR as the trigger. The specific script is as follows:

CREATE OR REPLACE TRIGGER PROTECT_ESCMOWNER_BTR

BEFORE DDL

ON ESCMOWNER.SCHEMA

BEGIN

   if sys.sysevent = 'ALTER' and sys.dictionary_obj_type in ('TRIGGER','VIEW','PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','SYNONYM') then

      NULL;

   elsif sys.sysevent = 'ANALYZE' then

        NULL;

      else

        RAISE_APPLICATION_ERROR(-20016,'('||sys.sysevent||') Action denied as it is a protected object!');

   end if;

END PROTECT_ESCMOWNER_BTR;

 

Conclusion:

This database trigger should be set by the former DBA, and I have been using the system update script, so this trigger has not been triggered. The problem is solved after the trigger is disabled.