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

On Dec 19, 3:20 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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...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&i...- 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. Received on Fri Dec 19 2008 - 20:47:44 CST