How to perform an online redefinition to change a production table

The scenario is that there is a table in Scott schema which is in production use and it needs a structural change i.e there are 9 columns and one column data type has to be changed or may be the table needs to be moved to a different tablespace. In my case i will be moving it to a different tablespace. My table is in USERS tablespace and I want to move it to EMPLOYEE tablespace.

Now it's a production table and a downtime can not be taken so how do we move the table to a different tablespace or change the structure of the table on the go ?

There is something called online redefinition which can be used in populating a new table with new requirements in my case new tablespace and at some point when they become identical they can be swapped and the new table can be renamed to original table name and can be made primary in PROD. This is the concept of the theory.

Let's take an example now.  

I have a databases Batch38, and schema user Scott has a

table emp which is being used in PROD:

I am using sql developer to create the DDL structure command.
Below is the DDL command to create the empty table:

I will be using that sql of the table and create another table with a different name.

Create the tablespace for the new table. I want to create a registration tablespace for the registration table of scott.

Below is my command to create the tablespace: CREATE TABLESPACE EMPLOYEE    DATAFILE   '/u14/oradata/Batch38/employee01.dbf'    SIZE 100M    AUTOEXTEND ON    NEXT 100M    MAXSIZE UNLIMITED; Below command can be used to verify the tablespace is created: select * from v$tablespace;

Now i can use Employee tablespace where the new table should

be populated using online redefinition.

Let's create the empty table using the current table DDL and keep

it in employee tablespace: I highlighted what i changed.

CREATE TABLE "SCOTT"."EMP_BKP"   ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP_BK" PRIMARY KEY ("EMPNO")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "EMPLOYEE"  ENABLE, CONSTRAINT "EMP_SAL_CK" CHECK (sal between 800 and 6000) ENABLE, CONSTRAINT "FK_DEPTNO_B" FOREIGN KEY ("DEPTNO")  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "EMPLOYEE" ;

Set the ddl_lock_timeout:

alter session set ddl_lock_timeout=300;

MAP each original column to the new (same) name on the new table,

and perform step to populate/synchronize the data to the new table.

-- map all the columns in the interim table to the original table

DECLARE col_mapping VARCHAR2(1000); BEGIN col_mapping := 'EMPNO EMPNO , '|| 'ENAME ENAME , '|| 'JOB JOB , '|| 'MGR MGR , '|| 'HIREDATE HIREDATE , '|| 'SAL SAL , '|| 'COMM COMM , '|| 'DEPTNO DEPTNO ' ; DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP', 'EMP_BKP', col_mapping); END; /

Now perform the steps for the indexes/constraints:

DECLARE error_count pls_integer := 0; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT', 'EMP', 'EMP_BKP', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,TRUE, error_count); DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count)); END; /

Review any errors:
select * from DBA_REDEFINITION_ERRORS;

Check the row count by select * or select count(*):

-- perform swap of emp_bkp table to become emp and emp to become emp_bkp table

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMP', 'EMP_BKP');

I have in between swapping committed two inserts in primary table:

insert into emp values (7776, 'omer' , 'DBA', 7902, sysdate, 2000, 300, 30); insert into emp values (7777, 'ahmad' , 'DBA', 7902, sysdate, 1000, 300, 30); commit

Now when i select * from emp:

Lets drop the bkp table now:

Lets check where does the table exists now as the goal and why we did was to have this table in employee tablespace without affecting the PROD table.

We are all done the table is moved to the required tablespace.