Purge Partitions

I have tried to find a good example for purging partitions in a table but was unable to come with a good example that could help me out. I am trying to create a drop procedure to purge partitions for weekly basis on a table and records have to be there from current date to past 3 years of weekly basis so basically 52*3+1 week = 157 week, but anything else more than that would be dropped for testing purposes we are doing past 3 year but eventaully going to turn down to 2 years. Any help would be grateful.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if your partitions have unique names you could write a script to drop/purge the partitions as shown in this link.  

Once the script is ready you can schedule this using DBMS JOB or CRON job.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
"if your partitions have unique names"?
What?  Oracle will make sure that partition names are unique!

You can query the "high_value" column of the dba_tab_partitions (or all_tab_partitions or user_tab_partitions) view to get the date limit for any table partition.

I use a combination of a custom PL\SQL package that I wrote plus a custom table that I created to hold control information for each partitioned table that I want my package to adjust automatically.  This combination allows me to automatically:
1. Rename table and index partitions to meaningful names that include the year and month (plus the week for tables that are partitioned by week) so DBAs and/or developers can know which time span any table partition correponds to.
2. Drop partitions that are no longer needed (based on data in my custom control table).

Would you like me to post the scripts for my custom table and PL\SQL procedure here?
@Mark -

I meant to say "meaningful names  that include the year and month/week/day" as you mentioned in your point 1.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

if you wanted one-time dynamic script

select 'Alter table ' ||table_owner||'.'||table_name || ' drop partition '|| partition_name ||';' stmt
from dba_tab_partitions
where 1 = 1
and table_name = 'YOUR TABLE NAME'
and table_owner = 'OWNER NAME'
and partition_name like 'P201301%' (for dropping all jan 2013 month partition(s))
Mark GeerlingsDatabase AdministratorCommented:
To sventhan:

Why is this included in your script: "1 = 1"?

Just for formatting reasons, nothing more specific. Its easy for me to alter the where conditions if it needed for testing.
Mark GeerlingsDatabase AdministratorCommented:
Yes, I understand that including that line (out of habit) can add a tiny amount of convenience during testing and debugging of code.  With that included, any/all other "where" clause lines can be commented out, and the SQL statement is still legal.  I hate seeing that line though in production code, and I've seen lots of production code where that line gets left in.  In production, this is just a performance penalty.  OK, the penalty may be small, but Oracle's query optimizer has to at least read this line, then decide it adds no value, and can be ignored.

Basically, I don't like seeing that line included in examples for others to learn from.
Got it. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.