Re: HELP HELP : ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist
Date: 6 Feb 2004 18:09:02 -0800
Message-ID: <5c91cce9.0402061809.1b0e2472_at_posting.google.com>
Hi,
i got this error messsage:
[oracle_at_icems oracle]$ sqlplus /'as sysdba'
SQL*Plus: Release 9.2.0.2.0 - Production on Sat Feb 7 09:45:33 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
SQL> grant select on v$session to system; grant select on v$session to system
*
ERROR at line 1:
ORA-04098: trigger 'SYSTEM.LOG_ERRORS_TRIG' is invalid and failed re-validation ORA-02030: can only select from fixed tables/views
Now, my user unable to do any DDL or DML. Pls advice! TQ!
regards,
tracy kim
"DBA Infopower Support" <support_at_dbainfopower.com> wrote in message news:<J7CdnUs32ONM_L7dRVn-hg_at_comcast.com>...
> Hello Tracy,
>
> Any DDL (like "create") would perform commit internally before and after
> DDL ix executed - I.e. you can not rollback DDL.
>
> Looks like you run the script as user 'SYSTEM'. As a "sys" user issue
> "grant select on v_$session to system;" This would resolve issue of
> "ORA-00942: table or view does not exist".
>
> Please, let us know if this helps.
>
> Regards,
>
> Support
>
> DBA Infopower
>
> https://www.dbainfopower.com
>
>
>
> The advice provided by DBA Infopower are provided "as is" with no warranty.
> DBA Infopower expressly disclaims any warranty, regarding the advice
> including any implied warranty of merchant fitness for a particular purpose
> aviating course of dealing and/or performance. DBA Infopower does not
> warrant that the advices provided by DBA Infopower will be free from bias,
> detests, errors, eavesdropping or listening. DBA Infopower shall not be
> responsible for the quality of information or the authentication of the
> services or details given by experts on the advice. By using this advice the
> user of the advice accepts the terms and conditions of this statement.
>
>
>
>
> "tracy" <tracykim10_at_yahoo.com.hk> wrote in message
> news:5c91cce9.0402060057.3d6ca551_at_posting.google.com...
> > Hi, I really need help.
> >
> > I run this script and error message appeal as below:
> >
> > drop trigger log_errors_trig;
> > drop trigger log_errors_trig
> > ERROR at line 1:
> > ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist
> >
> > drop table log_errors_tab;
> > drop table log_errors_tab
> > ERROR at line 1:
> > ORA00942: table or view does not exist
> >
> >
> > create table log_errors_tab (
> > error varchar2(30),
> > timestamp date,
> > username varchar2(30),
> > osuser varchar2(30),
> > machine varchar2(64),
> > process varchar2(8),
> > program varchar2(48));
> >
> > create or replace trigger log_errors_trig
> > after servererror on database
> > declare
> > var_user varchar2(30);
> > var_osuser varchar2(30);
> > var_machine varchar2(64);
> > var_process varchar2(8);
> > var_program varchar2(48);
> > begin
> > select username, osuser, machine, process, program
> > into var_user, var_osuser, var_machine, var_process, var_program
> > from sys.v_$session
> > where audsid = userenv('sessionid');
> >
> > insert into log_errors_tab
> > values(dbms_standard.server_error(1),sysdate,var_user,
> > var_osuser,var_machine,var_process,var_program);
> > end;
> > /
> > WARINING: trigger created with compilation errors.
> >
> > SQL>roll back;
> > Rollback complete.
> >
> > After that i got error message in my SQL:
> > ERROR at line 1:
> > ORA-04098: trigger 'SYSTEM.LOG_ERRORS_TRIG' is invalid and failed
> > re-validation
> >
> >
> > I check under user_objects & user_errors, result as below:
> > 1 select * from user_objects
> > 2* WHERE STATUS <>'VALID'
> > SQL> /
> >
> > OBJECT_NAME
> > --------------------------------------------------------------------------
> > SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
> > CREATED LAST_DDL_
> > ------------------------------ --------- --------------
> > ------------------ --------- ---------
> > TIMESTAMP STATUS T G S
> > ------------------- ------- - - -
> > LOG_ERRORS_TRIG
> > 47321 TRIGGER
> > 06-FEB-04 06-FEB-04
> > 2004-02-06:14:56:15 INVALID N N N
> >
> >
> > SQL> select * from user_errors;
> >
> > NAME TYPE SEQUENCE LINE
> > POSITION
> > ------------------------------ ------------ --------- ---------
> > ---------
> > TEXT
> > --------------------------------------------------------------------------
> > LOG_ERRORS_TRIG TRIGGER 1 10
> > 13
> > PL/SQL: ORA-00942: table or view does not exist
> >
> > LOG_ERRORS_TRIG TRIGGER 2 8
> > 2
> > PL/SQL: SQL Statement ignored
> >
> >
> > How can i fix this problem? I roll back after I run the trigger, why
> > this still happened?
> >
> >
> > Regards.
> > tracy kim
Received on Sat Feb 07 2004 - 03:09:02 CET