?
Solved

TO find MAX Partition of table

Posted on 2011-02-17
8
Medium Priority
?
1,799 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 668 total points
ID: 34926032
See attached.
query.txt
0
 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 664 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 668 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

599 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