This content has been marked as final. Show 2 replies
What is your 4 digit Oracle version?
Provide an example of a table and what you consider to be the 'oldest' and 'newest' partitions. Are you talking about tables that are RANGE partitioned? INTERVAL partitioned? What about subpartitions?
Why does your query use MAX(partition_name)? How is that information useful - partitions might have any names including system generated ones.
And why would the oldest and newest give you retention period?
For simple RANGE partitioned tables the range info is in the HIGH_VALUE column (a LONG - so need to use PL/SQL) of DBA_TAB_PARTITIONS.
Suggest you first work out a step-by-step manual process for doing what you want to do and then work on automating it.
Post an example of your manual process.