Home » SQL & PL/SQL » SQL & PL/SQL » Condition based truncate partition (Oracle)
|
|
Re: Condition based truncate partition [message #689425 is a reply to message #689423] |
Fri, 22 December 2023 09:58   |
Solomon Yakobson
Messages: 3305 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.
|
|
|
|
Goto Forum:
Current Time: Sat May 03 03:15:22 CDT 2025
|