Wednesday, June 17, 2009

gen_truncate_tables.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Truncate all tables *****
PROMPT

SPOOL truncate_tables.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL truncate_tables.log' FROM dual;
SELECT '' FROM dual;

SELECT 'TRUNCATE TABLE '||owner||'.'||table_name||';'
FROM dba_tables
WHERE owner='&1'
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT *******************************
PROMPT
PROMPT Output saved at truncate_tables.sql
PROMPT