Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

oracle identify and generate script to drop old partitions

khallas301May 5 2015 — edited May 5 2015

Hello DBAs and Gurus,

DB: 11gR2

OS: Linux

I am new to Oracle partition world with limited experience in partition management.

I am trying to drop some old partitions from users schema and not sure from where to start and safest way.

Task is to identify and drop all old partition of a schema older than March 2015.

1. Can somebody share with me correct way to find all old partitions of a schema?

2. How to write dynamic script to drop old partitions?  I think I need to do something like dynamic select query to drop partitions, spool alter table drop partition command and run sql script?

3. If table has got foreign key then is it possible to drop partition without disabling FK?

Is below query correct to identify old partitions?

SELECT table_name, partition_name

FROM dba_tab_partitions

WHERE table_owner = 'USER1'

AND partition_name <= 'PART_2015_03'

ORDER BY 1;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 2 2015
Added on May 5 2015
9 comments
1,764 views