Re: Help: Can select, truncate but cannot alter and drop a table

Hi Richard,

    Thanks for you suggestion. ORA-00604 is something related with the internal dictionary. We are still finding the solutions. I think I may need to restore the backup. But don't know why there is something wrong about the internal dictionary.

    (By the way, I am sure that I login as A, owner of car of is A.)

Thanks

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:Exia9.15184$g9.47836_at_newsfeeds.bigpond.com...
> Hi David,
>
> Let me get this right.
>
> You have a table called car owned by A.
>
> You have a (public) synonym called car for the car table owned by A. Note
> that the only way to have both the table name and synonym name as car is
for
> the synonym to be public. So when you refer to car, it doesn't really make
> much difference, you refer to the table car each time.
>
> When you TRUNCATE TABLE car it works fine (when connected in as *A*).
>
> But when you DROP TABLE car it doesn't work (when again connected in as
> *A*).
>
> If that's the case, weird indeed.
>
> However, are you sure, really really sure you are connected in as A each
> time ? The only way this scenario appears possible is if you are *not*
> connected in as A when attempting to drop the table (assuming you can
> successfully connect in as A and truncate the table). The fact you have a
> synonym appears to me anyway to be a red (or bluish) herring. You would
get
> a different error message if you attempted to perform DDL through a
synonym
> but the fact they are named the same suggests the operation indeed is
being
> attempted directly on the table.
>
> If you can spool the sequence of events and post, it might shed some light
> (to me anyway).
>
> Good Luck
>
> Richard
>
>
>
>
>
>
>
>
>
> "David" <davidlee_at_diamond.com> wrote in message
> news:ak9b4g$lfm2_at_imsp212.netvigator.com...
> > but I can truncate table car. Also, I have already specify the owner
(i.e.
> > A.car) in alter and drop table. Any idea?
> >
> > "Stan" <stan0074_at_yahoo.com> wrote in message
> > news:3D6817E2.70605_at_yahoo.com...
> > > David wrote:
> > >
> > > > I need some help. Here is my question (oracle 8.1.7 on Solaris):
> > > >
> > > > when I alter table A.car or drop table A.car (login as A, owner of
car
> > is A
> > > > in SQL*Plus), following errors shown:
> > > >
> > > > ORA-00604: error occurred at recursive SQL level 1
> > > > ORA-00942: table or view does not exist
> > > >
> > > > However, I can truncate table car ("truncate table car", login as A)
> or
> > > > "select * from car" in SQL*Plus. Anyone knows why I cannot alter
table
> > or
> > > > drop table car? (Synonym "car" is pointed to A.car)
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > > David,
> > >
> > > car is a 'synonym' and you can't issue any table related ddl commands
> > > against a synonym like alter table, drop table or truncate table...
> > >
> > > --
> > > hth,
> > > Stan
> > >
> >
> >
>
>
Received on Mon Aug 26 2002 - 08:05:13 CDT