在有些极端情况下,数据库由于某种原因无法执行ddl操作,但是可以执行dml语句,我们可以通过dml语句来替代ddl操作(非官方支持,纯属个人闲着无聊中的测试,请勿模仿,否则后果自负)
创建测试用户xifenfei
[oracle@web103 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 16 23:04:25 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user xifenfei identified by "www.xifenfei.com";
User created.
SQL> grant dba to xifenfei;
Grant succeeded.
创建测试index，并使用10046跟踪
SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_18838.trc
SQL> create index ind_xifenfei_1 on t_xifenfei(object_id,data_object_id);
Index created.
SQL> exit
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
分析trace文件,找出来核心操作步骤
--插入创建index的记录到obj$中
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,
remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
--插入index的信息到seg$中
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,
extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1,
scanhint, bitmapranges)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,
:17),:18,:19)
--继续分配index的空间
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19
where
ts#=:1 and file#=:2 and block#=:3
--插入index对应的列信息到icol$中
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,
spare1,spare2,spare3)
values
(:1,:2,:3,:4,0,0,0,:5,:6,:7,:8)
--插入index信息到ind$中
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,
pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,
cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,
indmethod#,trunccnt,spare1,spare4,spare2,spare6)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,
:29,:30,:31,:33)
通过这里我们基本上可以整体概况出来创建index主要操作了obj$,seg$,icol$,ind$几张基表,那么如果我们想通过dml语句删除index,那可以尝试人工删除这些新增记录
分析人工dml语句删除index
[oracle@web103 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 16 23:31:49 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> select object_id,data_object_id from user_objects where object_name='IND_XIFENFEI_1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
114642 114642
SQL> select object_id,data_object_id from user_objects where object_name='T_XIFENFEI';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
114641 114641
SQL> SELECT OBJ#,DATAOBJ# FROM SYS.OBJ$ WHERE NAME='IND_XIFENFEI_1';
OBJ# DATAOBJ#
---------- ----------
114642 114642
--通过上面语句我们可以确定,IND_XIFENFEI_1 index的obj#和dataobj#都是为114642,而且可以直接查询obj$基表获得
SQL> SELECT OBJ#,DATAOBJ#,TS#,FILE#,BLOCK# FROM SYS.IND$ WHERE OBJ#=114642;
OBJ# DATAOBJ# TS# FILE# BLOCK#
---------- ---------- ---------- ---------- ----------
114642 114642 4 4 323994
--ind$结合obj$.obj#的信息,可以获得ts#,file#,block#
SQL> SELECT TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='IND_XIFENFEI_1';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USERS 4 323994
SQL> SELECT TS# FROM V$TABLESPACE WHERE NAME='USERS';
TS#
----------
4
--这里证明ind$中的相关ts#,file#,block#和seg#中存储一致
SQL> SELECT COL#,POS# FROM SYS.icol$ WHERE OBJ#=114642;
COL# POS#
---------- ----------
4 1
5 2
SQL> SELECT COL#,NAME from SYS.COL$ where OBJ# =114641;
COL# NAME
---------- ------------------------------
1 OWNER
2 OBJECT_NAME
3 SUBOBJECT_NAME
4 OBJECT_ID
5 DATA_OBJECT_ID
6 OBJECT_TYPE
7 CREATED
8 LAST_DDL_TIME
9 TIMESTAMP
10 STATUS
11 TEMPORARY
12 GENERATED
13 SECONDARY
14 NAMESPACE
15 EDITION_NAME
15 rows selected.
--这里证明icol$中存储的列，正好和我们创建index指定列一致
人工dml删除index
SQL> conn / as sysdba
Connected.
SQL> delete from obj$ where obj#=114642 and dataobj#=114642;
1 row deleted.
SQL> delete from sys.seg$ where file#=4 and ts#=4 and block#=323994;
1 row deleted.
SQL> delete from icol$ where obj#=114642;
2 rows deleted.
SQL> delete from ind$ where obj#=114642 and file#=4 and ts#=4 and block#=323994;
1 row deleted.
SQL> commit;
Commit complete.
检查dml语句删除index效果
SQL> select object_id,data_object_id from user_objects where object_name='IND_XIFENFEI_1';
no rows selected
SQL> SELECT TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='IND_XIFENFEI_1';
no rows selected
SQL> select * from dba_indexes where index_name='IND_XIFENFEI_1';
no rows selected
从数据字典层面看，index IND_XIFENFEI_1确实已经被删除
尝试重建同名index
从数据字典层面查询,该index已经被删除,但是创建报ORA-00955: name is already used by an existing object错误
SQL> create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id);
create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
解决重建同名index报ORA-00955错误
SQL> ALTER SESSION SET EVENTS '955 trace name errorstack level 3';
Session altered.
SQL> create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id);
create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_20277.trc
--分析trace文件发现创建index执行计划部分
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 548 | |
| 1 | INDEX BUILD NON UNIQUE | IND_XIFENFEI_1| | | | |
| 2 | SORT CREATE INDEX | | 127K | 3302K | | |
| 3 | INDEX FAST FULL SCAN | IND_XIFENFEI_1| | | | |
------------------------------------------------+-----------------------------------+
--从这里大概知道原因了，创建index之时，由于shared pool里面依旧记录了老的index信息，因此再次创建index之时，
--直接从老index读取信息，而且明确也依旧是在shared pool里面检查未通过，从而出现了ORA-00955错误
SQL> alter system flush shared_pool;
System altered.
SQL> create index xifenfei.ind_xifenfei_1 on xifenfei.t_xifenfei(object_id,data_object_id);
Index created.
SQL> select obj#,dataobj# from SYS.obj$ where name='IND_XIFENFEI_1';
OBJ# DATAOBJ#
---------- ----------
114643 114643
SQL> select object_type,object_name from dba_objects where object_id=114643;
OBJECT_TYPE
-------------------
OBJECT_NAME
--------------------------------------------------------------------------------
INDEX
IND_XIFENFEI_1
SQL> select index_name from dba_indexes where table_name='T_XIFENFEI' AND OWNER='XIFENFEI';
INDEX_NAME
------------------------------
IND_XIFENFEI_1
补充说明
1.在不同的数据库版本,不同的平台可能操作语句以后一定的出入,依据你测试为准
2.在dml删除index操作中,可能还涉及到一些其他外围表处理,比如统计信息等
3.切勿在生产库中测试,否则可能导致数据库数据字典混乱,后果非常严重