ORACLE 11G trigger debug record Error: PLS-00201: identifier & #39; SYS. DBMS_SYSTEM & #39; must be declared, oracle11g

Source: Internet
Author: User
Tags get ip

ORACLE 11G trigger debug record Error: PLS-00201: identifier 'sys. DBMS_SYSTEM 'must be declared, oracle11g

 

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;

 

 

 Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.csdn.net/mchdba/article/details/45898169
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------

 

 

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;

 

 

 

 

 

 

 

Reference Article address: https://blogs.oracle.com/mandalika/entry/resolving_pls_00201_identifier_dbms

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.