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

From: ddf <oratune_at_msn.com>
Date: Mon, 22 Dec 2008 13:27:11 -0800 (PST)
Message-ID: <5bee5af9-523f-4f2d-b8fc-d1338b6ece0b@k36g2000yqe.googlegroups.com>


On Dec 22, 2:49 pm, zigzag..._at_yahoo.com wrote:
> On Dec 22, 12:31 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
> > Comments embedded.
>
> > On Dec 20, 3:27 pm, 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...-
>
> > > > >> - 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-Hidequotedtext -
>
> > > > - 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.
>
> > And all of those DDL statements have the form of:
>
> > <command> <options> ON <object>;
>
> > For example:
>
> > SQL> --
> > SQL> -- Create table
> > SQL> --
> > SQL> -- Use a logical name
> > SQL> --
> > SQL> create table sales_data(
> >   2          sales_id   number,
> >   3          year    number,
> >   4          quarter number,
> >   5          sales   number
> >   6  );
>
> > Table created.
>
> > SQL>
> > SQL> --
> > SQL> -- Grant all privileges on our
> > SQL> -- sales_data table to another
> > SQL> -- user
> > SQL> --
> > SQL> grant all on sales_data to yarkput;
>
> > Grant succeeded.
>
> > SQL>
> > SQL> --
> > SQL> -- Connect as that other user
> > SQL> --
> > SQL> connect yarkput/^^^^^^^^^^^^^
> > Connected.
> > SQL>
> > SQL> --
> > SQL> -- Create a synonym
> > SQL> --
> > SQL> -- Use a mysterious name
> > SQL> -- having nothing at all to
> > SQL> -- do with the table's purpose
> > SQL> --
> > SQL> -- This flies in the face of providing
> > SQL> -- transparency, but, hey, it's
> > SQL> -- 'legal'
> > SQL> --
> > SQL> create synonym grompenflumper
> >   2  for bing.sales_data;
>
> > Synonym created.
>
> > SQL>
> > SQL> --
> > SQL> -- Execute an approved DDL command
> > SQL> -- against the synonym
> > SQL> --
> > SQL> audit select on grompenflumper;
>
> > Audit succeeded.
>
> > SQL>
> > SQL> --
> > SQL> -- Attempt to truncate the base table
> > SQL> -- through the synonym
> > SQL> truncate table grompenflumper;
> > truncate table grompenflumper
> >                *
> > ERROR at line 1:
> > ORA-00942: table or view does not exist
>
> > SQL>
> > SQL> --
> > SQL> -- Gee, it appears that grompenflumper isn't
> > SQL> -- a table ...
> > SQL> --
> > SQL> -- which it isn't, it's a synonym
> > SQL> -- and there is no 'truncate synonym' command
> > SQL> --
> > SQL> --
> > SQL> -- We try again, using the fully qualified
> > SQL> -- object name
> > SQL> --
> > SQL> -- Goll-ee, we succeed
> > SQL> --
> > SQL> truncate table bing.sales_data;
>
> > Table truncated.
>
> > SQL>
>
> > > Puropose of synonym is to use another name for existing name.
>
> > No, it isn't.
>
> > > If
> > > Oracle allows this, it should be  consistent through all statements.
>
> > It is.  Why do you fail to see this?
>
> > > You or anyone can come up with your own reasons why truncate is  not
> > > allowed with synonyms,
>
> > Show me a 'truncate synonym' command in Oracle.  One doesn't exist as
> > a synonym is a 'pointer' to another object, allowing a user to
> > reference that object absent the owner.object_name syntax.  This is
> > why the 5 DDL command that *are* allowed work; they allow the synonym
> > to be resolved to its base object:
>
> > audit ...  on ...
> > noaudit ... on ...
> > grant ... on ... to ...
> > revoke ... on ... from ...
> > comment ... on ....
>
> > > but in my view there is really no good reason
> > > why  synoymns  should not be allowed every where.- Hide quoted text -
>
> > The fact that a synonym is NOT a table is a pretty good one in my
> > estimation.
>
> > > - Show quoted text -
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks a lot for detailed explanation.- Hide quoted text -
>
> - Show quoted text -

You're welcome.

David Fitzjarrell Received on Mon Dec 22 2008 - 15:27:11 CST

Original text of this message