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

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...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.
>
> 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 for detailed explanation. Received on Mon Dec 22 2008 - 12:20:27 CST