Synonym in trucate table, drop table ...
zigz...@yahoo.com
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
joel garry
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/statements_7001.htm#sthref7346
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-study-finds.htm
Palooka
> I suppose it would be, when you are doing DDL, you want to do it on
> the proper object.
Yep, that would seem to be the case, and as you impute, not unreasonable:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> create table x (y varchar2(100));
Table created.
SQL> insert into x values ('Hello World');
1 row created.
SQL> commit;
Commit complete.
SQL> create synonym z for x;
Synonym created.
SQL> delete from z;
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> truncate table z;
truncate table z
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table z;
drop table z
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table x purge;
Table dropped.
SQL> drop synonym z;
Synonym dropped.
Palooka
Mark D Powell
> On Dec 18, 10:46 am, zigzag...@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
> 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.
>
> - 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 --
joel garry
>
> > - 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&item_index=1&query=dramatic&sort=NULL
zigz...@yahoo.com
>
> > > - 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.
>
> - 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.
DA Morgan
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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
zigz...@yahoo.com
> >>>> - 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.
>
> >> - 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
> 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.
Palooka
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
zigz...@yahoo.com
> >>>>>> - 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.
> >>>> - 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...@x.washington.edu (replace x with u to respond)
>
> >> - Show quoted text -
>
> > Sorry, I meant to say synoymns are allowed in severl DDL statements:
> > 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,
>
>
> - 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.
ddf
> > >>>>>> - 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...@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.
The list of DDL statements where synonyms are allowed is short and non-
destructive to the referenced object:
"You can refer to synonyms in the following DDL statements: AUDIT,
NOAUDIT, GRANT, REVOKE, and COMMENT."
Not one of those DDL operations modifies the structure of the object.
On DDL statements which DO modify object structure, including
TRUNCATE, the case is clear; synonyms are NOT permitted. I see no
inconsistency here.
> Typically when one is using synonyms one would have
> tested them in development environment.
One would think ...
> If one has not tested,
> deleting rows using synonyms can have disastorous consequences as well.- Hide quoted text -
>
However a delete is a recoverable operation, either by a rollback of
the existing transaction or through tools such as flashback query or
LogMiner. Truncates are not, outside of a tablespace restore from a
backup or a table restore from a recent export. It's a shame that
you're inconvenienced by this; it appears perfectly logical to me.
> - Show quoted text -
David Fitzjarrell
Mark D Powell
>
> > > - 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.
>
> - Show quoted text -
DDL commands are not supported over a database link. What we have
always done is write a procedure that uses execute immediate (or
previously dbms_sql) to issue DDL and store it on the target system.
You can then execute the procedure via the link.
Mark D Powell
> > >>>>> 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.
> > >>>> - 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...@x.washington.edu (replace x with u to respond)
>
> > >> - 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,
>
> - 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.
ddf
> > >>>> 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...@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
zigz...@yahoo.com
> > > >>> 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.
>
> > > >> - 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...@x.washington.edu (replace x with u to respond)
>
> - Show quoted text -
thanks for detailed explanation.
zigz...@yahoo.com
> > > >>> 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.
>
> > > >> - 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...@x.washington.edu (replace x with u to respond)
>
> - Show quoted text -
ddf
>
> > > > >> - 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...@x.washington.edu (replace x with u to respond)
>
> - Show quoted text -
You're welcome.
David Fitzjarrell