Sams Teach Yourself SQL in 21 Days, Fourth Edition

Truncating All Tables in a Schema

Truncating tables is an event that occurs in a development environment. To effectively develop and test data load routines and SQL statement performance, data is reloaded frequently. This process identifies and exterminates bugs, and the application being developed or tested is moved into a production environment.

The following example truncates all tables in a specified schema:

SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL TRUNC.SQL
SQL> SELECT 'TRUNCATE TABLE ' || TABLE_NAME || ';'
  2  FROM ALL_TABLES
  3  WHERE OWNER = 'RYAN'
  4  / TRUNCATE TABLE ACCT_PAY; TRUNCATE TABLE ACCT_REC; TRUNCATE TABLE CUSTOMERS; TRUNCATE TABLE EMPLOYEES; TRUNCATE TABLE HISTORY; TRUNCATE TABLE INVOICES; TRUNCATE ...