ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 9i online redefinination

9i online redefinination

原创 Linux操作系统 作者:hrb_qiuyb 时间:2008-01-31 08:19:57 0 删除 编辑

问题提出:

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>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9134/viewspace-166501/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ora-01438诊断
下一篇: TPC-C估值
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2008-01-30

  • 博文量
    50
  • 访问量
    534872