Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state. So often we’d like to bar the use of it. Obviously, to truncate a table in another schema you need DROP ANY TABLE which one would hope very very very few people, aka none 🙂 would be granted, so really this is all about connections as the owner of a table.
Ideally, the solution is part of the application implementation, namely, that people never connect as the schema owner – that’s just good security practice for applications even without considering truncate. A common technique is to disable the ability to connect as the schema owner, so that all access must come from another schema which only has insert, update, delete, select access to the tables, or even better, the data access is entirely held behind a PL/SQL layer, and execute on those PL/SQL modules is all that is given.
But … out in the wild of the real world, the threat is often still present. So you can use a trigger as a stopgap measure.
SQL> create or replace trigger ddl_trigger 2 before truncate on scott.SCHEMA 3 declare 4 l_sysevent varchar2(25); 5 begin 6 select ora_sysevent into l_sysevent from dual; 7 8 if ( l_sysevent in ('TRUNCATE') ) 9 then 10 raise_application_error(-20000,'no no no no'); 11 end if; 12 end; 13 / Trigger created. SQL> truncate table t; truncate table t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20000: no no no no ORA-06512: at line 8