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

On Dec 20, 1:18 pm, DA Morgan <damor..._at_psoug.org> wrote:
> zigzag..._at_yahoo.com wrote:
> > 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...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.
>
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Sorry, I meant to say synoymns are allowed in severl DDL statements: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm#sthref7346, so saying that synonyms are only allowed in DML is not correct.

Puropose of synonym is to use another name for existing name. If Oracle allows this, it should be consistent through all statements. You or anyone can come up with your own reasons why truncate is not allowed with synonyms, but in my view there is really no good reason why synoymns should not be allowed every where. Received on Sat Dec 20 2008 - 15:27:28 CST