Geek Answers Handbook

How to crop any table using its synonym in oracle?

How to crop 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?.
+4
source share
3 answers

The truncate operator cannot be used synonymously.

Synonyms cannot be used in a drag and drop table, nor can tables or clusters be omitted or cropped. If this is tried, the result is ORA-00942: the table or view does not exist

For instance,

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>
+3
source

You can use dynamic SQL for this, for example:

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;

, . , , , .

begin
  truncate_my_table@dblinkname;
end;
+1

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

Alert Log:

ORA-00604: error at the recursive level of SQL 1 ORA-14410: PERFORMANCE LOG TABLE published in the table referenced by the synonym

Follow the dynamic SQl above to remove / truncate it.

-1
source

More articles:


All Articles