Oracle rename partition if exists

Question

I need to create a statement that can be run to rename a partition only if specific partition name exists and if not then continue on to execute other code.

basic command  = ALTER TABLE TEST RENAME PARTITION P1 TO P2:

I have looked at the following but have not come up with a solution

Why so you like to rename a partition? – Wernfried Domscheit Mar 20 '18 at 13:08 — Wernfried Domscheit, Mar 20 '18 at 13:08

score 2 · Answer 1 · answered Mar 20 '18 at 13:05

I depends on your requirements but a basic procedure would be this one:

DECLARE
    PARTITION_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(PARTITION_DOES_NOT_EXIST, -2149); 
BEGIN

    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE TEST RENAME PARTITION P1 TO P2';
    EXCEPTION 
        WHEN PARTITION_DOES_NOT_EXIST THEN NULL;
    END;

    ... ohter commands
END;

Robert Kock · Accepted Answer · 2018-03-20T13:56:29.020

2

You could check whether the partition exists within the table USER_TAB_PARTITIONS:

DECLARE
  v_p1_exists AS NUMBER;
  v_p2_exists AS NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_p1_exists
    FROM user_tab_partitions
   WHERE table_name = 'TEST'
     AND partition_name = 'P1';
  SELECT COUNT(*)
    INTO v_p2_exists
    FROM user_tab_partitions
   WHERE table_name = 'TEST'
     AND partition_name = 'P2';
   IF (v_p1_exists <> 0) AND (v_p2_exists = 0) THEN
     EXECUTE IMMEDIATE 'ALTER TABLE TEST RENAME PARTITION P1 TO P2';
   END;
END;

edited Mar 20 '18 at 13:56

answered Mar 20 '18 at 13:09

Robert Kock

5,795
1
12
20

1

Just a note: (Like mine) this procedure will raise an exception if a partition `P2` already exist. – Wernfried Domscheit Mar 20 '18 at 13:52
Updated the answer – Robert Kock Mar 20 '18 at 13:56

score 0 · Answer 3 · answered Mar 20 '18 at 13:03

One option is to enclose ALTER TABLE into its own BEGIN-END block, with appropriate exception handling section. Mine is kind of stupid (WHEN OTHERS, eh?); feel free to rewrite it to be meaningful - it is just to show how to do it.

So: if ALTER TABLE fails, it'll raise some error; it'll be captured, handled, and code will continue its execution.

begin
  begin
    execute immediate 'alter table test rename partition p1 to p2';
  exception
    when others then 
      -- ignore errors
      null;
  end;

  -- continue to execute other code
  ...
end;

Oracle rename partition if exists

3 Answers3