touchchandra touchchandra - 6 months ago 228
SQL Question

How to truncate any table using its synonym in oracle?

How to truncate any table using its synonym in oracle?

-- 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?.

Answer

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>