0

I'm trying to access an Oracle 12c EM and when I try to login it shows me a load error box with the errors that I expose below, which could be happening? Is there a possibility to reconfigure EM Express? If possible, I would like to know where the logs are, thanks.

ORA-00979: not a GROUP BY expression
ORA-06512: at "SYS.PRVT_AWR_VIEWER", line 9171
ORA-06512: at "SYS.PRVT_AWR_VIEWER", line 13196
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 876
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 144
ORA-06512: at "SYS.WRI$_REPT_DBHOME", line 91
ORA-06512: at "SYS.DBMS_REPORT", line 898
ORA-06512: at line 1
1

This is a shortened, but public version of an MOS (My Oracle Support) note:

WHEN LOGIN VIA EM EXPRESS, ORA-979 IS RAISED (Doc ID 2390782.1)

Here we can find this information:

CHANGES

setting "_fix_control"='11657903:ON'

Which gives away the solution, without accessing the private content. Someone enabled the patch for bug 11657903, and that change caused this error. Try disabling the applied patch for bug 11657903:

alter session set "_fix_control"='11657903:OFF';

But this will not have any effect, as logging in to EM creates a new session. Instead of it, you can disable it at system level:

alter system set "_fix_control"='11657903:OFF';

This solves your problem, but that patch was applied to fix other issues, it is not nice to disable it completely. If you want to set a parameter dynamically, on LOGON, only for specific sessions, you can do that with a logon trigger. Here is an example of such a trigger that shows you how to set _fix_control for a specific user and patch, just change it to your needs.

http://karandba.blogspot.com/2015/03/tracing-session-with-client-identifier.html

CREATE OR REPLACE TRIGGER set_trace_logon_test1
AFTER LOGON ON SCOTT.SCHEMA
WHEN (lower(sys_context('USERENV', 'CLIENT_IDENTIFIER')) like '%hr%')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12 : 10053 trace name context forever, level 1''';
EXECUTE IMMEDIATE 'alter session set "_fix_control"="6765823:off"';
insert into sys.trigger_test values (sys_context('USERENV', 'CLIENT_IDENTIFIER'),sysdate,'LOGON');
commit;
END set_trace_logon_test1;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.