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

On Dec 19, 5:40 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

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

--
@home.com is bogus.
If at first you don't succeed... jump off!
http://video.signonsandiego.com/vmix_hosted_apps/p/media?id=2531504&item_index=1&query=dramatic&sort=NULL