how to get names of partition in oracle while i input a date

Question

I have a table with many partitions range. I need to get the name of all partition when I give a date. For eg: if I input date 20/09/2014, it should list all partitions before that given date.

create or replace function get_part_name(p_date in date)
return varchar2 is
d date;
retp varchar2(30);
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
cursor c is
select high_value, partition_name p
  from user_tab_partitions
 where table_name='TEST';
begin
  for r in c loop
     str := r.high_value;
     execute immediate 'select '||str||' from dual' into d;     
     if p_date<d and d<mind then
        retp:=r.p;
        mind:=d;
     end if;
  end loop;
  return retp;
end;

This is returing a single date. I need all the dates, is it possible?

You return `varchar2` only. So only one partition name is returned.. You keep overwriting the same variable! You need to have a table function to return a table of names. — Maheswaran Ravisankar, Oct 16 '14 at 07:59

Lalit Kumar B · Accepted Answer · 2015-11-23T13:48:58.780

WITH DATA AS (
select table_name,
       partition_name,
       to_date (
          trim (
          '''' from regexp_substr (
                     extractvalue (
                       dbms_xmlgen.getxmltype (
                       'select high_value from all_tab_partitions where table_name='''
                                || table_name
                                || ''' and table_owner = '''
                                || table_owner
                                || ''' and partition_name = '''
                                || partition_name
                                || ''''),
                             '//text()'),
                          '''.*?''')),
          'syyyy-mm-dd hh24:mi:ss')
          high_value_in_date_format
  FROM all_tab_partitions
 WHERE table_name = 'SALES' AND table_owner = 'SH'
 )
 SELECT * FROM DATA
   WHERE high_value_in_date_format < SYSDATE
/

TABLE_NAME           PARTITION_NAME       HIGH_VALU
-------------------- -------------------- ---------
SALES                SALES_Q4_2003        01-JAN-04
SALES                SALES_Q4_2002        01-JAN-03
SALES                SALES_Q4_2001        01-JAN-02
SALES                SALES_Q4_2000        01-JAN-01
SALES                SALES_Q4_1999        01-JAN-00
SALES                SALES_Q4_1998        01-JAN-99
SALES                SALES_Q3_2003        01-OCT-03
SALES                SALES_Q3_2002        01-OCT-02
SALES                SALES_Q3_2001        01-OCT-01
SALES                SALES_Q3_2000        01-OCT-00
SALES                SALES_Q3_1999        01-OCT-99
SALES                SALES_Q3_1998        01-OCT-98
SALES                SALES_Q2_2003        01-JUL-03
SALES                SALES_Q2_2002        01-JUL-02
SALES                SALES_Q2_2001        01-JUL-01
SALES                SALES_Q2_2000        01-JUL-00
SALES                SALES_Q2_1999        01-JUL-99
SALES                SALES_Q2_1998        01-JUL-98
SALES                SALES_Q1_2003        01-APR-03
SALES                SALES_Q1_2002        01-APR-02
SALES                SALES_Q1_2001        01-APR-01
SALES                SALES_Q1_2000        01-APR-00
SALES                SALES_Q1_1999        01-APR-99
SALES                SALES_Q1_1998        01-APR-98
SALES                SALES_H2_1997        01-JAN-98
SALES                SALES_H1_1997        01-JUL-97
SALES                SALES_1996           01-JAN-97
SALES                SALES_1995           01-JAN-96

28 rows selected.

SQL>

Use your desired date in place of SYSDATE in above query. Or you can pass it as INPUT through the FUNCTION and RETURN the result set.

+1 Good One! Though it might take time to understand for novice! — Maheswaran Ravisankar, Oct 16 '14 at 08:07
I should add a one-liner, to put desired date in place of sysdate. Else, it would return all the high values. — Lalit Kumar B, Oct 16 '14 at 08:09
how can it be created as a function. i am returning some errors while executing the function. — Charles Peter, Oct 16 '14 at 08:57
Why do you need a function when it could be done with plain SQL. — Lalit Kumar B, Oct 16 '14 at 10:22

score 2 · Answer 2 · answered May 18 '17 at 15:55

Find Partition name using date IF you have a meaningful date column in the table in Oracle DB

WITH table_sample AS (select COLUMN_WITH_DATE from table SAMPLE (5))



SELECT uo.SUBOBJECT_NAME AS "PARTITION_NAME_1"
FROM table_sample sw,

  SYS.USER_OBJECTS uo 

WHERE sw.COLUMN_WITH_DATE = TRUNC(SYSDATE) -- ENTER DATE HERE AS 'DD-MM-YYYY 00:00:00'

AND OBJECT_ID              = dbms_rowid.rowid_object(sw.rowid)

AND ROWNUM                 < 2;

score 2 · Answer 3 · edited Oct 25 '18 at 00:46

I know this issue is old, but I ran across it looking for something and thought I'd weigh in to prevent others from going down the road above.The answers provided make it way more difficult than it has to be. You can use the dbms_rowid.rowid_object() to get the data object id of the row and join that with either user_objects, all_objects or dba_objects (whichever fits your needs).

Something like this should work ...

select distinct 
       o.subobject_name
  from user_objects o,
       my_table x
 where o.object_name = 'MY_TABLE'
   and dbms_rowid.rowid_object( x.rowid ) = o.data_object_id
   and trunc( x.stamp ) > ( current_timestamp - 31 );

I use this for code that has to determine partition names, for various reasons (queries, DML, etc...). I even used this very recently identify the partitions which have fallen outside of a defined window, as an auto-drop feature for an interval partitioned table.

(Old? It's old but I am here.) Interesting but it requires searching MY_TABLE for dates. I have a zillion records in each date partition. The idea of partitioning was to avoid searching by date. — user1683793, Nov 22 '19 at 21:44

Maheswaran Ravisankar · Answer 4 · 2014-10-16T08:14:31.587

Single SQL Solution: (high_value has to converted to date with correct format!)

SELECT
 partition_name p
  from user_tab_partitions
 where table_name='TEST'
 AND high_value < to_date('4444-01-01','yyyy-mm-dd') AND high_value > SYSDATE;

PL/SQL Solution:

Create a global type;

create type ty_partition_names is table of varchar2(30);
/

Function:

create or replace function get_part_name(p_date in date)
return ty_partition_names is
d date;
retp ty_partition_names := ty_partition_names();
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
idx number := 0;
cursor c is
select high_value, partition_name p
  from user_tab_partitions
 where table_name='test';
begin
  for r in c loop
     str := r.high_value;
     /*execute immediate 'select '||str||' from dual' into d;     */
     if p_date<str and str <mind then
        retp.extend(1);
        idx := idx + 1;
        retp(idx):=r.p;
        mind:=str;
     end if;
  end loop;
  return retp;
end;

And Finally,

SELECT * FROM TABLE(get_part_name(sysdate));

when i execute in cmd i get this error SQL> select get_part_name(sysdate) from dual; GET_PART_NAME(SYSDATE) -------------------------- TY_PARTTION_NAMES('PP_22102014') this is the result i get — Charles Peter, Oct 16 '14 at 08:09

how to get names of partition in oracle while i input a date

4 Answers4

Linked