Start Free Trial

asked on

TO find MAX Partition of table

Hi,

I would appreciate if I can get help in finding max partition of table which has date.

Ex: table name xyz partitioned by date from dec 1 to dec 30 like below
P_20101201, .....until.....P_20101230. Lets consider that only until the P_20101220 has data and other partitions starting from P_20101221 is empy. no data. So what I need here is MAX partition which has data. In the above example that would be P_20101220.

I need a sql, pl/sql blocl or proc....anything would be fine...

make sure your statistics are up to date then try this...

I'm assuming all of your partition names follow the format you have specified
if not, the ordering won't work

ee.txt

select * from user_tab_partitions
where table_name = 'xyz'
order by high_value

--> the high_value will indicate the partition with the highest value

http:#34918544 won't work.

high_value is a LONG column, you can't order by that

you'll get a syntax error if you even try it (ORA-00997)

How about Partition_position column?

select max(partition_position) from user_tab_partitions where table_name = YourTabName

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial