2 Replies Latest reply on May 16, 2012 7:51 PM by 922659

    oracle Partition maintenance range information required

      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;
        • 1. Re: oracle Partition maintenance range information required
          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.