I would like to know the range between oldest partition and the newest partition on a table. can some body help me with this?
I have a query that gives me all the partition tables in the database ,now I would like to get the date of oldest partition and the newest partition to get the rentention period..any help would be appreciated..
here is what I have get all the partition tables
SELECT table_owner, table_name,max(partition_name)
FROM dba_tab_partitions p
WHERE p.table_name IN
(select distinct c.TABLE_NAME
from dba_part_key_columns p, dba_tab_cols c
where p.name = c.TABLE_NAME
and p.column_name = c.COLUMN_NAME
and p.object_type = 'TABLE'
and c.DATA_TYPE = 'DATE'
and p.owner NOT IN ('SYS', 'SYSTEM'))
and p.table_name not like 'BIN%'
group by table_owner, table_name
order by table_owner;
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.