CODEWITHSUNDEEP
sqlplsqloracle11g
Thiyagu ATR
Thiyagu ATR

Reputation: 2264

how to drop manually a partition identified by its boundary?

I've a table which is partion by month now i need to drop partition which is older than 2 months using plsql procedure under oracle 11g.I have tried alter table <table_name> drop partition <partition name>this 'll drop by name of that partition.but instead i need to drop by month.my actual table is.

CREATE TABLE invoices_1
(in_no    NUMBER NOT NULL,
 voice_date  DATE   NOT NULL,
 feed_back     VARCHAR2(500))
PARTITION BY RANGE (voice_date)
(PARTITION jan VALUES LESS THAN (TO_DATE('01/12/2012', 'DD/MM/YYYY')) ,
 PARTITION feb VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) ,
 PARTITION decs VALUES LESS THAN (TO_DATE('01/02/2013', 'DD/MM/YYYY')) );

Upvotes: 1

Views: 181

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You can drop a partition by identifying it with its boundary with the keyword FOR:

SQL> ALTER TABLE invoices_1 DROP PARTITION
  2                          FOR (TO_DATE('2012-12-01', 'YYYY-MM-DD'));

Table altered

Upvotes: 2

Related Questions