Home » SQL & PL/SQL » SQL & PL/SQL » Condition based truncate partition (Oracle)
Condition based truncate partition [message #689423] Fri, 22 December 2023 04:03 Go to next message
ace_friends22
Messages: 9
Registered: December 2023
Junior Member
Hi team,

How can we write SQL statement to truncate partition based on condition.

For example, I have a table, partitioned based on 2 columns. BRAND & YEAR.

If today is 1st JUNE or greater then truncate current year partition and given BRAND
If today is less than 1st JUNE, then truncate current year and previous year partition and Given BRAND
Re: Condition based truncate partition [message #689424 is a reply to message #689423] Fri, 22 December 2023 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do something like:
begin
  for part in (
    select partition_name from user_tab_partitions 
    where table_name = '<your table>'
      and <your other conditions>
  )
  loop
    execute immediate 'alter table <your table> truncate partition '||part.partition_name;
    dbms_output.put_line ('Partition '||part.partition_name||' truncated');
  end loop;
end;
/
Re: Condition based truncate partition [message #689425 is a reply to message #689423] Fri, 22 December 2023 09:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
You didn't tell if it is range/list and regular or interval/automatic but you'll have to use dynami SQL regardless. For example:

SQL> DROP TABLE TBL PURGE
  2  /

Table dropped.

SQL> CREATE TABLE TBL(
  2                   BRAND VARCHAR2(10),
  3                   YEAR  NUMBER
  4                  )
  5    PARTITION BY LIST(BRAND,YEAR)
  6    (
  7     PARTITION BRAND1_2022 VALUES(('BRAND1',2022)),
  8     PARTITION BRAND2_2022 VALUES(('BRAND2',2022)),
  9     PARTITION BRAND1_2023 VALUES(('BRAND1',2023)),
 10     PARTITION BRAND2_2023 VALUES(('BRAND2',2023))
 11    )
 12  /

Table created.

SQL> INSERT INTO TBL VALUES('BRAND1',2022)
  2  /

1 row created.

SQL> INSERT INTO TBL VALUES('BRAND2',2022)
  2  /

1 row created.

SQL> INSERT INTO TBL VALUES('BRAND1',2023)
  2  /

1 row created.

SQL> INSERT INTO TBL VALUES('BRAND2',2023)
  2  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT * FROM TBL PARTITION FOR('BRAND1',2023)
  2  /

BRAND            YEAR
---------- ----------
BRAND1           2023

SQL> EXEC EXECUTE IMMEDIATE 'ALTER TABLE TBL TRUNCATE PARTITION FOR(''BRAND1'',' || TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-5),'YYYY')) || ')'

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TBL PARTITION FOR('BRAND1',2023)
  2  /

no rows selected

SQL>
SY.
Re: Condition based truncate partition [message #689801 is a reply to message #689424] Wed, 24 April 2024 10:09 Go to previous message
ace_friends22
Messages: 9
Registered: December 2023
Junior Member
@Michel Cadot: It worked.
Previous Topic: Convert input from user into UPPERCASE
Next Topic: Missing records
Goto Forum:
  


Current Time: Sat Apr 27 15:43:39 CDT 2024