How to Find Interval Partition Name by Date Column Value

Since  HIGH_VALUE  in DBA_TAB_PARTITION view is LONG type, so it is difficult to get the system generated interval partition name straight away. Here is a small function to achieve this purpose.

SQL> desc dba_tab_partitions
Name              Null?    Type
----------------- -------- ------------
TABLE_OWNER                VARCHAR2(30)
TABLE_NAME                 VARCHAR2(30)
COMPOSITE                  VARCHAR2(3)
PARTITION_NAME             VARCHAR2(30)
SUBPARTITION_COUNT         NUMBER
HIGH_VALUE                 LONG
...
.

1)first, create a function.

set serveroutput on
CREATE OR REPLACE FUNCTION GET_INTERVAL_PARTITION_NAME(
tableowner in VARCHAR2, tablename VARCHAR2, partitiondate VARCHAR2 )
return VARCHAR2
IS
  dt date;
begin
for x in (select table_name,partition_name, high_value 
          from dba_tab_partitions 
          where INTERVAL='YES' and TABLE_OWNER=tableowner 
          and TABLE_NAME=tablename )
loop
execute immediate 'select '||x.high_value||' from dual' into dt;
        if to_char(dt - 1/24,'YYYYMMDD') = partitiondate 
        then
          --dbms_output.put_line(' partition: '||x.table_name||'.'||
                               x.partition_name||' '||x.high_value);
          return x.partition_name;
       end if;
end loop;
end;

2) Get the interval partition name in SQLPLUS by calling the previous function.

SQL> var pat_name varchar2(30);
SQL> exec :pat_name:=GET_INTERVAL_PARTITION_NAME('TESTUSER','CDR_TBL','20181225');

PL/SQL procedure successfully completed.

SQL> print :pat_name;

PAT_NAME
--------------------------------------
SYS_P122658

3) Get the partition name in shell environment.

$ export PARTITIONNAME=`sqlplus -s / as sysdba <<EOF
set head off feedback off serverout off
var pat_name varchar2(30);
exec :pat_name:=GET_INTERVAL_PARTITION_NAME('TESTUSER','CDR_TBL','20181225');
print pat_name
exit
EOF`

$ echo $PARTITIONNAME
SYS_P122658

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.