Solved

TO find MAX Partition of table

Posted on 2011-02-17
8
1,724 Views
Last Modified: 2012-06-27
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...
0
Comment
Question by:pal2k
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34917962
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
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34918544
select * from user_tab_partitions
where table_name = 'xyz'
order by high_value

--> the high_value will indicate the partition with the highest value
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34918630
http:#34918544  won't work.


high_value is a LONG column,  you can't order by that
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Expert Comment

by:sdstuber
ID: 34918640
you'll get a syntax error if you even try it  (ORA-00997)
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34918859
How about Partition_position column?

select max(partition_position) from user_tab_partitions where table_name = YourTabName
0
 
LVL 32

Accepted Solution

by:
awking00 earned 167 total points
ID: 34926032
See attached.
query.txt
0
 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 166 total points
ID: 34942798
Hi,

You can use the below pl/sql block the get the result you need.



Thanks..
declare
type tt is ref cursor;
kk   tt;
stmt 	  varchar2(500);
dt_alone  date;
mm 	  number;
part_name varchar2(30);
begin
      select max(partition_name) into part_name from user_tab_partitions where table_name = 'TEST_PART';
       loop
       open  kk for 'select count(*) from test_part partition ( ' || part_name || ' ) ';
       fetch kk into mm;
       close kk;
       exit when mm > 0;
         if mm = 0 then
           dt_alone  := to_date(substr(part_name,3,8),'YYYYMMDD');
           dt_alone  := dt_alone - 1;
           part_name := 'P_' || to_char(dt_alone,'YYYYMMDD');
           end if;
     end loop;
       dbms_output.put_line('Max Part Name ' || part_name);
       dbms_output.put_line('count ' || mm);
end;
/

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 167 total points
ID: 34943110
if you must iterate though all partitions then I suggest NOT using count and simply check for existence

something like this...

select null from your_table partition (some_partition) where rownum = 1

if that returns a row then your partition has data,  if it generates a no_data_found then your partition does not have data.

or, if you don't want to catch an exception then

select count(*) from your_table partition (some_partition) where rownum = 1

this will only count 1 row which is sufficient to prove existence of data.

If your partitions are large, this can be significant time savings.



0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question