rem 
rem
rem Truncate all non-sys tables in a database. 
rem
rem Note: I would add the schema owner to the sub-query where clause
rem otherwise if this script will truncate every table in the database.
rem
rem Last Change 07/31/02 by Ed Campbell
rem
set linesize 132
set pagesize 0
set heading off
set verify off
set echo off
set feedback off
set termout off
spool truntblsxx.sql
prompt spool truntblsxxx.lis
select  'TRUNCATE TABLE '||table_name||' REUSE STORAGE;'
 from user_tables
where table_name in (select table_name 
                       from dba_tables 
                      where owner != 'SYS'
                      AND owner like '&p_owner'
                      );
prompt spool off
spool off
@truntblsxx.sql
prompt Check truntblsxx.sql log file for any errors