Online redefinition table

Online redefinition tables ORACLE databases support online (off-database) Modification of various physical and logical attributes of a table, including partitions, tablespaces, adding columns, deleting columns, and changing a table to an IOT table. Now let's test this powerful function: [SQL] -- experiment environment 14:23:42 SCOTT @ orcl> select * from v $ version; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production PL/SQL Release 11.2.0.1.0-Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: version 11.2.0.1.0-Production NLSRTL Version 11.2.0.1.0-Production selected 5 Line. [SQL] -- create the original table 14:07:36 SCOTT @ orcl> create table admin_emp (14:07:54 2 empno number (4), ename varchar2 (10), job varchar2 (9 ), deptno number (4); the table has been created. Time in use: 00: 00: 01.01 we use the primary key method for redefinition: Online redefinition supports key-pressing and ROWID-based redefinition. The key can be the primary key or the key with a unique index [SQL] -- add the primary key to the original table at 14:13:53 SCOTT @ orcl> alter table admin_emp add primary key (empno); the table has been changed. 14:14:17 SCOTT @ orcl> is the desc admin_emp name blank? Type --------------------------------------------------- -------- ------------- empno not null number (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) deptno number (4) [SQL] -- verify whether the table can be redefined online at 14:14:26 SCOTT @ orcl> CONN/AS SYSDBA is connected. 14:14:39 SYS @ orcl> BEGIN 14:14:43 2 DBMS_REDEFINITION.CAN_REDEF_TABLE ('Scott ', 'admin _ emp', 14:14:43 3 DBMS_REDEFINITION.CONS_USE_PK); 14:14:43 4 END; 14:14:43 5/PL/SQL process completed successfully. [SQL] 14:15:09 SYS @ orcl> conn scott/tiger is connected. 14:15:15 SCOTT @ orcl> -- CREATE an intermediate TABLE 14:15:33 SCOTT @ orcl> create table int_admin_emp 14:16:44 2 (empno NUMBER (5) primary key, 14:16:44 3 ename VARCHAR2 (15) not null, 14:16:44 4 job VARCHAR2 (10), 14:16:44 5 mgr NUMBER (5), 14:16:44 6 hiredate date default (sysdate), 14:16:44 7 sal NUMBER (), 14:16:44 8 deptno NUMBER (3) not null, 14:16:44 bonus NUMBER (1000) DEFAULT () 14:16:44 10 partition by range (e Mpno) 14:16:44 11 (PARTITION emp1000 values less than (1000) TABLESPACE SCOTT_TBS, 14:16:44 12 PARTITION emp2000 values less than (2000) tablespace users); the table has been created. Time in use: 00: 00: 00.32 14:16:46 SCOTT @ orcl> -- start to redefine processing 14:17:04 SCOTT @ orcl> conn/as sysdba is connected. 14:17:25 SYS @ orcl> BEGIN 14:17:40 2 DBMS_REDEFINITION.START_REDEF_TABLE ('Scott ', 'admin _ emp', 'int _ admin_emp', 14:17:40 3' empno, ename, job, deptno + 10 deptno, 0 bonus ', 14:17:40 4 dbms_redefinition.cons_use_pk); 14:17:40 5 END; 14:17:40 6/PL/SQL process completed successfully. Used time: 00: 00: 10.84 14:17:52 SYS @ orcl> -- Copy dependent object 14:18:29 SYS @ orcl> DECLARE 14:18:29 2 num_errors PLS_INTEGER; 14:18:29 3 BEGIN 14:18:29 4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('Scott ', 'admin _ emp', 'int _ admin_emp ', 14:18:29 5 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors); 14:18:29 END; at 14:18:29, the PL/SQL process was successfully completed. [SQL] 14:19:10 SYS @ orcl> -- synchronize the intermediate table 14:19:49 SYS @ orcl> BEGIN 14:19:49 2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('Scott ', 'admin _ emp', 'int _ admin_emp '); 14:19:49 3 END; 14:19:49 4/PL/SQL process completed successfully. Time used: 00: 00: 00.06 14:19:50 SYS @ orcl> -- re-define 14:20:13 SYS @ orcl> BEGIN 14:20:13 2 DBMS_REDEFINITION.FINISH_REDEF_TABLE ('Scott ', 'admin _ emp ', 'Int _ admin_emp '); 14:20:13 3 END; 14:20:13 4/PL/SQL process completed successfully. Time used: 00: 00: 00.95 14:20:17 SYS @ orcl> conn scott/tiger connected. -- We found that the table structure has changed the table online redefinition. 14:20:26 SCOTT @ orcl> desc admin_emp name is blank? Type -------- --------------------------------- empno not null number (5) ename not null VARCHAR2 (15) JOB VARCHAR2 (10) mgr number (5) hiredate date sal number (7, 2) deptno not null number (3) bonus number () -- check whether it has been redefined as a partition table 14:22:42 SCOTT @ orcl> select * from admin_emp partition (emp1000); unselected row use time: 00: 00: 00.01 -- Note: Online redefinition tables require a large amount of free space. DML operations can be performed when online table redefinition is performed. Generally, the package DBMS_REDEFINITION or EM is used to execute online redefinition.