In stead of removing all tables in our schema as shown in post Drop All Tables In an Oracle Schema
, there are situations where we want to keep the tables while empty all the data stored in them. The following PL/SQL script does this.
Warning!!!!! The script below will empty all tables
under the current schema!!!
begin for r in (select table_name from user_tables order by table_name) loop begin execute immediate 'truncate table '||r.table_name; exception when others then null; end; end loop; end; /
Warning!!!!! The script above will empty all tables under the current schema!!!I saved it as file this_will_empty_all_user_tables.sql. I logged in my schema using sqlplus and run the scripts.
$sqlplus myschema/mypassword SQL> @this_will_empty_all_user_tables PL/SQL procedure successfully completed.All data stored in the tables in current schema are gone while the table structure remain unchanged.