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;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply