title: Table partition is range or interval based
body: <p>I have to do the data base partition testing, i created the interval partition to the table now if i move the clock for testing, the new partitions are not getting created? Still it is showing the old partitions. Any idea how to resolve this? How to check whether table is range partitioned or interval partitioned in oracle database?</p>
  
  <p>After using the below code for the partition deletion i am getting interval partitioned table as range partitioned.  </p>
  
  <p><code>SQL&gt; create or replace procedure  partition_delete(var in int) AS
        2       v Date := SYSDATE;
        3       i number;
        4       occurance  number;
        5       l_drop_sql varchar2(2000);
        6  BEGIN
        7  execute immediate ' alter table "sch1"."AUDITS" set interval ()';
        8  execute immediate ' alter table "sch1"."ALERTAUDITS" set interval ()';
        9  -- select the tables that starts with either A or L having cfcc as table owner
        10  for curs in ( select table_owner,table_name,partition_name,high_value from          dba_tab_partitions where table_owner='owner' and REGEXP_LIKE (table_name,'^(A|L)')) LOOP
       11       EXECUTE IMMEDIATE 'SELECT ' || curs.high_Value || ' FROM dual' INTO v; --   Conversion of high_value from long to date
      12       select round(SYSDATE - v) into i from dual; -- finding the difference between    current date and partition creation date
      13       select count(*) into occurance  from dba_tab_partitions where     table_name=curs.table_name;  -- Finding the no of existing partitions to the particular    table
      14       -- occurance&gt;1 indicates table having only one partition is not getting deleted
      15         if i &gt; var  and occurance &gt; 1  then
      16             dbms_output.put_line('The no of days are '||i);
      17             dbms_output.put_line('The table name and partition name are    '||curs.table_name||'and'||curs.partition_name);
      18             l_drop_sql  :='alter table "'||curs.table_owner||'"."'||curs.table_name||'" drop partition '||curs.partition_name||' update global indexes';
      19             dbms_output.put_line(l_drop_sql);
      20             execute immediate l_drop_sql;
      21        end if;
      22  end LOOP;
  23  execute immediate 'alter table "sch1"."AUDITS" set interval (NUMTOYMINTERVAL(1,''month''))';
  24  execute immediate 'alter table "CFCC"."ALERTAUDITS" set interval (NUMTOYMINTERVAL(1,''month''))';
  25  end;
  26  /</code></p>
  
  <p>Procedure created.</p>
  
  <p>Please help me with it..</p>