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;
