Problems caused by Online usage of index creation in Oracle

Problems caused by Online usage of index creation in Oracle

In the local test database, the original space is insufficient. As a result, a table is created with more than 6 million records and you want to create an index. The physical segment has more than 340 MB.

The size of the temporary segment is 100 mb. If you want to create an index, the system always reports the error of insufficient temporary tablespace.

[Ora11g @ rac1 test] $ ksh test. sh "create unique index t_pk on t (object_id) tablespace pool_data nologging online ;"
Create unique index t_pk on t (object_id) tablespace pool_data nologging online
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMPTS1

Excluding the index of the tablespace is not enough, real-time monitoring, found that the temporary tablespace usage in an instant soared to 100%, and then reported the ORA-01652 error.

Oracle index and null

What about Oracle index?

Introduction to Oracle Virtual index

Oracle index monitoring)

Online rebulid index exception termination encountering ORA-08104

After trying various methods, expand the temporary section and try again.

Alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 200 M;

The following error occurs when you try again.
Create unique index t_pk on t (object_id) tablespace pool_data nologging online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01114: IO error writing block to file (block #)
ORA-01114: IO error writing block to file 201 (block #15439)
ORA-27072: File I/O error.
Additional information: 4
Additional information: 15439
Additional information: 4096
Process ID: 5683
Session ID = 18 Serial number: 103

Elapsed: 00:00:21. 11
ERROR:
ORA-03114: not connected to ORACLE

At first glance, some crashes, and the database crashes again. At first glance, the process is still in progress and can be connected.

[Ora11g @ rac1 dbm_lite] $ ps-ef | grep smon
Ora11g 2357 1 0 05:32? 00:00:01 ora_smon_TEST01
Ora11g 5746 5327 0 00:00:00 pts/0 grep smon

View alert logs.

Fri Jun 06 06:26:14 2014
Alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 200 M
Completed: alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 200 M
Fri Jun 06 06:26:39 2014
Online index (re) build cleanup: objn = 15331 maxretry = 2000 forever = 0
Fri Jun 06 06:26:57 2014
Non critical error OR

Then try again, and the results are incorrect one after another.

Create again, prompting that the index already exists.
Create unique index t_pk on t (object_id) tablespace pool_data nologging
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Then I will delete and recreate it.

Drop index t_pk
*
ERROR at line 1:
ORA-08104: this index object 15334 is being online built or rebuilt

Try the force option.

Drop index t_pk force
*
ERROR at line 1:
ORA-29862: cannot specify FORCE option for dropping non-domain index

View the status of the Index. The value is valid.

* ********* Index details info *****************

INDEX_NAME TABLESPACE INDEX_TYPE uniquenes par COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
--------------------------------------------------------------------------------------------------------------------------------
T_PK POOL_DATA normal unique no OBJECT_ID TABLE VALID N
 
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
-------------------------------------------------------------------------------------------------
T T_PK 39174 6856704
 
Can I rebuild it again?

Alter index t_pk rebuild parallel 4
*
ERROR at line 1:
ORA-08104: this index object 15334 is being online built or rebuilt

Helpless, you can't always wait. After a while, there was no progress. If it is in a production environment, it is definitely an accident. In earlier versions, only restart is allowed for smon to be cleaned up. I don't know whether the dbms_repair package starts from 10 Gb or 11 GB. It also includes a very practical method. This problem can be solved.

SQL> declare
2 isClean boolean;
3
4 begin
5 isClean: = FALSE;
6 while isClean = FALSE loop
7 isClean: = dbms_repair.online_index_clean (dbms_repair.all_index_id,
8 dbms_repair.lock_wait );
9 dbms_lock.sleep (2 );
10 end loop;
11 exception
12 when others then
13 RAISE;
14 end;
15/

PL/SQL procedure successfully completed.

After running successfully, try again.

Alter index t_pk rebuild parallel 4
*
ERROR at line 1:
ORA-01418: specified index does not exist

This is my expectation. It seems that in production, the online rebuild of index should also be used with caution.