Re: The light dawns - public synonym called SYS - how to drop?
Date: Thu, 3 Jul 2008 09:08:21 -0700 (PDT)
On 3 Jul, 16:50, gazzag <gar..._at_jamms.org> wrote:
> On 3 Jul, 16:26, jeremy <jeremy0..._at_gmail.com> wrote:
> > With reference to my other post on various SYS packages being
> > unavailable, I now know the cause. Despite my declarations of
> > confidence, it turns out that one of our guys (a student on work
> > placement as it happens) wanted to create a public synonym and called
> > it "SYS".
> > 2 things
> > 1) am surprised that Oracle (10gR2) didn't object to this
> > 2) we are unable to drop the synonym
> > 16:25:05 SQL> drop public synonym sys;
> > drop public synonym sys
> > *
> > ERROR at line 1:
> > ORA-04098: trigger 'XDB.XDB_PI_TRIG' is invalid and failed re-
> > validation
> > Elapsed: 00:00:00.18
> > 16:25:58 SQL>
> > Anyone know how to tidy up this mess?
> Ouch! What about:
> DROP PUBLIC SYNONYM "PUBLIC.SYS";
Following sorted it out:
16:57:13 SQL> drop public synonym sys force;
drop public synonym sys force
ERROR at line 1:
ORA-04098: trigger 'XDB.XDB_PI_TRIG' is invalid and failed re- validation
16:57:26 SQL> alter trigger XDB.XDB_PI_TRIG disable;
16:57:28 SQL> drop public synonym sys force;
16:57:32 SQL> alter trigger XDB.XDB_PI_TRIG enable;
After which everything was fine again.
-- jeremyReceived on Thu Jul 03 2008 - 11:08:21 CDT