Shulou(Shulou.com)06/01 Report--

In the local test library, there is not enough space. As a result, a table is created with more than 6 million records. I want to create an index. The physical segment has more than 340 M.

The temporary segment size is 100m, and as a result, if you want to create an index, you always report an error that the temporary table space is insufficient.

[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

Eliminated the problem of insufficient tablespace in which the index is located, monitored it in real time, and found that the utilization rate of temporary tablespace had risen to 100% in an instant, and then reported the error of ORA-01652.

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

Alter database tempfile'/ u03 resize ora11g resize oradata resize temp01.dbf`

As a result, when you try again, you will have the following error.

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

As soon as I saw it, I crashed and thought the database was dead again. At a glance, the process is still there and can still 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 06:27 pts/0 00:00:00 grep smon

View the alert log.

Fri Jun 06 06:26:14 2014

Alter database tempfile'/ u03 resize ora11g resize oradata resize temp01.dbf`

Completed: alter database tempfile'/ u03max ora11g resize oradataUnique temp01.dbf' oradata

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 to create it again, and the result is one error after another.

Create it again, indicating 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

What if I delete and rebuild.

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

Check the status of Index and show that it 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 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 wait forever. And after a while, there was no progress.

In a production environment, it would definitely be an accident. In earlier versions, you might just have to restart and let smon do the cleanup.

I don't know whether to start with 10g or 11g, there is a dbms_repair package, which also includes a very practical method. Can solve this problem.

SQL > declare

2 isClean boolean

three

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 finally the result I expected, and it seems that index's online rebuild should also be used cautiously in production.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.