Hi guys,
It's Oracle 12c R2 on Linux x86-64bit.
In a testing session, I've got a child cursor which doesn't go away even after flushing the shared pool.
If that's normal, could you please shed a light on this case? To best of my knowledge, flushing the shared pool remove all the cursors from the shared pool.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> R
1 SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, IS_RESOLVED_ADAPTIVE_PLAN IRAP, IS_REOPTIMIZABLE IR
2 FROM V$SQL
3 WHERE SQL_ID='&v_sql_id'
4* ORDER BY SQL_ID, CHILD_NUMBER
old 3: WHERE SQL_ID='&v_sql_id'
new 3: WHERE SQL_ID='2zs68wnqp11xj'
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE IRAP IR
------------- ------------ --------------- ---- --
2zs68wnqp11xj 1 3188328183 N N