分类： Oracle
2007-03-27 16:34:06
同样，Oracle在删除主键和约束的时候也会自动删除这个唯一索引。
但是，Oracle似乎没有提供直接的方式来检查一个索引是系统自动产生的还是由Oracle自动创建的。
首先，描述一下区分这两种情况的意义。
一、去除约束：个别的时候需要去掉唯一约束，允许重复的值插入到表中，这个时候如果无法区分两种不同的情况就会产生问题。
对于系统自动创建的唯一索引，在删除约束的同时被Oracle自动删除，那么这种情况只需要删除索引就可以保证重复数据插入。
但对于用户自动创建的唯一索引，即使删除了唯一约束，唯一索引会仍然存在，这个时候如果不手工删除唯一索引的话，是无法将重复数据插入到表中的。
二、为数据库中的对象生成脚本
如果需要完全重现对象的生成脚本必须区分两种不同的情况。
但是，Oracle并没有提供一种简单的方法来区分这两种不同的情况，下面看一个简单的例子：
SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY);
表已创建。
SQL> CREATE TABLE T2 (ID NUMBER NOT NULL, CONSTRAINT PK_T2 PRIMARY KEY (ID));
表已创建。
SQL> CREATE TABLE T3 (ID NUMBER NOT NULL);
表已创建。
SQL> CREATE UNIQUE INDEX PK_T3 ON T3(ID);
索引已创建。
SQL> ALTER TABLE T3 ADD PRIMARY KEY (ID);
表已更改。
SQL> CREATE TABLE T4 (ID NUMBER NOT NULL);
表已创建。
SQL> CREATE UNIQUE INDEX PK_T4 ON T4(ID);
索引已创建。
SQL> ALTER TABLE T4 ADD CONSTRAINT PK_T4 PRIMARY KEY (ID);
表已更改。
SQL> SELECT
2 INDEX_NAME,
3 TABLE_NAME,
4 STATUS,
5 GENERATED,
6 GLOBAL_STATS
7 FROM USER_INDEXES
8 WHERE TABLE_NAME LIKE 'T_'
9 ORDER BY TABLE_NAME;
INDEX_NAME TABLE_NAME STATUS G GLO
------------------------------ --------------- -------- - ---
SYS_C009200 T1 VALID Y NO
PK_T2 T2 VALID N NO
PK_T3 T3 VALID N NO
PK_T4 T4 VALID N NO
SQL> SELECT
2 CONSTRAINT_NAME,
3 TABLE_NAME,
4 GENERATED,
5 INDEX_NAME
6 FROM USER_CONSTRAINTS
7 WHERE TABLE_NAME LIKE 'T_'
8 AND CONSTRAINT_TYPE = 'P'
9 ORDER BY TABLE_NAME;
CONSTRAINT_NAME TABLE_NAME GENERATED INDEX_NAME
------------------------------ --------------- -------------- -----------
SYS_C009200 T1 GENERATED NAME SYS_C009200
PK_T2 T2 USER NAME PK_T2
SYS_C009204 T3 GENERATED NAME PK_T3
PK_T4 T4 USER NAME PK_T4
上面建立了4张表，每张表都设置了主键。第一章表采用行内约束的方式；第二章表采用行外约束的方式；第三张表，首先建立一个唯一索引，然后建立约束；第四张表，是在第三张表的基础上给约束增加了名称。
通过查询USER_INDEXES，只有T1的GENERATE列为Y，其他三张表的GENERATE列都是N，说明这个列包含的内容是索引名称是用户命名还是系统生成的。而不是我们需要找的索引本身是系统生成的还是用户创建的。
在观察USER_CONSTRAINTS视图，也找不到任何有价值的东西。
那么通过Oracle的一些工具能否得到结果呢：
SQL> SET LONG 1000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', TNAME) FROM TAB WHERE TNAME LIKE 'T_';
DBMS_METADATA.GET_DDL('TABLE',TNAME)
----------------------------------------------------------------------
CREATE TABLE "YANGTK"."T1"
( "ID" NUMBER,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
CREATE TABLE "YANGTK"."T2"
( "ID" NUMBER NOT NULL ENABLE,
CONSTRAINT "PK_T2" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
CREATE TABLE "YANGTK"."T3"
( "ID" NUMBER NOT NULL ENABLE,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
CREATE TABLE "YANGTK"."T4"
( "ID" NUMBER NOT NULL ENABLE,
CONSTRAINT "PK_T4" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
根据上面的信息可以看出，DBMS_METADATA包返回的表信息可以得到正确的行内约束或行外约束信息，对于系统自动产生的名称也会忽略掉，但仅从表信息是无法得到原始创建脚本的。
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES
2 WHERE TABLE_NAME LIKE 'T_';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------
CREATE UNIQUE INDEX "YANGTK"."SYS_C009200" ON "YANGTK"."T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
CREATE UNIQUE INDEX "YANGTK"."PK_T4" ON "YANGTK"."T4" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
CREATE UNIQUE INDEX "YANGTK"."PK_T3" ON "YANGTK"."T3" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
CREATE UNIQUE INDEX "YANGTK"."PK_T2" ON "YANGTK"."T2" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"
而得到的索引信息更是几乎完全一样。
下面看看通过EXP工具得到的结果：
E:>exp yangtk/yangtk file=struct.dmp rows=n tables=t1, t2, t3, t4
Export: Release 10.2.0.1.0 - Production on 星期二 7月 25 17:27:33 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集注: 将不导出表数据 (行)
即将导出指定的表通过常规路径...
. . 正在导出表 T1
. . 正在导出表 T2
. . 正在导出表 T3
. . 正在导出表 T4成功终止导出, 没有出现警告。
下面通过UltraEdit打开，找到建表的DDL语句：
TABLE "T1"
CREATE TABLE "T1" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS
ALTER TABLE "T1" ADD PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING ENABLE
TABLE "T2"
CREATE TABLE "T2" ("ID" NUMBER NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS
CREATE UNIQUE INDEX "PK_T2" ON "T2" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING
ALTER TABLE "T2" ADD CONSTRAINT "PK_T2" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING ENABLE
TABLE "T3"
CREATE TABLE "T3" ("ID" NUMBER NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS
CREATE UNIQUE INDEX "PK_T3" ON "T3" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING
ALTER TABLE "T3" ADD PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING ENABLE
TABLE "T4"
CREATE TABLE "T4" ("ID" NUMBER NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS
CREATE UNIQUE INDEX "PK_T4" ON "T4" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING
ALTER TABLE "T4" ADD CONSTRAINT "PK_T4" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING ENABLE
从上面的DDL看，exp已经改变了表原来的结构。对于T2本来没有建立索引，而如果通过EXP/IMP之后，会先建立索引再建立主键，从而结构和T4一致。
Oracle的DBMS_METADATA和EXP为了更好的实现他们各自的功能，并没有完全按照实际的情况来返回表的创建脚本。
Oracle的工具和提供的视图虽然没有体现出来二者的区别，但是Oracle本身显然是了解这些区别的。
SQL> INSERT INTO T1 VALUES (1);
已创建 1 行。
SQL> INSERT INTO T2 VALUES (1);
已创建 1 行。
SQL> INSERT INTO T3 VALUES (1);
已创建 1 行。
SQL> INSERT INTO T4 VALUES (1);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (1)
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (YANGTK.SYS_C009200)
SQL> INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (1)
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (YANGTK.PK_T2)
SQL> INSERT INTO T3 VALUES (1);
INSERT INTO T3 VALUES (1)
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (YANGTK.SYS_C009204)
SQL> INSERT INTO T4 VALUES (1);
INSERT INTO T4 VALUES (1)
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (YANGTK.PK_T4)
四张表由于主键的存在都无法插入重复数据，下面删除主键约束：
SQL> ALTER TABLE T1 DROP PRIMARY KEY;
表已更改。
SQL> ALTER TABLE T2 DROP PRIMARY KEY;
表已更改。
SQL> ALTER TABLE T3 DROP PRIMARY KEY;
表已更改。
SQL> ALTER TABLE T4 DROP PRIMARY KEY;
表已更改。
SQL> INSERT INTO T1 VALUES (1);
已创建 1 行。
SQL> INSERT INTO T2 VALUES (1);
已创建 1 行。
SQL> INSERT INTO T3 VALUES (1);
INSERT INTO T3 VALUES (1)
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (YANGTK.PK_T3)
SQL> INSERT INTO T4 VALUES (1);
INSERT INTO T4 VALUES (1)
*第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (YANGTK.PK_T4)
显然，Oracle是清楚哪些索引是在主键创建的时候自动建立的，哪些索引是在主键创建时就已经存在的。
说了这么多，下面就给出检查一个索引是否是创建约束时自动生成的办法：
SQL> SELECT A.NAME, B.NAME,
2 DECODE(BITAND(C.PROPERTY, 4096), 4096, 'SYSTEM CREATED', 'MANU CREATED') INDEX_TYPE
3 FROM SYS.OBJ$ A, SYS.OBJ$ B, SYS.IND$ C
4 WHERE A.OBJ# = C.BO#
5 AND B.OBJ# = C.OBJ#
6 AND A.NAME LIKE 'T_';
NAME NAME INDEX_TYPE
------------------------------ ------------------------------ --------------
T1 SYS_C009221 SYSTEM CREATED
T2 PK_T2 SYSTEM CREATED
T3 PK_T3 MANU CREATED
T4 PK_T4 MANU CREATED
简单的说，就是SYS.IND$中的PROPERTY列的第13位数字标识这个索引是否为系统自动创建。
