Prev: What is "automated batching of data from computer to computer" in Named User License?
Next: why ora1801
From: jeremy on 3 Jul 2008 11:26
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?

--
jeremy

From: gazzag on 3 Jul 2008 11:50
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?
>
> --
> jeremy

Ouch! What about:

DROP PUBLIC SYNONYM "PUBLIC.SYS";

HTH

-g
From: gazzag on 3 Jul 2008 12:04
On 3 Jul, 16:50, gazzag <gar...(a)jamms.org> wrote:
> Ouch!  What about:
>
> DROP PUBLIC SYNONYM "PUBLIC.SYS";
>
> HTH
>
> -g- Hide quoted text -
>
> - Show quoted text -

Ignore previous post. I've just tried it on a test database and the
same error is returned. I too now have a kippered database :)

If the database in question is in ARCHIVELOG mode, would a PITR be
possible?

HTH

-g
From: jeremy on 3 Jul 2008 12:08
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

From: Mark D Powell on 3 Jul 2008 12:14
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.

-- Mark D Powell --
 |  Next  |  Last
Pages: 1 2
Prev: What is "automated batching of data from computer to computer" in Named User License?
Next: why ora1801