Re: Synonym in trucate table, drop table ...

From: DA Morgan <>
Date: Sat, 20 Dec 2008 10:18:23 -0800
Message-ID: <> wrote:

> On Dec 19, 3:20 pm, joel garry <> wrote:

>> On Dec 19, 5:40 am, Mark D Powell <> wrote:
>>> On Dec 18, 2:16 pm, joel garry <> wrote:
>>>> On Dec 18, 10:46 am, wrote:
>>>>> am on Oracle on HP UNIX 11i.
>>>>> When I execute a statement:
>>>>> truncate table t;
>>>>> where t is a synonym for a table tx
>>>>> I get error
>>>>> ORA-00942: table or view does not exit
>>>>> However, if I do truncate table tx
>>>>> table is deleted.
>>>>> synonym however works for insert, select, delete, update etc.
>>>>> What is rationale for not recognizing synonyms in ceratin statements.
>>>>> Thanks
>>>> I suppose it would be, when you are doing DDL, you want to do it on
>>>> the proper object.
>>>> says what you can do, but not the rationale. So I guess that means
>>>> that's just the way it is. Also, consider the differences between
>>>> public and private synonyms. Maybe it just gets too hinky with the
>>>> possibilities of dependent objects with several degrees of separation.
>>>> Besides that, there are bugs, like this one "fixed in the
>>>> patchset" metalink Note:603093.1
>>>> jg
>>>> --
>>>> is bogus.
>>>> Spear phishing: text -
>>>> - Show quoted text -
>>> I look at it this way. DDL opertates on the object in the command.
>>> Only tables and the indexes on them can be truncated so truncate
>>> table_synonym is not valid because synonym is not a table and the only
>>> valid operations on a synonym are create/replace and drop.
>>> The truncate command has worked this way since it was introduced and
>>> since I am not usually the owner when I use the command I have used:
>>> "truncate owner.table_name" when I have needed to perform truncates.
>>> You can grant non-owners the ability to truncate a table via a stored
>>> procedure owned by the target table owner that uses dynamic SQL to
>>> issue the truncate command or better yet a procedure owned by a DBA
>>> privileged account that accepts the owner and table_name and compares
>>> it against an authorized to be truncated table list. This can help
>>> protect your system from soneone using the first type of routine to
>>> truncate the wrong table.
>>> HTH -- Mark D Powell --
>> So why can't you truncate over a link? Doesn't that specify a
>> particular table/owner? But it acts like a synonym, which I guess it
>> must be.
>> jg
>> --
>> is bogus.
>> If at first you don't succeed... jump off! Hide quoted text -
>> - Show quoted text -
> I noticed that Oracle allows truncate in some DDL statements but not
> in others. I do not find any consistenecy in what Oracle does, If it
> lets us delete rows from a table,  why it won't let you drop a table.

TRUNCATE and DELETE are two entirely different things. First off the first is DDL and the second DML. Secondly I can recover from the later with ROLLBACK. From the first, traditionally, I am running for the backup tapes. There is a huge difference between running FLASHBACK TABLE and FLASHBACK DATABASE.

But as to your initial comment: "allows truncate in some DDL statements." This is incorrect by almost any definition.

TRUNCATE is DDL and TRUNCATE and is not allowed, whatever that means, in any other DDL statement. It is a statement unto itself.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sat Dec 20 2008 - 12:18:23 CST

Original text of this message