问题提出：
eingmarra:
如何在一个有数据的表中插入一列？
想插入一列id（在表的最前面），但oracle中只能在最后插入，好象不能指定插入的位置啊！？请各位高手解答一下！
谢谢！
以一个online redefinination的测试来解答这个问题
1、准备测试场景SQL> show user
USER is "HR"
SQL> select * from v$version;
BANNER
------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> create table b (col number) ;
Table created.
SQL> insert into table b values(1);
insert into table b values(1)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> insert into b values(1);
1 row created.
SQL> insert into b values(2);
1 row created.
SQL> insert into b values(3);
1 row created.
SQL> insert into b values(4);
1 row created.
SQL> insert into b values(5);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from b;
COL
----------
1
2
3
4
5
2、先确定表是不是能做online redefinination
SQL> connect / as sysdba
Connected.
SQL> execute dbms_redefinition.CAN_REDEF_TABLE('HR','B');
BEGIN dbms_redefinition.CAN_REDEF_TABLE('HR','B'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HR"."B" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1
QL>
SQL> alter table hr.b add constraint pk_b primary key (col);
Table altered.
SQL> execute dbms_redefinition.CAN_REDEF_TABLE('HR','B');
PL/SQL procedure successfully completed.
SQL>
3、建一个中间表
SQL> create table init_b
2 (
3 id varchar2(10),
4 col number
5 )
6 /
Table created.
4、开始redefinition
SQL> execute dbms_redefinition.START_REDEF_TABLE('HR','B','INIT_B','COL COL');
PL/SQL procedure successfully completed.
SQL> select * from hr.init_b;
ID COL
---------- ----------
1
2
3
4
5
此时你可以在中间表上建一些索引，trigger...
5、如有必要同步一下数据
SQL> execute dbms_redefinition.SYNC_INTERIM_TABLE('HR','B','INIT_B');
PL/SQL procedure successfully completed.
6、完成redefinition
SQL> execute dbms_redefinition.FINISH_REDEF_TABLE('HR','B','INIT_B');
PL/SQL procedure successfully completed.
7、看一下结果。
SQL> desc hr.b
Name Null? Type
----------------------- -------- -----------------
ID VARCHAR2(10)
COL NUMBER
SQL> desc hr.init_b;
Name Null? Type
----------------------- -------- -----------------
COL NOT NULL NUMBER
SQL>
