ORACLE/ADMIN2016. 2. 18. 10:09
반응형

Truncate Table From a Remote Database


Truncating from a remote database is not allowed. On a development database environment, developers are in need of truncating certain tables on a regular basis. Delete is allowed over database link, that’s an option. But, that’s a bad example for allowing fragmentation and high water mark over the period of time.

If you try to truncate a table on a remote database, you will get the following error:
ORA-02021: DDL operations are not allowed on a remote database.

As, procedure can be executed over a dblink, so following workaround can be a good option on such scenario.

1.    Create a procedure on Remote Database that can truncate
CREATE OR REPLACE PROCEDURE
Trunc_Rem_Tab(p_table_name VARCHAR2) AS
   v_sql_error_code PLS_INTEGER;
   v_sql_error_message VARCHAR2(512);
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;
EXCEPTION
   WHEN OTHERS THEN
      v_sql_error_code := SQLCODE;
      v_sql_error_message := SQLERRM(v_sql_error_code);
      DBMS_OUTPUT.ENABLE(5000);
      DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
      DBMS_OUTPUT.PUT_LINE(v_sql_error_message);
END Truncate_Remote_Table;

2.    Execute the procedure from local database.
BEGIN
   Trunc_Rem_Tab@REMOTE_LINK('TEST');
END;


반응형
Posted by [PineTree]