user_truncate_objs.sql

-- truncate_user.sql
--
-- Andy Barry
-- 26/07/05
--
set trimspool on wrap off
set heading off feedback off
set verify off
set pages 1000 lines 1000

select '&&username' from dual;

spool /tmp/truncate_&username..sql

select 'alter table ' ||  owner || '.' || table_name || ' drop primary key cascade;'
from dba_constraints
where owner = trim(upper('&username'))
and constraint_type = 'P'
/

select 'alter table ' ||  owner || '.' || table_name || ' drop constraint ' || constraint_name || ' cascade;'
from dba_constraints
where owner = trim(upper('&username'))
and constraint_type = 'U'
/

select 'truncate table ' || owner || '.' || table_name || ' reuse storage;'
from dba_tables
where owner = trim(upper('&username'))
/

select 'drop sequence ' || sequence_owner || '.' || sequence_name || ';'
from dba_sequences
where sequence_owner = trim(upper('&username'))
/

spool off

set heading on feedback on
@/tmp/truncate_&username