Switching from identity columns to sequences

I

If, for whatever reason, you decide you don't want the identity columns anymore and want to switch back to the classic sequences, this is for you.

Here is the plan for how you can achieve it:

1. get identity columns (identify tables and identity sequences)
2. create new sequences for every table with "{TABLE_NAME}_ID" name
3. get maximum value for each column to use it as a start value for new sequence
4. create backup for objects source code replacements (in annotations)
5. remove identity from all columns
6. add new sequences as a default values
7. fix source code (triggers, packages...); this is solved just partially

To identify columns with identities:

SELECT
    s.sequence_name,
    c.table_name,
    c.column_name,
    a.annotation_value AS old_seq_backup
FROM user_sequences s
LEFT JOIN user_tab_cols c
    ON c.data_default_vc    LIKE '"' || USER || '"."' || s.sequence_name || '".nextval'
LEFT JOIN user_annotations_usage a
    ON a.object_name        = c.table_name
    AND a.object_type       = 'TABLE'
    AND a.column_name       IS NULL
    AND a.annotation_name   = 'IDENTITY_BACKUP'
    AND a.annotation_value  = s.sequence_name
WHERE s.sequence_name       LIKE 'ISEQ$$%'
ORDER BY 1;

Here is the script for points 2-6:

DECLARE
    in_min          CONSTANT NUMBER         := 1000;    -- minimum sequence value
    in_pad          CONSTANT NUMBER         := 10;      -- how long are your sequences (for display)
    in_postfix      CONSTANT VARCHAR2(8)    := '_ID';   -- sequence name = table_name + postfix
    --
    v_max           NUMBER;
    v_next          NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE(
        '    | ' || RPAD('SEQ_NAME',    14)
        || ' | ' || RPAD('TABLE_NAME',  40)
        || ' | ' || LPAD('CURRENT',     in_pad)
        || ' | ' || LPAD('NEW_VALUE',   in_pad)
        || ' | ' || LPAD('COLUMN_NAME', 16)
        || ' | '
    );
    --
    FOR c IN (
        SELECT
            s.sequence_name,
            c.table_name || in_postfix AS new_sequence_name,
            s.cache_size,
            c.table_name,
            c.column_name
        FROM user_sequences s
        LEFT JOIN user_tab_cols c
            ON c.data_default_vc    LIKE '"' || USER || '"."' || s.sequence_name || '".nextval'
        WHERE s.sequence_name       LIKE 'ISEQ$$%'
        ORDER BY 1
    ) LOOP
        EXECUTE IMMEDIATE
            'SELECT MAX(' || c.column_name || ') FROM ' || c.table_name
            INTO v_max;
        --
        v_next := GREATEST(in_min, NVL(ROUND(v_max + POWER(10, LENGTH(v_max)) / 2, -LENGTH(v_max)), 0));
        --
        DBMS_OUTPUT.PUT_LINE(
            '    | ' || RPAD(c.sequence_name,   14)
            || ' | ' || RPAD(c.table_name,      40)
            || ' | ' || LPAD(NVL(TO_CHAR(v_max),  ' '), in_pad)
            || ' | ' || LPAD(NVL(TO_CHAR(v_next), ' '), in_pad)
            || ' | ' || LPAD(c.column_name,     16)
            || ' |'
        );
        --
        EXECUTE IMMEDIATE
            'DROP SEQUENCE IF EXISTS ' || c.new_sequence_name;
        --
        EXECUTE IMMEDIATE
            'CREATE SEQUENCE ' || c.new_sequence_name || ' MINVALUE ' || v_next || ' CACHE ' || c.cache_size;
        --
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name
            || ' ANNOTATIONS (ADD IF NOT EXISTS identity_backup ''' || c.sequence_name || ''')';
        --
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name
            || ' MODIFY ' || c.column_name || ' DROP IDENTITY';
        --
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name
            || ' MODIFY ' || c.column_name || ' DEFAULT ' || c.new_sequence_name || '.NEXTVAL';
        --
    END LOOP;
END;
/

If you are referencing identities in your code (not ideal), you'll need to change them manually. However, you can check this article to see how you can automate changing your objects.

And here's a query to help identify all the occurrences:

SELECT
    t.name,
    t.type,
    t.line,
    t.text,
    --
    a.old_sequence_name,
    a.new_sequence_name
    --
FROM user_source t
JOIN (
    SELECT
        a.annotation_value      AS old_sequence_name,
        a.object_name || '_ID'  AS new_sequence_name        -- adjust the postfix
    FROM user_annotations_usage a
    WHERE a.object_type         = 'TABLE'
        AND a.column_name       IS NULL
        AND a.annotation_name   = 'IDENTITY_BACKUP'
) a
    ON UPPER(t.text) LIKE UPPER('%' || a.old_sequence_name || '%')
ORDER BY
    1, 2, 3;

And to remove the annotations, you can run this:

BEGIN
    FOR c IN (
        SELECT table_name
        FROM user_tables
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name || ' ANNOTATIONS (DROP IF EXISTS "IDENTITY_BACKUP")';
    END LOOP; 
END;
/