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:
--------------------------------------------------------
-- 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定义信息时:
--------------------------------------------------------
-- 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。
Any sql developer version will have the issue.
Create a sys connection for a database.
Use the connection to do database export for SCOTT schema with constraints.
I can see PK in the export sql script output.
But I can’t see FK in the export sql script output.
Need to user SCOTT connection to do dabase export for SCOTT schema.
Then can see FK in the export sql script output.
If user is sys role, he can’t know each db account’s password, then can’t export FK contraints for other db account in SQL developer.
export_SCOTT_by_SYSTEM.sql
export_SCOTT_by_SCOTT.sql
You can see as following in export_SCOTT_by_SCOTT.sql
ALTER TABLE “EMP” ADD CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “DEPT” (“DEPTNO”) ENABLE;
You can’t see “ALTER TABLE “EMP” ADD CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “DEPT” (“DEPTNO”) ENABLE;” in export_SCOTT_by_SYSTEM.sql.