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

This article introduces the relevant knowledge of "how to solve the ORA-00001 and ORA-00604 problems arising from the use of the database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

On: 12.1.0.2 version, RDBMS

Alert log shows:

ORA-00604: error occurred at recursive SQL level 1

ORA-00001: unique constraint (SYS.SMON_SCN_TIME_TIM_IDX) violated

Analysis:

The analysis table smon_scn_time is required. Verify the structure and consistency of tables and indexes, if there are inconsistencies between them. The index needs to be recreated.

For 11.2.0.3, 11.2.0.2 databases, it could be caused by Bug 13251192-SMON reports ORA-604/ORA-1 on SYS.SMON_SCN_TIME_TIM_IDX which is fixed in 12.1.0.1 and 11.2.0.4.

Solution:

For database 11.2.0.4 OR 12c, perform the analyze command below:

ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE

1. First get the creation statement of INDEX on the table SMON_SCN_TIME)

Set pages 0

Set long 2000000000

Select dbms_metadata.get_ddl ('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') from dual

Select dbms_metadata.get_ddl ('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') from dual

2. Then set the 12500 event at the system level to prevent SMON from updating the SMON_SCN_ time table

Alter system set events' 12500 trace name context level 10'

3. Drop index

Drop index SYS.SMON_SCN_TIME_SCN_IDX

Drop index SYS.SMON_SCN_TIME_TIM_IDX

4. Create the index SYS.SMON_SCN_TIME_SCN_IDX; and SYS.SMON_SCN_TIME_TIM_IDX; through the ddl obtained in step 1)

5. Close event 12500 so that SMON can update the SMON_SCN_TIME table

Alter system set events' 12500 trace name context off'

This is the end of the content of "how to solve the ORA-00001 and ORA-00604 problems in the use of the database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.