Generate Truncate Table Statements (truncate.gen)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This generator script creates TRUNCATE TABLE statements for tables matching the specified criteria. TRUNCATE is a DDL operation that removes all rows from a table quickly and cannot be rolled back.

The Script

rem  truncate.gen
rem
select 'truncate table ' || owner || '.' || table_name || ';'
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table')
 order by owner, table_name;

Usage

-- Basic usage
@truncate.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table: Table name pattern (use % for wildcard)

Parameters

The script prompts for:

• &owner - Schema owner pattern (use % for wildcard)
• &table - Table name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLES

Sample Output

truncate table HR.EMPLOYEES;
truncate table HR.EMPLOYEES_TEMP;
truncate table HR.JOBS_HISTORY;

Key Considerations

• TRUNCATE vs DELETE: TRUNCATE is faster but cannot be rolled back
• Foreign Keys: Tables with referential constraints may fail to truncate
• Triggers: TRUNCATE does not fire DELETE triggers
• High Water Mark: TRUNCATE resets the table’s high water mark

Common Use Cases

Truncate All Tables in Schema

@truncate.gen
-- Enter: SCOTT for owner, % for table

Truncate Specific Table Pattern

@truncate.gen
-- Enter: % for owner, %_TEMP for table

Related Scripts

• Drop Table Generator - Generate DROP TABLE statements
• Disable Constraints Generator - Disable constraints before truncating