Re: Be careful dropping users in 8.1.7!

Scary!

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 29, 2001 3:52 PM

> As if you didn't have enough to worry about, here's a new bug to keep an
eye
> out for. Ever since upgrading from 8.1.5 to 8.1.7, my client has been
> finding that table triggers would sporadically disappear mysteriously.
> Poof! Gone without a trace. Turned on auditing for all DDL, but that
didn't
> catch anything. The LAST_DDL_TIME of the triggerless table was not
updated.
>
> We finally tracked down the cause of the problem, and unearthed a new bug.
> If you have a user whose user id is the same as a table's object id, and
you
> drop that user, Oracle will drop all the triggers on the table. It
> presumably means to drop any user triggers for that user, such as a logon
> trigger, but the recursive sql fails to check the object type when
dropping
> the triggers.
>
> So if this query finds any matching users/tables, and you have triggers on
> the table, don't drop the user!
>
> select u.user#, u.name, o.owner, o.object_name, o.object_type, o.object_id
> from user$ u, dba_objects o
> where o.object_id = u.user#
> and object_type = 'TABLE'
> and owner != 'SYS';
>
> Development is working on a fix, the bug # 1803203 if anybody wants to
track
> it.
>
> Gary
>
> Gary Kirsh
> Next Extent, Inc.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kirsh, Gary
> INET: gary.kirsh_at_gs.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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).