Prev: What is "automated batching of data from computer to computer" in Named User License?
Next: why ora1801
From: gazzag on 3 Jul 2008 12:21
On 3 Jul, 17:08, jeremy <jeremy0...(a)gmail.com> wrote:
>
> 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 -
>

Ah, you beat me to it! An interesting academic exercise
nonetheless :)

Cheers,

-g
From: Terry Dykstra on 3 Jul 2008 14:11
"gazzag" <gareth(a)jamms.org> wrote in message
news:8b87e8ad-691d-49f9-8d04-16fee37af408(a)e39g2000hsf.googlegroups.com...
> On 3 Jul, 17:08, jeremy <jeremy0...(a)gmail.com> wrote:
>>
>> 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 -
>>
>
> Ah, you beat me to it! An interesting academic exercise
> nonetheless :)
>
> Cheers,
>
> -g

But what got potentially out of whack in the data dictionary by virtue of
that trigger not running?

Terry Dykstra


From: Palooka on 3 Jul 2008 18:19
Mark D Powell wrote:
> On Jul 3, 12:08 pm, jeremy <jeremy0...(a)gmail.com> wrote:
>> On 3 Jul, 16:50, gazzag <gar...(a)jamms.org> wrote:
>>
>>
>>
>>
>>
>>> On 3 Jul, 16:26, jeremy <jeremy0...(a)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
From: phil_herring on 3 Jul 2008 19:37
On Jul 4, 8:19 am, Palooka <nob...(a)nowhere.com> wrote:

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

Not that uncommon, I suspect. I recently had a developer complain to
me that he couldn't do what he wanted in the DB. He said, "in the last
place I worked, we all had the DBA role". I had to repeat my answer a
few times before it sank in that he wasn't going to get what he
wanted.

-- Phil
From: Vladimir M. Zakharychev on 4 Jul 2008 15:28
On Jul 3, 10:11 pm, "Terry Dykstra" <tddyks...(a)forestoil.ca> wrote:
> "gazzag" <gar...(a)jamms.org> wrote in message
>
> news:8b87e8ad-691d-49f9-8d04-16fee37af408(a)e39g2000hsf.googlegroups.com...
>
>
>
> > On 3 Jul, 17:08, jeremy <jeremy0...(a)gmail.com> wrote:
>
> >> 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 -
>
> > Ah, you beat me to it!  An interesting academic exercise
> > nonetheless :)
>
> > Cheers,
>
> > -g
>
> But what got potentially out of whack in the data dictionary by virtue of
> that trigger not running?
>
> Terry Dykstra

Most probably nothing. XDB is optional, so are its triggers, including
DDL ones. This particular trigger only handles drop/truncate of
XMLType tables, so it should not affect DROP SYNONYM.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
First  |  Prev  | 
Pages: 1 2
Prev: What is "automated batching of data from computer to computer" in Named User License?
Next: why ora1801