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

This article introduces the relevant knowledge of "how to solve database ora-600 exceptions". 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!

First, problem description

According to the user's feedback, an exception of ora-600 occurs when a section of SQL is executed.

Create or replace view grp_cust_ucs_v as

Select distinct

A.CUST_ID

A.CUST_NAME

B.service_id

B.list_system_id

A.CUST_ADDR

Decode (a. Custis TYPEP) '01century', '02'' small and medium', '03'' public', '04'' campus', 'unknown') CUST_TYPE

A.CARD_TYPE

A.CRAD_NO

A.EPARCHY_CODE

A.CITY_CODE

A.SCORE_VALUE

A.CREDIT_CLASS

A.BASIC_CREDIT_VALUE

A.CREDIT_VALUE

A.DEVELOP_DEPART_ID

F_develop_staff_tonumber (a.CUST_ID) DEVELOP_STAFF_ID

A.IN_DEPART_ID

A.IN_STAFF_ID

A.IN_DATE

A.REMOVE_FLAG

A.REMOVE_DATE

A.REMOVE_STAFF_ID

A.REMOVE_CHANGE

A.UPDATE_TIME

A.UPDATE_STAFF_ID

A.UPDATE_DEPART_ID

B.ACTIVE_TIME

B.active_man

Case

When b.cust_id is null then '10'

When b.cust_id is not null then '11'

End as CUST_STATUS

B.if_valid

B.sync_status

B.inactive_time

B.inactive_man

A.new_cust_type

(select b2.SYSTEM_USER_NAME |'('| b2.SYSTEM_USER_CODE | |') 'from grp_distribute_cust_manager b1 direction vs. systematical user b2

Where b1.cust_id=a.cust_id and b1.status=10 and b1.manager_id=b2.SYSTEM_USER_ID) accept_man

(select b2.SYSTEM_USER_NAME |'('| b2.SYSTEM_USER_CODE | |') 'from grp_distribute_cust_manager b1 direction vs. systematical user b2

Where b1.cust_id=a.cust_id and b1.status=10 and b1.send_user=b2.SYSTEM_USER_ID) send_man

(select to_char (b1. Senddated1) from grp_distribute_cust_manager b1

Where b1.cust_id=a.cust_id and b1.status=10) send_date

(select b1.manager_id from grp_distribute_cust_manager b1

Where b1.cust_id=a.cust_id and b1.status=10) accept_man_id

From GRP_BMS_CUST_INFO a,GRP_CUST_UCS_T b

Where a.cust_id = b.cust_id (+)

And b.if_valid (+) = '10'

II. Problem solving

This is a bug of oracle, which has been resolved in version 10.2.0.5.4 and later. The above ora-600 [kcblasm_1], [], [] problems may occur when large tables are associated with hash join or execute hash group by.

You can choose to avoid errors caused by the corresponding sql by avoiding the hash join execution plan or the hash group by execution plan. We can execute this sql statement before:

Alter session set "_ hash_group_enabled" = false disables hash join connection, or uses hint no_use_hash in the sql statement.

Alter session set "_ gby_hash_aggregation_enabled" = falase disables hash group by execution path

The quickest way given by Oralce is to directly type patch to version 10.2.0.5.4 or above, or you can choose to call patch 7612454 on linux and unix platforms to solve this problem. Under windows, you need to download patch 3 or above to solve this problem.

Here, we choose to use hint no_use_hash in the sql statement, and the problem is solved.

This is the end of the content of "how to solve database ora-600 exceptions". 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.