Truncate partitions in Oracle

How to truncate partitions in Oracle

Check the partitions table in Oracle

col table_owner for a30
col PARTITION_NAME for a30
select table_owner,table_name,partition_name,read_only from dba_tab_partitions;

Truncate the present partition in Oracle

SYNTAX:
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> TRUNCATE PARTITION <
PARTITION_NAME> <UPDATE GLOBAL INDEXES(optional)>;

Example:
ALTER TABLE SCOTT.TRAN TRUNCATE PARTITION TRAN_202101 UPDATE GLOBAL INDEXES;

--- In oracle 12c, we can truncate multiple partitions in one command
ALTER TABLE SCOTT.TRAN TRUNCATE PARTITIONS tran_202101, tran_202102, tran_202103 UPDATE GLOBAL INDEXES;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.