Handling the failure of invalid index ORA-20000

Troubleshooting invalid index ORA-20000

Author: Zhao Quanwen Net Name: guestart

This Thursday morning, the development students feedback, Oracle database link will be broken after a period of time, he asked if it is because IIS and database are not in a network segment reasons, to long connection can it? So, logging in to EMCC 12C monitoring of this database found that there are 2 SQL execution failed for a long time, as shown in the following figure.

Check the alert log file and find the following error, as shown below,

Look at the trc file shown in the picture above, as shown in the following operation,

Found that the error is ORA-20000, that is, an index is unavailable under the table corresponding to the user, as shown in the figure below.

Alert log file also has that ORA-20000 error, see the figure below,

Next, the processing steps are as follows:

1. Enter sqlplus, query found that there is an invalid index, as shown in the following figure,

2. More than 35 million data were found in the query table T_STUDENT_DURATION, as shown in the figure below.

3. Use rebuild command to rebuild the two invalid indexes found above, see operation below,

4. Query again with the SQL above, and found that the invalid index does not exist, see the figure below,

5. The next day, EMCC 12C monitoring of this database found that the execution status of the two SQL is normal and completed. See the following figure to view the results.

Then check the alert log file again, in the time period of the day, found that there is no ORA-20000 fault code.

If you think this article is helpful to you, welcome to pay attention to Weixin Official Accounts: DBA study notes of guestart. Your support is my greatest encouragement!