• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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.
0
thomaszhwang
Asked:
thomaszhwang
  • 5
  • 4
5 Solutions
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now