CODEWITHSUNDEEP
oracle-databasepartition
Sankerea
Sankerea

Reputation: 37

Partition table rename automatically in ORACLE

I have a so many tables that come in every month. The partition name is SYS_P***. And I want to change it SYS_PYYYYMM automatically. I tried ALTER TABLE table_name RENAME PARTITION partition_name TO new_name;

This is for only 1 Partition name. How to change partition name always?

Upvotes: 1

Views: 1318

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

I use this procedure. It is more generic but fit to your needs:

PROCEDURE MaintainPartitions IS 

    EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
    PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);

    CURSOR PartTables IS
    SELECT TABLE_NAME, INTERVAL
    FROM USER_PART_TABLES 
    WHERE TABLE_NAME LIKE 'P_%'
        AND PARTITIONING_TYPE = 'RANGE' 
    ORDER BY TABLE_NAME;

    CURSOR TabParts(aTableName VARCHAR2) IS 
    SELECT PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = aTableName
    ORDER BY PARTITION_POSITION;

    ym INTERVAL YEAR TO MONTH;
    ds INTERVAL DAY TO SECOND;
    newPartName VARCHAR2(30);
    PERIOD TIMESTAMP;

BEGIN

    FOR aTab IN PartTables LOOP 
        BEGIN       
            EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ds;
            ym := NULL; 
        EXCEPTION 
            WHEN EXPRESSION_IS_OF_WRONG_TYPE THEN
                EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ym;
                ds := NULL;         
        END;            

        FOR aPart IN TabParts(aTab.TABLE_NAME) LOOP         
            EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT PERIOD;
            IF ds IS NOT NULL THEN
                IF ds >= INTERVAL '7' DAY THEN
                    -- Weekly partition
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"IYYY"W"IW';
                ELSE
                    -- Daily partition
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYYMMDD';
                END IF;
            ELSE
                IF ym = INTERVAL '3' MONTH THEN
                    -- Quarterly partition 
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYY"Q"Q';
                ELSE
                    -- Monthly partition
                    EXECUTE IMMEDIATE 'BEGIN :ret := TO_CHAR('||aPart.HIGH_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P_"YYYYMM';
                END IF;
            END IF;

            IF newPartName <> aPart.PARTITION_NAME THEN
                EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO '||newPartName;
            END IF;             
        END LOOP;
    END LOOP;

END MaintainPartitions;

Upvotes: 3

Related Questions