CODEWITHSUNDEEP
oracle-databaseoracle11g
Sushma
Sushma

Reputation: 121

Table partition is range or interval based

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?

After using the below code for the partition deletion i am getting interval partitioned table as range partitioned.

SQL> 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>1 indicates table having only one partition is not getting deleted 15 if i > var and occurance > 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 /

Procedure created.

Please help me with it..

Upvotes: 2

Views: 7304

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

To find if a table is interval partitioned, whether or not any partitions based on the interval have been created, you can check (DBA)(ALL)(USER)_PART_TABLES.INTERVAL. If it's not NULL this column contains the partitioning interval specified when the table was created or altered to be interval partitioned. If NULL it means that the table isn't interval partitioned.

Upvotes: 2

wolφi
wolφi

Reputation: 8361

For interval partitions, the column INTERVAL in the data dictionary view USER_TAB_PARTITIONS is set to YES:

SELECT table_name, partition_name, partition_position, INTERVAL, segment_created 
  FROM user_tab_partitions;

  TABLE_NAME  PARTITION_NAME PARTITION_POSITION INTERVAL SEGMENT_CREATED
  MYTABLE     PRE2014        1                  NO       NO
  MYTABLE     SYS_P385       2                  YES      YES
  MYTABLE     SYS_P401       3                  YES      YES
  MYTABLE     SYS_P441       4                  YES      YES

Upvotes: 1

Related Questions