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

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

Post by Davi » Sun, 25 Aug 2002 17:05:32



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)

 
 
 

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

Post by Stan » Mon, 26 Aug 2002 08:27:22



> 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

 
 
 

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

Post by Davi » Mon, 26 Aug 2002 10:19:48


but I can truncate table car. Also, I have already specify the owner (i.e.
A.car) in alter and drop table. Any idea?



> > 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

 
 
 

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

Post by Richard Foot » Tue, 27 Aug 2002 14:13:57


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


> but I can truncate table car. Also, I have already specify the owner (i.e.
> A.car) in alter and drop table. Any idea?




> > > 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

 
 
 

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

Post by Davi » Tue, 27 Aug 2002 22:05:13


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


> 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



> > but I can truncate table car. Also, I have already specify the owner
(i.e.
> > A.car) in alter and drop table. Any idea?




> > > > 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

 
 
 

1. Drop table versus truncate and drop index

I have a DTS package that bulk copies data from text files
into a staging table and then adds an index to speed the
parsing out of rows into another destination table. Then
indexes are adding to the destination table to speed some
data quality queries that run against it. At the moment
the package truncates the tables and drops the indexes
before moving the data.

Would it be more efficient to drop the tables and then re-
create them as opposed to truncating and then waiting on
the several drop index statements?

2. Newbie to Java, Hibernate

3. Help SP: for ALTER TABLE....DROP COLUMN

4. Connecting to SQL Server from IIS

5. Help SP: ALTER TABLE...DROP COLUMN

6. Red Clock

7. Problem with Drop Table and truncate table

8. English + Japanese + Chinese in same SQL Server

9. Truncate table vs drop table and fragmentation

10. Help: cannot drop table

11. help: cannot drop table

12. HELP: Table appears in SQLManager but cannot drop it

13. Help: Cannot drop table