?
Solved

TO find MAX Partition of table

Posted on 2011-02-17
8
Medium Priority
?
1,752 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 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