创建索引和索引创建失败清理

1.在线创建索引

vi /tmp/IND_CONTRACT_PRODUCT_SUSP_3.sql
nohup sqlplus user/pass @/tmp/IND_CONTRACT_PRODUCT_SUSP_3.sql 1>/tmp/IND_CONTRACT_PRODUCT_SUSP_3.log &

set time on
set timing on
alter session set ddl_lock_timeout=1000;
ALTER SESSION FORCE PARALLEL DDL parallel 4;
alter session set workarea_size_policy = manual;
alter session set sort_area_size=1024000000;
alter session set db_file_multiblock_read_count= 128;
alter session set "_sort_multiblock_read_count"= 128;
CREATE INDEX IND_CONTRACT_PRODUCT_SUSP_3 ON T_CONTRACT_PRODUCT(SUSPEND,PRODUCT_ID,START_PAY_DATE) tablespace LIFEINDEX_T_L ONLINE;
alter index IND_CONTRACT_PRODUCT_SUSP_3 noparallel;
exit;

SELECT * FROM DBA_IND_COLUMNS where INDEX_OWNER='USER' and TABLE_NAME='T_AGENT';

--查询索引状态

col INDEX_OWNER for a20
col INDEX_NAME for a20
col TABLE_OWNER for a20
col COLUMN_NAME for a20
select index_name,status,table_owner,table_name from dba_indexes where owner='USER' and INDEX_NAME in
('IND_CONTRACT_PRODUCT_SUSP_3');

INDEX_NAME STATUS TABLE_OWNER TABLE_NAME
-------------------- ------------------------ -------------------- ------------------------------
IND_CONTRACT_PRODUCT VALID USER T_CONTRACT_PRODUCT_SUSP_3

--查询要创建索引的大小

col OWNER for a20
col SEGMENT_NAME for a30
col SEGMENT_TYPE for a30
col TABLESPACE_NAME for a40
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024/1024 G from dba_segments where OWNER='USER' and SEGMENT_NAME in
('IND_CONTRACT_PRODUCT_SUSP_3');

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME G
-------------------- ------------------------------ ------------------------------ ---------------------------------------- ----------
USER IND_CONTRACT_PRODUCT_SUSP_3 INDEX LIFEINDEX_T_L 30.4423828

Elapsed: 01:22:05.97

索引创建成功了，1个半小时，大小为30G，状态有效

2.清理在线创建索引失败的对象

1.select object_name,object_id from dba_objects where object_name='IND_CONTRACT_PRODUCT_SUSP_3';

OBJECT_NAME OBJECT_ID
--------------------------------------------------------------------------------
IND_CONTRACT_PRODUCT_SUSP_3 886767

2.
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 886767;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
COMMIT;
END;
/