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

On Dec 20, 8:30 pm, zigzag..._at_yahoo.com wrote:
> On Dec 20, 5:43 pm, Palooka <nob..._at_nowhere.com> wrote:
>
>
>
>
>
> > zigzag..._at_yahoo.com wrote:
> > > 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...
> > >>>>>> - 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...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-Hidequoted 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/statem...,
> > > 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.
>
> > Nonsense.
>
> > The purpose of synonyms is to allow location transparency at runtime. At
> > DDL time  it is absolutely correct that you should specify owner/object
> > name of that which you are altering, truncating, or dropping.
>
> > And if you are doing DDL at runtime, then may God have mercy on your soul,
>
> > Palooka- Hide quoted text -
>
> > - Show quoted text -
>
> Oracle itself does not follow your thinking because it allows synonyms
> in some DDL's.Typically when one is using synonyms one would have
> tested them in development environment. If one has not tested,
> deleting rows using synonyms can have disastorous consequences as well.- Hide quoted text -
>
> - Show quoted text -

Zig, your example is flawed as deleting rows (delete statement) is not DDL it is DML. There is a huge difference in the two types of statements.

Synonyms exist mostly for use with DML (insert, update, delete, and select) statements.

HTH -- Mark D Powell -- Received on Mon Dec 22 2008 - 11:07:14 CST