You could either create rolling partitions based directly on the transaction date and drop/create new partitions, or create a new (redundant) column to store to_char(transaction_date,'W'), using that as the partitioning key, and just truncate and reuse partitions.
dropping and recreating the partition every day is not a good idea since it will be time consuming, and requires DBA intervention.
I just wanted to truncate the partition, and have the transactions of the new day to be inserted in this partition.
also adding a new column in the table is not possible, since we don't have the source code of the application, so we can't add this column in the insert statement, and we can't add a trigger in the table to populate this column since it has bulk inserts on it, (4-5 millions per day).
Originally posted by amir_magdy dropping and recreating the partition every day is not a good idea since it will be time consuming, and requires DBA intervention.
Well, no it doesn't actually. You can have a scheduled job set up through DBMS_JOB to create new partitions and drop old ones automatically - any worthwhile DBA could do that, it ain't rocket science and the operation would not be time-consuming to run either.
You can also hedge your bets a little by creating the partitions a week in advance.