[askdba] Re: Partition Tables

  • From: Abraham Kurian <kurian24@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Fri, 3 Dec 2004 08:56:17 +0530

hi Justin,
   Your assumption is right , I am talking about range-partitioned tables.
i wanted a query which looked something like this

select partition_name from dba_tab_partitions
where <somevalue>  = to_char(sysdate,'MMYYYY');

I managed to achive this by using PL/SQL ... used High_value column of
 dba_tab_partitions

Query 1 :
select * from (select table_name , partition_name , partition_position
, li_high_value
        from dba_tab_partitions
        )
        where li_high_value=to_char(sysdate,'MMYYYY');

where 
li_hign_value := (rpad(nvl(rtrim(ltrim(substr(replace(x.high_value,
'TO_DATE(''', ''), 1, 11))), ' '), 10))

Regards,
Kurian

On Thu, 2 Dec 2004 09:17:57 -0700, Justin Cave (DDBC) <jcave@xxxxxxxxxxx> wrote:
> First, I assume we are talking about range-partitioned tables here, not
> hash or list partitioned tables...
> 
> Do you have a standard naming convention for partitions in a range
> partitioned table?  If so, you could get the partition name from
> dba_tab_partitions by looking for the right partition_name.
> 
> In 9i, you can use the dbms_metadata package to extract the DDL for an
> object and parse that DDL for the current partition.
> 
> If you do an explain plan on a query that would access current month
> data, you could probably also extract the partition name from the
> plan_table.
> 
> Justin Cave  <jcave@xxxxxxxxxxx>
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
> 
> 
> 
> -----Original Message-----
> From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
> On Behalf Of Abraham Kurian
> Sent: Thursday, December 02, 2004 4:25 AM
> To: askdba@xxxxxxxxxxxxx
> Subject: [askdba] Partition Tables
> 
> Hi ,
> 
>            I have lots of partitioned tables in my schema , the
> requirement is to identify the current partition of the table.
> 
> Let me explain this with an eg.
> 
> Lets say I have a table T , with 24 partitions .. starting for Jan -04
> to Dec-05. Now , my current partition is Dec-04.
> 
> How can I achieve this by querying the data dictionary ?
> 
> Regards
> Kurian
> 
>

Other related posts: