Truncate table with condition

[ridhirao22]

Hi all,

I am insert data with a select clause into a table and I need to truncate the table for last month data and re-insert instead of truncating the table completely.

truncate table MYtable where orddate >= addmonths(trunc(sysdate,'MM'),-1)

but I get the storage error:ORA-03291

How can I do this?

TIA,
RR

 

[LKBrwnDBA]

Did you try the DELETE statement?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ridhirao22]

Thank you for the quick reply!

Delete works...but just curious if we can Truncate with a where cause?
best way to do it...

 

[dkyrtata]

There is no WHERE clause in TRUNCATE. TRUNCATE is not a DML command like DELETE. Therefore a TRUCNCATE will COMMIT your database (including any changes made by other statements). Since no log of the changes are maintained, you cannot ROLLBACK from a TRUNCATE operation. This is what makes TRUNCATE a lot faster than DELETE.

 

[Dagon]

If you want to do this, your only option is to partition the table on orddate. You will then be able to truncate individual partitions rather than the whole table.

For Oracle-related work, contact me through Linked-In.