Yes i have done below
1. alter system Set "_optimizer_order_by_elimination_enabled"=false;
2. alter session set "_complex_view_merging"=false;
3. alter system set "_optimizer_push_pred_cost_based"=false;
But it doesnot help and about tracing the session what i get...
Above errors ORA-00600: internal error is resolved after i submit SR. They suggest to change parameter “_optimizer_join_factorization” to false.
But i am still facing with another error with view on oracle 11gr2
ORA-03113: end-of-file on communication channel...
Below is the work around i done for "ORA-03113: end-of-file on communication channel"
Join Condition for view is
If i run by just removing any one of above join condition is works fine but since i dont know business logic so i come up with solution below
1. create table t111 as select * from t1;
2. rename table t1 to abc
3. rename t111 to t1
Now it is starts working.
I don't know what had happen but i find workaround not solution.
If somebody suggest the solution and reason why this happening highly appreciated.
Please check below notes which could be helpful to understand your issue:
Connections To Database Terminate With ORA-3113 end-of-file on communication channel [ID 1104673.1]
DIAGNOSING ORA-3113 ERRORS [ID 1020463.6]
ORA-03113 on Unix - What Information to Collect [ID 17613.1]
OERR: ORA 3113 "end-of-file on communication channel" [ID 19790.1]
Thanks for this suggestion ... setting the optimizer to false did help me. The problem I was facing was with my Views... every time I executed a view, I would experience the ORA-00600 error. I set the optimizer to false and restarted my database server. I experience this on Oracle 11.2 on Windows.
Queries may raise ora-600 [kkqtSetOp: 0] or ora-600 [kkqtSetOp.1]
or the query may fail to be fully optimized with table expansion, star transformation and join factorization all together when the query is eligible for all 3 transformations.