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:
以上SQL中未给出PK和FK约束的ddl信息。 如果使用scott用户登录数据库，导出自身schema的table,index,constraint定义信息时:
我们尝试导出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"
) ;
|
|
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。
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277141
