Re: Synonym in trucate table, drop table ...
Date: Mon, 22 Dec 2008 09:31:50 -0800 (PST)
Message-ID: <d905b3fb-5df8-4bfc-bf93-2431899c05bf@g38g2000yqn.googlegroups.com>
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...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/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 Received on Mon Dec 22 2008 - 11:31:50 CST