Dropping and renaming table partitions

T

The space on free OCI can sometimes be a bit limiting, especially if you're using table partitions. Even an empty partition consumes 8 MB, and if you have partitions per tenant in every table — and over 100 tables — it can quickly grow with each new tenant.

So here is the script which helps you to remove empty partitions and also rename the partitions so you can easily identify specific tenants:

DECLARE
    in_table_like           CONSTANT VARCHAR2(128) := '%';
    in_partition_prefix     CONSTANT VARCHAR2(128) := '__P';
    --
    v_found             PLS_INTEGER;
    v_high_value        VARCHAR2(4000);
    v_new_name          VARCHAR2(128);
BEGIN
    FOR c IN (
        SELECT
            t.table_name,
            t.partition_name,
            t.high_value
        FROM user_tab_partitions t
        WHERE t.table_name LIKE in_table_like
        ORDER BY 1, 2
    ) LOOP
        v_found         := NULL;
        v_high_value    := c.high_value;    -- LONG conversion
        --
        IF v_high_value IN ('DEFAULT', 'MAXVALUE') THEN
            CONTINUE;
        END IF;

        -- calculate number of rows in partition
        EXECUTE IMMEDIATE
            'SELECT MAX(1) FROM ' || c.table_name ||
            ' PARTITION (' || c.partition_name || ') WHERE ROWNUM = 1'
            INTO v_found;

        -- delete empty partition
        IF v_found IS NULL THEN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' DROP PARTITION ' || c.partition_name || ' UPDATE INDEXES';
            --
            CONTINUE;
        END IF;

        -- rename partition
        v_new_name := c.table_name || in_partition_prefix || v_high_value;
        --
        IF c.partition_name != v_new_name THEN
            EXECUTE IMMEDIATE
                'ALTER TABLE ' || c.table_name ||
                ' RENAME PARTITION ' || c.partition_name || ' TO ' || v_new_name;
        END IF;
    END LOOP;
END;
/

Here is a simple query to see your state:

SELECT
    t.table_name,
    t.partition_name,
    t.high_value
FROM user_tab_partitions t
WHERE t.table_name LIKE '%'
ORDER BY 1, 2;

And here is a more complex query where you can see more details (and number of rows in each partition):

WITH p AS (
    SELECT
        p.*,
        --
        'SELECT p.high_value'                                       || CHR(10) ||
        'FROM user_tab_partitions p'                                || CHR(10) ||
        'WHERE p.table_name = ''' || p.table_name || ''''           || CHR(10) ||
        '    AND p.partition_name = ''' || p.partition_name || '''' AS query_
    FROM user_tab_partitions p
)
SELECT
    p.table_name,
    p.partition_position AS position,
    p.partition_name,
    p.high_value,
    --
    LTRIM(REGEXP_SUBSTR(r.high_value, '[^,' || ']+', 1, 1)) AS header_1,
    LTRIM(REGEXP_SUBSTR(r.high_value, '[^,' || ']+', 1, 2)) AS header_2,
    LTRIM(REGEXP_SUBSTR(r.high_value, '[^,' || ']+', 1, 3)) AS header_3,
    LTRIM(REGEXP_SUBSTR(r.high_value, '[^,' || ']+', 1, 4)) AS header_4,
    --
    TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML(
        'SELECT /*+ PARALLEL(p,4) */ COUNT(*) AS r ' ||
        'FROM ' || p.table_name || ' PARTITION (' || p.partition_name || ') p'
        )), '/ROWSET/ROW/R')) AS count_rows,
    --
    p.subpartition_count    AS subpartitions,
    p.read_only
    --
FROM p
JOIN (
    SELECT
        p.table_name,
        p.partition_name,
        LTRIM(RTRIM(h.high_value, ' )'), '( ') AS high_value
    FROM p,
        -- trick to convert LONG to VARCHAR2 on the fly
        XMLTABLE('/ROWSET/ROW'
            PASSING (DBMS_XMLGEN.GETXMLTYPE(p.query_))
            COLUMNS high_value VARCHAR2(4000) PATH 'HIGH_VALUE'
        ) h
) r
    ON r.table_name         = p.table_name
    AND r.partition_name    = p.partition_name
ORDER BY 1, 2;