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

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 - 00:13:57 CDT