Swap and drop in Oracle Database

I want to alter the data type for one column in a big table w/ billions of rows and using range partition.  I don't want to lock the table, so I suppose I can only create a new table w/ the new data type for that column and copy the data over and drop the old table.  Is this a correct assumption?  Also if I want to copy a range partitioned table, what would be the most efficient way to do this?  Thanks.
thomaszhwangAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ggzfabCommented:
You could create a view with a datatype change function for the column and (after renaming the original table) give it the name of the table.
This will cost some extra processing, but perhaps worth a try. Just run a select on the query and on the original table to see or the response time isn't affected too much.
0
sdstuberCommented:
Try using dbms_redefinition package.

It will do exactly what you're intending to do, that is make a new table and copy data, but it will also manage live transactions against original table so the new table is also updated.
Furthermore, when you're ready to drop and swap, it only needs brief lock to drop the old and rename the new back to the old name.
0
thomaszhwangAuthor Commented:
@ggzfab I'm new to Oracle and I cannot understand how views can help here.  Could you please explain a little bit more?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

thomaszhwangAuthor Commented:
@sdstuber Can you give me more information?  Is this package an external package I need to install?  How can I actually use this package?  Thanks.
0
sdstuberCommented:
It's a built in package.  Should be installed in database by default.

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_redefi.htm

If you can provide the before and after structures you're looking for, I can provide and example of the calls you'll need.
0
thomaszhwangAuthor Commented:
Thanks sdstuber.  I just want to change the data type for one column from varchar2(2000) to nvarchar2(2000).
0
sdstuberCommented:
I meant full ddl for original and final table, but ok I'll mock up the rest of it.
0
sdstuberCommented:
First, I'll create a table to be redefined and fill it with data
CREATE TABLE redef_test
(
    id   INTEGER NOT NULL,
    d    DATE NOT NULL,
    text VARCHAR2(2000),
    CONSTRAINT pk_redef_test PRIMARY KEY(id)
)
PARTITION BY RANGE
    (d)
    INTERVAL ( NUMTOYMINTERVAL(1, 'MONTH') )
    (
        PARTITION
            redef_test_p1
            VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
    );

CREATE INDEX idx_redef_test
    ON redef_test(d);

INSERT INTO redef_test
    SELECT LEVEL, DATE '2012-10-01' + LEVEL * 5, TO_CHAR(DATE '2012-10-01' + LEVEL * 5, 'jsp')
      FROM DUAL
    CONNECT BY LEVEL < 100;

COMMIT;

Open in new window


Next, confirm that dbms_redefintion is allowed on your table.
You'll get an exception if it won't work.
If you don't have a primary or unique key on your table, then use 2 for the options_flag
BEGIN
    DBMS_REDEFINITION.can_redef_table(
        uname          => USER,
        tname          => 'REDEF_TEST',
        options_flag   => 1,  -- 1 use pk,  2 use rowid
        part_name      => NULL  --
    );
END;

Open in new window


 next, create a new table structure you want
 Note, no constraints, indexes or triggers on the new table
 but partitions are defined

CREATE TABLE redef_test_new
(
    id   INTEGER,
    d    DATE,
    text NVARCHAR2(2000)
)
PARTITION BY RANGE
    (d)
    INTERVAL ( NUMTOYMINTERVAL(1, 'MONTH') )
    (
        PARTITION
            redef_test_p1
            VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
    );

Open in new window


Do the redefinition

DECLARE
    TYPE error_tab IS TABLE OF dba_redefinition_errors%ROWTYPE
        INDEX BY PLS_INTEGER;

    v_error_count PLS_INTEGER;
    v_errors      error_tab;
BEGIN
    -- Session changes suggested by the Administrator's Guide
    -- If you want to use these, adjust the degree to something reasonable for your system
    --EXECUTE IMMEDIATE 'alter session force parallel dml parallel 2';
    --EXECUTE IMMEDIATE 'alter session force parallel query parallel 2';

    DBMS_REDEFINITION.start_redef_table(
        uname         => USER,
        orig_table    => 'REDEF_TEST',
        int_table     => 'REDEF_TEST_NEW',
        col_mapping   => 'ID ID,D D,TRANSLATE(TEXT USING NCHAR_CS) TEXT'
    );

    --execute immediate 'drop materialized view REDEF_TEST_NEW';

    DBMS_REDEFINITION.copy_table_dependents(
        uname        => USER,
        orig_table   => 'REDEF_TEST',
        int_table    => 'REDEF_TEST_NEW',
        num_errors   => v_error_count
    );

    IF v_error_count > 0
    THEN
        raise_application_error(
            -20001,
               v_error_count
            || ' errors encountered, check DBA_REDEFINITION_ERRORS view for more information'
        );
    ELSE
        DBMS_REDEFINITION.sync_interim_table(
            uname        => USER,
            orig_table   => 'REDEF_TEST',
            int_table    => 'REDEF_TEST_NEW'
        );
        DBMS_REDEFINITION.finish_redef_table(
            uname        => USER,
            orig_table   => 'REDEF_TEST',
            int_table    => 'REDEF_TEST_NEW'
        );
    END IF;
END;
/

Open in new window


The names of the tables are now reversed  so "redef_test_new" is actually my original table
Don't execute this drop until you are confident that the redefined table is correct.
--DROP TABLE redef_test_new PURGE;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomaszhwangAuthor Commented:
I will test this out tomorrow and let you know if I encounter any issue.  I will leave this ticket open for now.  Thanks sdstuber.
0
thomaszhwangAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.