Shulou(Shulou.com)05/31 Report--

Editor to share with you how to do ORA-01502 errors in oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Part one:

Received notification from developers and business staff that a landing page was not available and reported an error:

2017-10-24 15 5914 02721 [com.aspire.common.dao.OamUserDAO]-- 214:select error

Java.sql.SQLException: ORA-01502 state

This error is caused by index failure, and after the index is rebuilt, the problem is solved.

To figure out why the index fails and how to solve it, let's do a test:

First, let's create a normal test table (non-partitioned table):

SQL > create table t (a number)

Table created.

SQL > select tablespace_name from user_segments where segment_name='T'

TABLESPACE_NAME

-

DATA_DYNAMIC

SQL >

Then, we create a normal index

SQL > create index idxt on t (a)

Index created.

SQL > insert into t values (10)

1 row created.

SQL > set linesize 200

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDXT NORMAL DATA_DYNAMIC TABLE VALID

SQL >

The simulated index is invalid:

SQL > alter table t move tablespace tools

2 /

Table altered.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDXT NORMAL DATA_DYNAMIC TABLE UNUSABLE

SQL >

We see that when you use a command like alter table xxxxxx move tablespace xxxxxxx, the index becomes invalid.

Of course, as a test, you can also use the alter index idxt unusable; command directly to invalidate the index, for example:

SQL > alter index idxt unusable

Index altered.

SQL >

In this case, let's insert data into the table to see what happens:

SQL > insert into t values (11)

Insert into t values (11)

*

ERROR at line 1:

ORA-01502: index 'MISC.IDXT' or partition of such index is in unusable state

SQL >

We see that the common "ORA-01502: index 'XXXXXXXX' or partition of such index is in unusable state" error occurs at this point.

Check the status of the index and notice that the index is already "UNUSABLE".

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDXT NORMAL DATA_DYNAMIC TABLE UNUSABLE

SQL >

For different indexes (non-unique indexes) in a regular table, we have two ways to solve this problem.

Method 1: set skip_unusable_indexes=true

SQL > alter session set skip_unusable_indexes=true

Session altered.

SQL > insert into t values (11)

1 row created.

SQL > commit

Commit complete.

SQL > select * from t

A

-

one

two

three

four

five

ten

eleven

7 rows selected.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDXT NORMAL DATA_DYNAMIC TABLE UNUSABLE

SQL >

Now we can see that the state of the index is still "UNUSABLE", but by setting "alter session set skip_unusable_indexes=true;"

We can already access the table, but note that in this case, the index is not available, which means that the optimizer does not consider this when considering whether to use the index.

Method 2: solve this problem once and for all through common

First, set "skip_unusable_indexes=false", that is, do not skip the invalid index

SQL > alter session set skip_unusable_indexes=false

Session altered.

SQL >

And then rebuild the invalid index

SQL > alter index idxt rebuild

Index altered.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDXT NORMAL DATA_DYNAMIC TABLE VALID

SQL >

After we see that the index is rebuilt, the state of the index is normal.

Now insert the data and see if it is normal:

SQL > insert into t values (12)

1 row created.

SQL > commit

Commit complete.

SQL >

It seems that rebuilding the index is the thorough way to solve this kind of problem.

The second part:

SQL > create table t (a number)

Table created.

Now, let's set up a unique index to see:

SQL > create unique index idx_t on t (a)

Index created.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T'

No rows selected

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDX_T NORMAL DATA_DYNAMIC TABLE VALID

SQL > insert into t values (1)

1 row created.

SQL > commit

Commit complete.

Manually modify the index to the unusable state (simulate an index failure):

SQL > alter index idx_t unusable

Index altered.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDX_T NORMAL DATA_DYNAMIC TABLE UNUSABLE

We see that it is no longer possible to insert data into the table normally:

SQL > insert into t values (2)

Insert into t values (2)

*

ERROR at line 1:

ORA-01502: index 'MISC.IDX_T' or partition of such index is in unusable state

First, we solve the problem by rebuilding the index (rebuild index):

SQL > alter index idx_t rebuild

Index altered.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDX_T NORMAL DATA_DYNAMIC TABLE VALID

SQL > insert into t values (2)

1 row created.

SQL > commit

Commit complete.

SQL >

Now let's simulate index failure (unusable state) again:

SQL > alter index idx_t unusable

Index altered.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDX_T NORMAL DATA_DYNAMIC TABLE UNUSABLE

SQL > insert into t values (3)

Insert into t values (3)

*

ERROR at line 1:

ORA-01502: index 'MISC.IDX_T' or partition of such index is in unusable state

Then, see if you can solve the problem by setting the parameter skip_unusable_indexes=true:

SQL > alter session set skip_unusable_indexes=true

Session altered.

SQL > insert into t values (3)

Insert into t values (3)

*

ERROR at line 1:

ORA-01502: index 'MISC.IDX_T' or partition of such index is in unusable state

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDX_T NORMAL DATA_DYNAMIC TABLE UNUSABLE

SQL > alter index idx_t rebuild

Index altered.

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T'

INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS

IDX_T NORMAL DATA_DYNAMIC TABLE VALID

SQL > insert into t values (3)

1 row created.

SQL > commit

Commit complete.

SQL >

Obviously, for unique index, the problem can not be solved by simply setting parameters, and the problem of unique index failure can only be solved by rebuilding the index.

[summary]

1. After moving the tablespace of the table, the index based on the table will automatically fail. When UNUSABLE; accesses or operates the table at this time, it will report an ORA-01502 exception.

2. For ordinary indexes, the problem of ORA-01502 exception can be solved by skipping the index or rebuilding the index.

3. For a unique index, the problem of ORA-01502 exception can only be solved by rebuilding the index.

[reference]

-- create a normal index

SQL > alter index idxt unusable

-- set skip_unusable_indexes=true

SQL > alter session set skip_unusable_indexes=true

-- rebuild the index

SQL > alter index idxt rebuild

-create a unique index

SQL > create unique index idx_t on t (a)

Query index status:

SQL > select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT'

The above is all the contents of the article "what to do if there is an ORA-01502 error in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.