patellion007
asked on
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you wanted one-time dynamic script
select 'Alter table ' ||table_owner||'.'||table_
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))
select 'Alter table ' ||table_owner||'.'||table_
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))
To sventhan:
Why is this included in your script: "1 = 1"?
Why is this included in your script: "1 = 1"?
@mark
Just for formatting reasons, nothing more specific. Its easy for me to alter the where conditions if it needed for testing.
Just for formatting reasons, nothing more specific. Its easy for me to alter the where conditions if it needed for testing.
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.
Basically, I don't like seeing that line included in examples for others to learn from.
Got it. Thanks!
I meant to say "meaningful names that include the year and month/week/day" as you mentioned in your point 1.