Truncate Table From a Remote Database

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;