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;