Hi Tom,

We need to drop old partitions in regular basis for some tables partitioned by interval.

To keep 60 days data for the TEST table partitioned by day interval, for instance, I could get partitions name older than two months by using the following SQL and drop them later:

with max_partition as ( select table_owner, table_name, max(partition_position) max_p from dba_tab_partitions where table_name = 'TEST' and table_owner=‘TEST' group by table_owner, table_name )
select t1.table_owner, t1.table_name, t1.partition_name 
       from dba_tab_partitions t1,
            max_partition t2
       where t1.table_name = 'TEST' 
       and t1.table_owner =‘TEST'
       and t1.table_name = t2.table_name
       and t1.table_owner = t2.table_owner
       and t1.partition_position <= (t2.max_p - 60);
   

--- output:
 
TEST    TEST     SYS_P59342
TEST    TEST     SYS_P59403
TEST    TEST     SYS_P59543
TEST    TEST     SYS_P59684
TEST    TEST     SYS_P59784
TEST    TEST     SYS_P59883
TEST    TEST     SYS_P60002
 
 
In order to perform the same task for several tables, I created a table with number of partition that should be keep for each table partitioned by interval.

 

CREATE TABLE TB_PART_RETENTION (
    OWN_NAME VARCHAR2(30),
    TAB_NAME VARCHAR2(30),
    NUM_RETAIN_PARTITION NUMBER);

 
 

Insert into tb_part_retention values (‘TEST’,’TEST’,60);

When I try to compile the following procedure, using the similar "with clause" statement, it fails. The error message says: "SQL Statement ignored" on the line of "With clause" statement.

Is “With clause” not allowed within a cursor?

 

CREATE OR REPLACE 
PROCEDURE proc_drop_partition (p_schema IN VARCHAR2, p_table IN VARCHAR2, 
p_num_retention IN NUMBER) AS

CURSOR c_expired_partition IS
WITH max_partition as 
( SELECT table_owner, table_name, max(partition_position) max_p FROM dba_tab_partitions WHERE table_owner=p_schema AND table_name = p_table GROUP BY table_owner, table_name)
SELECT t1.table_owner, t1.table_name, t1.partition_name
       FROM dba_tab_partitions t1,
            max_partition t2          
       WHERE t1.table_name = p_table AND t1.table_owner=p_schema
       AND t1.table_name = t2.table_name
       AND t1.table_owner = t2.table_owner
       AND t1.partition_position <= (t2.max_p - p_num_retention);                          
 
BEGIN
     FOR i_c_expired_partition in c_expired_partition
     LOOP
         exec_command ('ALTER TABLE'||c_expired_partition.table_owner||'.'||c_expired_partition.table_name||' DROP PARTITION '|| c_expired_partition.partition_name || ' UPDATE INDEXES');
     END LOOP;

END proc_drop_partition;

   
show erros
--
 
Procedure PROC_DROP_PARTITION compilado
 
Errors: check compiler log
Erros para PROCEDURE PROC_DROP_PARTITION:
LINE/COL ERROR
-------- --------------------------------------------------------------------------------------------------
3/5      PL/SQL: SQL Statement ignored
11/9     PL/SQL: ORA-00904: "T1"."PARTITION_NAME": identificador inválido
16/5     PL/SQL: Statement ignored
16/55    PLS-00225: a referência ao subprograma ou ao cursor 'C_EXPIRED_PARTITION' está fora da abrangência
 

Thanks in advance

declare
  cursor cur is 
    with rws as (
      select level x from dual
      connect by level <= 10
    )
      select * from rws;
      
begin

  for c in cur loop
    dbms_output.put_line ( c.x );
  end loop;

end;
/

1
2
3
4
5
6
7
8
9
10