Oracle11g trigger debug record Error: PLS-00201: identifier 'sys. DBMS_SYST

Oracle11g trigger debug record Error: PLS-00201: identifier

Oracle 11g trigger debug record Error: PLS-00201: identifier

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;

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;

This article permanently updates the link address: