Oracle SQL Developer 的一个Bug

Oracle SQL Developer是Oracle近几年来推出的一款免费的图形界面SQL PL/SQL开发工具，在11g中它甚至被集成到了Oracle server软件中；如果你平常有用这款IDE的话，大概会注意到它提供了十分实用的Database copy,diff,export功能。 当在Oracle SQL developer中使用sys用户登录数据库，并尝试导出某些schema的ddl定义时，可能会丢失一些constraint约束的ddl信息，如以下例子: 我们尝试导出scott用户的表，索引以及约束的相关ddl，当使用sys用户登录数据库时，Oracle SQL developer给出的SQL:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

-------------------------------------------------------- --  File created - Monday-August-30-2010 -------------------------------------------------------- -------------------------------------------------------- --  DDL for Table BONUS --------------------------------------------------------     CREATE TABLE "BONUS"    (    "ENAME" VARCHAR2(10),     "JOB" VARCHAR2(9),     "SAL" NUMBER,     "COMM" NUMBER    ) ; -------------------------------------------------------- --  DDL for Table DEPT --------------------------------------------------------     CREATE TABLE "DEPT"    (    "DEPTNO" NUMBER(2,0),     "DNAME" VARCHAR2(14),     "LOC" VARCHAR2(13)    ) ; -------------------------------------------------------- --  DDL for Table EMP --------------------------------------------------------     CREATE TABLE "EMP"    (    "EMPNO" NUMBER(4,0),     "ENAME" VARCHAR2(10),     "JOB" VARCHAR2(9),     "MGR" NUMBER(4,0),     "HIREDATE" DATE,     "SAL" NUMBER(7,2),     "COMM" NUMBER(7,2),     "DEPTNO" NUMBER(2,0)    ) ; -------------------------------------------------------- --  DDL for Table SALGRADE --------------------------------------------------------     CREATE TABLE "SALGRADE"    (    "GRADE" NUMBER,     "LOSAL" NUMBER,     "HISAL" NUMBER    ) ;   -------------------------------------------------------- --  DDL for Index PK_DEPT --------------------------------------------------------     CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO")   ; -------------------------------------------------------- --  DDL for Index PK_EMP --------------------------------------------------------     CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO")   ;

以上SQL中未给出PK和FK约束的ddl信息。 如果使用scott用户登录数据库，导出自身schema的table,index,constraint定义信息时:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

-------------------------------------------------------- --  File created - Monday-August-30-2010 -------------------------------------------------------- -------------------------------------------------------- --  DDL for Table BONUS --------------------------------------------------------     CREATE TABLE "BONUS"    (    "ENAME" VARCHAR2(10),     "JOB" VARCHAR2(9),     "SAL" NUMBER,     "COMM" NUMBER    ) ; -------------------------------------------------------- --  DDL for Table DEPT --------------------------------------------------------     CREATE TABLE "DEPT"    (    "DEPTNO" NUMBER(2,0),     "DNAME" VARCHAR2(14),     "LOC" VARCHAR2(13)    ) ; -------------------------------------------------------- --  DDL for Table EMP --------------------------------------------------------     CREATE TABLE "EMP"    (    "EMPNO" NUMBER(4,0),     "ENAME" VARCHAR2(10),     "JOB" VARCHAR2(9),     "MGR" NUMBER(4,0),     "HIREDATE" DATE,     "SAL" NUMBER(7,2),     "COMM" NUMBER(7,2),     "DEPTNO" NUMBER(2,0)    ) ; -------------------------------------------------------- --  DDL for Table SALGRADE --------------------------------------------------------     CREATE TABLE "SALGRADE"    (    "GRADE" NUMBER,     "LOSAL" NUMBER,     "HISAL" NUMBER    ) ;   -------------------------------------------------------- --  Constraints for Table EMP --------------------------------------------------------     ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE;   -------------------------------------------------------- --  Constraints for Table DEPT --------------------------------------------------------     ALTER TABLE "DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ENABLE; -------------------------------------------------------- --  DDL for Index PK_EMP --------------------------------------------------------     CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO")   ; -------------------------------------------------------- --  DDL for Index PK_DEPT --------------------------------------------------------     CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO")  ;   -------------------------------------------------------- --  Ref Constraints for Table EMP --------------------------------------------------------     ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")       REFERENCES "DEPT" ("DEPTNO") ENABLE;

Oracle SQL developer给出了正确的SQL，包含FK和PK约束。 这个Bug在8月(2010 Aug)被确认将影响此前所有版本的Oracle SQL developer。