Oracle 12c Enterprise Result Cache Disabled – BYPASS Status

cacheoracleoracle-12c

I have two database instances, development and production that are fairly identical. On production, result cache is working fine, in development it is not. The value for the database parameter result_cache_max_size was set to 0 so I reset it to remove the value, bounced the database, and when the database was back up, the value was still set to 0 so I tried starting with a PFILE with no value for result_cache_max_size and same problem. Then I tried setting it to 20M (value from production) and bounced, but same results.
I then invoked

SELECT dbms_result_cache.status() FROM dual;

which returns BYPASS.

The parameters result_cache_max_result and result_cache_mode are set to 5 and MANUAL on both databases.

I am using Enterprise Edition on both instances (so question Why is the result cache always disabled in Oracle 11g does not apply to me):

Development:

SYS@extdev02> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
... 

Production:

extuat01> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
... 

Edit: Tried bypass proc per @BalazsPapp answer but no luck. I think I tried it already, but without the second parm, which would have defaulted to false per the documentation. Today's try, including a flush proc just for fun:

SYS@extdev02> select dbms_result_cache.status() FROM dual; 

DBMS_RESULT_CACHE.STATUS()
---------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SYS@extdev02> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS

Best Answer

which returns BYPASS.

Then enable it:

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

SQL> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL>

DBMS_RESULT_CACHE.BYPASS