Re: V. Urgent - Got Stuck with trigger

From: chaos <chaospku_at_163.net>
Date: Mon, 22 Jul 2002 00:13:35 -0800
Message-ID: <F001.0049D7F4.20020722001335@fatcity.com>

sam dŁ¬

     hei, it is easy,
	logon as osuser and drop the trigger, it is ok.

I tested it on my 920 on linux:
SQL> CREATE OR REPLACE TRIGGER tpp
  2 before alter or create or drop on database   3 declare

  4          uname varchar2(20);
  5     begin
  6         select username into uname from v$session;
  7        if uname != 'SYSTEM' then
  8            raise_application_error(-20002,'notallowed');
  9         end if;

 10 end;
 11 /

Trigger created.

SQL> conn system/manager
Connected.
SQL> drop trigger tpp;

Trigger dropped.

Connect as sysdba also works:
SQL> create table t as select * from user_objects; create table t as select * from user_objects

                                *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

SQL> conn / as sysdba
Connected.
SQL> drop trigger system.tpp;

Trigger dropped.

also you can try:
add _system_trigger_enabled = false in initsid.ora and then bounce the database and drop it, remove it from the initfile and restart , it will be ok.

 2002-07-21 22:38:00 You wrote:
>Hi List,
>My friend has foll.(Big)problem.
>
>He'was testing the following trigger n now stuck.
>Do not execute this trigger on ur side.
>
>CREATE OR REPLACE TRIGGER tpp
> before alter or create or drop on database
>declare
> uname varchar2(20);
> begin
> select osuser into uname from v$session;
> if uname != 'Sachin' then
> raise_application_error(-20002,'not
>allowed);
> end if;
> end;
>
>Now he can't modify/drop this trigger, He gets the
>follow error:
>ERROR at line 1:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-01422: exact fetch returns more than requested
>number of rows
>ORA-06512: at line 4
>
>---------------------------------
>One thing I suggested is to make sure
>that there is only one session on server.
>Is that possible ?
>
>What are the other solutions ??
>
>(Do not execute this trigger on ur side. if u don't
>know the solution)
>
>Thx & Regards,
>Sam
>
>
>
>__________________________________________________
>Do You Yahoo!?
>Yahoo! Health - Feel better, live better
>http://health.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: sam d
> INET: sam_orafan_at_yahoo.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chaos
  INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jul 22 2002 - 03:13:35 CDT