ORACLE 11G trigger debug record Error: PLS-00201: identifier 'sys. DBMS_SYSTEM 'must be declared
1. The trigger content is as follows:
CREATE OR REPLACE TRIGGER"LOGON_DENIED_TO_ALERT"AFTER servererror ON DATABASEDECLAREmessage VARCHAR2(168);ip VARCHAR2(15);v_os_user VARCHAR2(80);v_module VARCHAR2(50);v_action VARCHAR2(50);v_pid VARCHAR2(10);v_sid NUMBER;v_program VARCHAR2(48);BEGINIF(ora_is_servererror(1017)) THEN-- get ip FOR remote connections :IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THENip := sys_context('userenv', 'ip_address');END IF;SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;SELECT p.spid, v.programINTO v_pid, v_programFROM v$process p, v$session vWHERE p.addr = v.paddrAND v.sid = v_sid;v_os_user := sys_context('userenv', 'os_user');dbms_application_info.read_module(v_module, v_action);message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||' logon denied from ' || nvl(ip,'localhost') || ' ' ||v_pid || ' ' || v_os_user || 'with ' || v_program || ' – ' ||v_module || ' ' || v_action;sys.dbms_system.ksdwrt(2, message);ENDIF;END;
2. Execute the following error message:
Compilation errors for TRIGGERTIMDBA. LOGON_DENIED_TO_ALERT
Error: PL/SQL: ORA-00942: table or viewdoes not exist
Line: 20
Text: SELECT sid INTO v_sid FROMsys. v _ $ mystat WHERE rownum <2;
Error: PL/SQL: SQL Statement ignored
Line: 20
Text: SELECT sid INTO v_sid FROMsys. v _ $ mystat WHERE rownum <2;
Error: PL/SQL: ORA-00942: table or viewdoes not exist
Line: 23
Text: FROM v $ process p, v $ session v
Error: PL/SQL: SQL Statement ignored
Line: 21
Text: SELECT p. spid, v. program
Error: PLS-00201: identifier 'sys. DBMS_SYSTEM 'must be declared
Line: 35
Text: sys. dbms_system.ksdwrt (2, message );
Error: PL/SQL: Statement ignored
Line: 35
Text: sys. dbms_system.ksdwrt (2, message );
Solution
Grant execute on sys. dbms_system to timdba;
3. An error is still reported during the next execution:
Compilation errors for trigger timdba. LOGON_DENIED_TO_ALERT
Error: PL/SQL: ORA-00942: table or viewdoes not exist
Line: 20
Text: SELECT sid INTO v_sid FROMsys. v _ $ mystat WHERE rownum <2;
Error: PL/SQL: SQL Statement ignored
Line: 20
Text: SELECT sid INTO v_sid FROMsys. v _ $ mystat WHERE rownum <2;
Error: PL/SQL: ORA-00942: table or viewdoes not exist
Line: 23
Text: FROM v $ process p, v $ session v
Error: PL/SQL: SQL Statement ignored
Line: 21
Text: SELECT p. spid, v. program
Solution:
Grant select on sys. v _ $ mystat to timdba;
4. debug the error message again. 3.
Compilation errors for TRIGGERTIMDBA. LOGON_DENIED_TO_ALERT
Error: PL/SQL: ORA-00942: table or viewdoes not exist
Line: 23
Text: FROM v $ process p, v $ session v
Error: PL/SQL: SQL Statement ignored
Line: 21
Text: SELECT p. spid, v. program
Solution:
Grant select on sys. v _ $ process to timdba;
Grant select on sys. v _ $ session to timdba;