Identifying table partition name

I am trying to write a generic procedure, based on dbms_stats.gather_table_stats, to gather fresh table stats for a single partition of any (date) range partioned table. I plan to run the procedure immediately after an overnight batch insert of new day's data into various partitioned tables. My problem is that dbms_stats needs to be supplied with the partition name if I want to limit the stats refresh to just one partition.

From the date value for my new day's data is there a simple way of identifying the name of the table partition that would hold it? (RDBMS is Oracle 8.1.7.0 Enterprise Edition on HPUX).

I've searched the documentation for the built in PL/SQL packages but haven't found anything that does this. I've also looked at the USER_TAB_PARTITIONS view, and although the information I need could be obtained by scanning through the approriate rows in order, and evaluating the expression in the high_values column, it doesn't look simple. I'm guessing the query optimiser has a better way of doing this!!

Any suggestions or pointers to an easier solution would be very much appreciated.

Paul Dixon Received on Mon May 19 2003 - 11:31:48 CDT