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?
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.