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

On Dec 18, 2:16 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 18, 10:46 am, zigzag..._at_yahoo.com wrote:
>
>
>
>
>
> >  am on Oracle 10.2.0.3 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.  http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...
> 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 10.2.0.5
> patchset" metalink Note:603093.1
>
> jg
> --
> @home.com is bogus.
> Spear phishing:  http://ibtimes.com/articles/20081217/personalized-spam-rising-sharply...- Hide quoted 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 -- Received on Fri Dec 19 2008 - 07:40:09 CST