Re: The light dawns - public synonym called SYS - how to drop?

From: Palooka <nobody_at_nowhere.com>
Date: Thu, 03 Jul 2008 23:19:51 +0100
Message-ID: <igcbk.229864$M63.64135@newsfe13.ams2>


Mark D Powell wrote:

> On Jul 3, 12:08 pm, jeremy <jeremy0..._at_gmail.com> wrote:

>> 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
>>
>> Elapsed: 00:00:00.49
>> 16:57:26 SQL> alter trigger XDB.XDB_PI_TRIG disable;
>>
>> Trigger altered.
>>
>> Elapsed: 00:00:00.15
>> 16:57:28 SQL> drop public synonym sys force;
>>
>> Synonym dropped.
>>
>> Elapsed: 00:00:00.58
>> 16:57:32 SQL> alter trigger XDB.XDB_PI_TRIG enable;
>>
>> Trigger altered.
>>
>> Elapsed: 00:00:00.18
>>
>> After which everything was fine again.
>>
>> --
>> jeremy- Hide quoted text -
>>
>> - Show quoted text -
> 
> Jeremy, thank you for the follow-up solution post.  Someone may
> actually query the  archives before posting and find a solution to
> their problem; this does happen on occassion.
> 

True I suppose, but allowing students to create public synonyms? And OP says he doubts his setup is broken (or words to that effect)?

Needs a complete rethink in terms of privileges granted, IMHO. Not the student's fault.

Palooka Received on Thu Jul 03 2008 - 17:19:51 CDT

Original text of this message