请看我在CSDN上的此篇文章,写的更为详细:http://blog.csdn.net/jiujiuaiqi/article/details/6907576
select object_id from dba_objects where object_name='NAQ_TRADE_ITEM_TRADENO_IND';
select object_id from dba_objects where object_name='NAQ_TRADE_ITEM_TRADENO_IND';
OBJECT_ID
----------
115225
查看ind$的创建语句,rdbms/admin/sql.bsq文件里
create table ind$ /* index table */
( obj# number not null, /* object number */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
ts# number not null, /* tablespace number */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
bo# number not null, /* object number of base table */
indmethod# number not null, /* object # for cooperative index method */
cols number not null, /* number of columns */
pctfree$ number not null, /* minimum free space percentage in a block */
initrans number not null, /* initial number of transaction */
maxtrans number not null, /* maximum number of transaction */
pctthres$ number, /* iot overflow threshold, null if not iot */
type# number not null, /* what kind of index is this? */
/* normal : 1 */
/* bitmap : 2 */
/* cluster : 3 */
/* iot - top : 4 */
/* iot - nested : 5 */
/* secondary : 6 */
/* ansi : 7 */
/* lob : 8 */
/* cooperative index method : 9 */
flags number not null,
/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */
/* 4 bits reserved for bitmap index version : 0x1E0000 */
property number not null, /* immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* functional index expr contains a PL/SQL function : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
online创建前:
select flags from ind$ where obj#=115225;
----------
115225
查看ind$的创建语句,rdbms/admin/sql.bsq文件里
create table ind$ /* index table */
( obj# number not null, /* object number */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
ts# number not null, /* tablespace number */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
bo# number not null, /* object number of base table */
indmethod# number not null, /* object # for cooperative index method */
cols number not null, /* number of columns */
pctfree$ number not null, /* minimum free space percentage in a block */
initrans number not null, /* initial number of transaction */
maxtrans number not null, /* maximum number of transaction */
pctthres$ number, /* iot overflow threshold, null if not iot */
type# number not null, /* what kind of index is this? */
/* normal : 1 */
/* bitmap : 2 */
/* cluster : 3 */
/* iot - top : 4 */
/* iot - nested : 5 */
/* secondary : 6 */
/* ansi : 7 */
/* lob : 8 */
/* cooperative index method : 9 */
flags number not null,
/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */
/* 4 bits reserved for bitmap index version : 0x1E0000 */
property number not null, /* immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* functional index expr contains a PL/SQL function : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
online创建前:
select flags from ind$ where obj#=115225;
FLAGS
----------
2050
这个2050 可以这么解释:
2050=2+2048=16进制的 2+800
查看ind$的创建语句会知道:
2050代表:2+800=表被分析过(/* analyzed : 0x02 */)+表是被dbms_stats分析的(/* global stats : 0x800 */)
表只有被dbms_stats分析过, global stats的状态才是yes.
online创建后:
select flags from ind$ where obj#=115225;
select flags from ind$ where obj#=115225;
FLAGS
----------
2562
SELECT 2562-2050 FROM dual;
2562-2050
----------
512
----------
2562
SELECT 2562-2050 FROM dual;
2562-2050
----------
512
增加的512可以这么解释:
select to_char(512,'xxxxxx') from dual;
select to_char(512,'xxxxxx') from dual;
TO_CHAR(512,'X
--------------
200
/* index is being online rebuilt : 0x200 */
索引正在被online重建
--------------
200
/* index is being online rebuilt : 0x200 */
索引正在被online重建
kill -9 16522
alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online;
ERROR:
ORA-03114: not connected to ORACLE
alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online;
ERROR:
ORA-03114: not connected to ORACLE
alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 16522
Session ID: 1144 Serial number: 5555
alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online;
*
ERROR at line 1:
ORA-08104: this index object 115225 is being online built or rebuilt
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 115225;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
COMMIT;
END;
/
但是如果表上存在事务或是有TM锁,那么这个PL/SQL块会久久的HANG在那里。直到事务结束。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-708783/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-708783/