automatic partition naming for range partitioning-CodePudding

I have a table like this

CREATE TABLE DATA_AUDIT (
    id_col NUMBER(*,0) NOT NULL ENABLE,
    col_2 VARCHAR2(10) NOT NULL ENABLE,
    col_3 NUMBER(*,0) NOT NULL ENABLE,
    col_4 VARCHAR2(10) NOT NULL ENABLE,
    col_5 VARCHAR2(10) NOT NULL ENABLE,
    CREATED_AT TIMESTAMP (3) default current_timestamp ,
    CONSTRAINT DATA_AUDIT_PK PRIMARY KEY (col_2,col_3,col_4)
 USING INDEX  ENABLE
)
 partition by range (CREATED_AT)
(
   partition p2022_jan
   values less than (to_date('01-jan-2022')),
   partition p2022_feb
   values less than (to_date('01-feb-2022')),
   partition p2022_mar
   values less than (to_date('01-mar-2022'))
)

Here - in above example - i need to explicitly mention the name in DDL

I want to create new partition automatically for every month data How can i achieve it naming it automatically - i am ok with any random name of partition

I am using oracle - Oracle Database 19c Enterprise Edition Release 19.0.0.0

CodePudding user response：

Assume that the table only has the first partition(p2022_jan) and you'll add new partitions to the table every month, then firstly create a stored procedure such as

CREATE OR REPLACE PROCEDURE Pr_Add_Part_to_Data_Audit is
  v_ddl       VARCHAR2(32767);
  v_date      DATE := TO_DATE(TO_CHAR(sysdate,'yyyy-mm-')||'01','yyyy-mm-dd');  
BEGIN 
  v_ddl :='ALTER TABLE data_audit ADD PARTITION p'||TO_CHAR(v_date,'yyyy')||'_'||TO_CHAR(v_date,'mon')||' VALUES LESS THAN ('''||v_date||''')';
  EXECUTE IMMEDIATE v_ddl;
END;
/

then, call that from a scheduler at the beginning of the upcoming months such as

DECLARE
  v_job_name VARCHAR2(32) := 'jb_add_part_data';
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(job_name        => v_job_name,
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'Pr_Add_Part_to_Data_Audit',
                            start_date      => TO_DATE('01-02-2021 01:00:10',
                                                       'DD-MM-YYYY HH24:MI:SS'),
                            repeat_interval => 'FREQ=MONTHLY; BYHOUR=1;',
                            auto_drop       => false,
                            comments        => 'Adds a new partition every month');

  DBMS_SCHEDULER.ENABLE(v_job_name);
END;
/

CodePudding user response：

Instead of a RANGE partition I would suggest an INTERVAL partition:

CREATE TABLE DATA_AUDIT (
    id_col NUMBER(*,0) NOT NULL ENABLE,
    col_2 VARCHAR2(10) NOT NULL ENABLE,
    col_3 NUMBER(*,0) NOT NULL ENABLE,
    col_4 VARCHAR2(10) NOT NULL ENABLE,
    col_5 VARCHAR2(10) NOT NULL ENABLE,
    CREATED_AT TIMESTAMP (3) default current_timestamp ,
    CONSTRAINT DATA_AUDIT_PK PRIMARY KEY (col_2,col_3,col_4) USING INDEX  ENABLE
)
 partition by range (CREATED_AT) INTERVAL (INTERVAL '1' MONTH)
(PARTITION p2021_dec VALUES LESS THAN (TIMESTAMP '2022-01-01 00:00:00'));

Then Oracle will create new partition automatically every months.

For renaming you can runs this procedure by daily scheduler job as proposed by Barbaros Özhan.

PROCEDURE RenamePartitions IS

    ts TIMESTAMP;
    newName VARCHAR2(30);

    CURSOR TabPartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME = 'DATA_AUDIT'
    ORDER BY 1,2;

BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 180';

    FOR aPart IN TabPartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        ts := ADD_MONTHS(ts, -1);
        newName := 'p'||TO_CHAR(ts,'yyyy_mon', 'NLS_DATE_LANGUAGE = american');
        IF aPart.PARTITION_NAME <> newName THEN             
            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO '||newName;
        END IF;
    END LOOP;

END RenamePartitions;

Or see a more generic one: Partition table rename automatically in ORACLE