CODEWITHSUNDEEP
oracle-databaseddldatabase-partitioning
Ashish Shetkar
Ashish Shetkar

Reputation: 1467

automatic partition naming for range partitioning

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

Upvotes: 1

Views: 5306

Answers (3)

Ashish Shetkar
Ashish Shetkar

Reputation: 1467

Here the answergiven by Wernfried Domscheit - is correct

thank you for your Answer - it showed the way of controlling partition name - in my case i didn't need to care about partition name - i just needed to make sure - new partition is created with new name - so i used first half of your comment -

to verify it - i used it with DAY - partitioning

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' DAY)
(PARTITION p2021_dec VALUES LESS THAN (TIMESTAMP '2022-01-01 00:00:00'));

and then it created partitions like

partition name -    table name
P2021_DEC     -   DATA_PURGE_AUDIT
SYS_P8592     -     DATA_PURGE_AUDIT

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

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

Upvotes: 3

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65198

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;
/

Upvotes: 3

Related Questions