Re: Synonym in trucate table, drop table ...
Date: Mon, 22 Dec 2008 09:03:45 -0800 (PST)
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.
> @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 -
DDL commands are not supported over a database link. What we have always done is write a procedure that uses execute immediate (or previously dbms_sql) to issue DDL and store it on the target system. You can then execute the procedure via the link.
HTH -- Mark D Powell -- Received on Mon Dec 22 2008 - 11:03:45 CST