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 DATABASE

DECLARE

Message 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);

BEGIN

IF (Ora_is_servererror (1017)) Then

--Get IP for remote connections:

IF Upper (Sys_context (' Userenv ', ' network_protocol ')) = ' TCP ' Then

IP: = Sys_context (' Userenv ', ' ip_address ');

END IF;

SELECT sid into V_sid from Sys.v_$mystat WHERE rownum < 2;

SELECT P.spid, V.program

Into V_pid, V_program

From V$process p, V$session v

WHERE p.addr = v.paddr

and 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;

----------------------------------------------------------------------------------------------------------- -----
< Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal liability !>
Original Blog Address: http://blog.csdn.net/mchdba/article/details/45898169
Hara Douglas fir (MCHDBA)
--------------------------------------------------------------------------------------------------------------- -

2, execute the 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);

Solutions

Grant execute on Sys.dbms_system to TIMDBA;

3, re-execution is still an error:

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

Workaround:

Grant SELECT on Sys.v_$mystat to TIMDBA;

4, debug the error message again3

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

Workaround:

Grant SELECT on Sys.v_$process to TIMDBA;

Grant SELECT on Sys.v_$session to TIMDBA;

Reference article address: Https://blogs.oracle.com/mandalika/entry/resolving_pls_00201_identifier_dbms

ORACLE 11G trigger Debug record error:pls-00201:identifier ' SYS. Dbms_system ' must be declared