to query any information
SQL> select * from rman.titi;
NAME
------------------------------
marta
jimena
nancy
To check information about SQL share area
SQL> select ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT from V$SQLAREA where upper(SQL_TEXT) like '%FROM RMAN.TITI%';
ADDRESS HASH_VALUE SQL_ID SQL_TEXT
---------------- ---------- ------------- ------------------------------------------------------------
00000000623AEE18 934823621 16y10hnvvhjq5 select ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT from V$SQLAREA
where upper(SQL_TEXT) like '%FROM RMAN.TITI%'
00000000672FC140 3464077173 7pwn4bv77m7vp select * from rman.titi
To purge
SQL> exec DBMS_SHARED_POOL.PURGE ('00000000672FC140,3464077173','C');
PL/SQL procedure successfully completed.
To check information about SQL share area after
SQL> select ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT from V$SQLAREA where upper(SQL_TEXT) like '%FROM RMAN.TITI%';
ADDRESS HASH_VALUE SQL_ID SQL_TEXT
---------------- ---------- ------------- ------------------------------------------------------------
00000000623AEE18 934823621 16y10hnvvhjq5 select ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT from V$SQLAREA
where upper(SQL_TEXT) like '%FROM RMAN.TITI%'
SQL> select * from rman.titi;
NAME
------------------------------
marta
jimena
nancy
SQL> select ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT from V$SQLAREA where upper(SQL_TEXT) like '%FROM RMAN.TITI%';
ADDRESS HASH_VALUE SQL_ID SQL_TEXT
---------------- ---------- ------------- ------------------------------------------------------------
00000000623AEE18 934823621 16y10hnvvhjq5 select ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXT from V$SQLAREA
where upper(SQL_TEXT) like '%FROM RMAN.TITI%'
00000000672FC140 3464077173 7pwn4bv77m7vp select * from rman.titi
