Applying PSU results “patching Following executables are active”

while patching oracle 12c database on Linux OS environment, i faced the below error while applying the patch using Opatch utility (although the database is shutdown):


Verifying environment and performing prerequisite checks…

Prerequisite check “CheckActiveFilesAndExecutables” failed.

The details are:

Following executables are active :


UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.



find the process that is locking the file system using the fuser command

fuser -u /oracl/db11/product/

and then kill it using kill -9 command


set define off and ‘&’ in your SQL Code

have you ever faced a problem when you executed a SQL code sent to you by a developer and the below pop window appeared ??



to avoid this just add the following starting statement:

set define off

set define off

When you have ‘&’ inside SQL Script you need to specify “set define off” at the beginning of the code .  It will Turn off substitution variables.




Oracle 12cR2 In-Memory for JSON Columns

One of the new exciting features of Oracle 12cR2 is the ability to use In-Memory technology with JSON data.

Brief background:

  • JSON stands for JavaScript Object Notation, JSON is a language-independent data format.


  • In Oracle database JSON data are stored in columns with VARCHAR2, CLOB, or BLOB. These columns have “is json” check constraint.


Let us explore the setup:


The following prerequisites should be met before using In-Memory with JSON:

The compatibility of the database should be

SQL> show parameter compatible

The following parameter max_string_size must be set to “extended”

max string size standard

If its set to “standard” as normally it would be in your database, change it to “extended” by following the steps:

—— Shutdown the database

SQL> shutdown immediate;

—— Startup the database in upgrade mode

SQL> startup upgrade

—— change the parameter setting

SQL> alter system set MAX_STRING_SIZE =extended scope=spfile;

SQL> @?/rdbms/admin/utl32k.sql

—– restart the database in normal mode:

SQL> shutdown immediate;

SQL> startup;

SQL> @?/rdbms/admin/utlrp.sql

**** Remark:  The reason you need to change to extended data type, is that when you create a JSON column with VARCHAR2 you will have the ability to store documents up to  32,767 bytes.

Also ensure the following init.ora parameter files are set:




And of course ensure that you enabled your in-memory area (feature).


Now, we can proceed in creating a dummy table with JSON for testing:

CREATE TABLE dummy_json

(id          VARCHAR2 (32) NOT NULL PRIMARY KEY,


json_doc VARCHAR2 (23767)

CONSTRAINT ensure_json CHECK (json_doc IS JSON));


dummy table with JSON

Then I will insert few records, for example:







“color”: “black”,

“rgb”: [0,0,0],

“hex”: “#000000”}’);


Remark: While inserting records if you face this error:

ORA-02290: check constraint (HR.ENSURE_JSON) violated

This means that the JSON data was inserted in a wrong format….so review your JSON code.



dummy in memory

Checking it by querying user_tables :

select TABLE_NAME,INMEMORY from user_tables where INMEMORY=’ENABLED’;

user table query

—— If you are already upgrading the database from 12cR1 to 12cR2 and have many tables with JSON column and would like to utilize the in-memory capability, you need first to ensure you met the perquisites as stated above are met and then execute the following script


SQL> @?/admin/utlimcjson.sql

This script will upgrade all currently existing tables with JSON columns so it can be populated in the in-memory area whenever this is required.


Finally, Having tables with JSON column in in-memory will improve performance for the following especially when using SQL Functions: json_table,  json_query, and  json_value .