Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TO find MAX Partition of table

Posted on 2011-02-17
8
Medium Priority
?
1,764 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

597 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