How to truncate any table using its synonym in oracle?

Question

-- in Server_A
Create Table table_a ( col int);

-- in server_B
CREATE SYNONYM syn_table_a FOR table_a@dblink_server_a;

--insert into
INSERT INTO syn_table_a values (1);

--Truncate
How to truncate table using synonym only?.

Yes I already found that truncate is not possible. I am using delete operation to empty table. Is there any alternative. — touchchandra, Mar 25 '15 at 03:08

score 3 · Accepted Answer · answered Mar 24 '15 at 08:55

A truncate statement cannot be used on a synonym.

Synonyms cannot be used in a drop table, drop view or truncate table/cluster statements. If this is tried, it results in a ORA-00942: table or view does not exist

For example,

SQL> CREATE TABLE t(col NUMBER);

Table created.

SQL>
SQL> CREATE SYNONYM t_syn FOR t;

Synonym created.

SQL>
SQL> TRUNCATE TABLE t_syn;
TRUNCATE TABLE t_syn
               *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Jeffrey Kemp · Answer 2 · 2015-03-25T03:31:27.233

2

You could use dynamic SQL to do it, e.g.:

declare
  d varchar2(1000);
begin
  select 'TRUNCATE TABLE "' || table_owner || '"."' || table_name || '"'
  into d
  from all_synonyms
  where synonym_name = 'MYSYNONYM';
  execute immediate d;
end;

If the table is accessed via a database link, this will not work. In that case, you could create a procedure on the remote instance that does the truncate, then call that procedure across the database link, e.g.

begin
  truncate_my_table@dblinkname;
end;

edited Mar 25 '15 at 03:31

answered Mar 25 '15 at 01:11

Jeffrey Kemp

59,135
14
106
158

Table is actually in another server and synonym is using dblink. Currently I m using delete operation to empty table. Is that a risk. – touchchandra Mar 25 '15 at 03:02
1

Updated answer. You can call a procedure across a database link which could do the truncate. – Jeffrey Kemp Mar 25 '15 at 03:32
I'm not sure what risk you're referring to. Risk of unauthorised use? Risk of errors? Risk of performance issues? – Jeffrey Kemp Mar 25 '15 at 03:33
1

I am referring to any possible risk using delete instead of truncate other than performance issue. – touchchandra Mar 25 '15 at 10:18
There's no risk unless you don't understand the fundamental difference between a delete and a truncate. – Jeffrey Kemp Mar 25 '15 at 11:19

score -1 · Answer 3 · edited May 27 '16 at 13:10

-1

In Oracle, you can also get ORA-14410 while trying to drop/truncate a table using synonym.

The alert log:

ORA-00604: error occurred at recursive SQL level 1 ORA-14410: RPI LOCK TABLE issued to table referenced through synonym

Follow the above dynamic SQl to drop/truncate it.

edited May 27 '16 at 13:10

abarisone

3,707
11
35
54

answered May 27 '16 at 02:20

Bikash Kumar Kar

31
2

Please check this[URL](http://stackoverflow.com/help) it will be handy to lift your content quality up – Willie Cheng May 27 '16 at 02:41

How to truncate any table using its synonym in oracle?

3 Answers3