Flush Shared Pool for a single query

 DECLARE
  p_sql_id VARCHAR2(13) := '&sql_id'; -- Input SQL_ID parameter
BEGIN
  DBMS_OUTPUT.PUT_LINE('*** before flush ***');
  
  DBMS_OUTPUT.PUT_LINE('From gv$sqlarea:');
  DBMS_OUTPUT.PUT_LINE('INST_ID | LOADED_VERSIONS | INVALIDATIONS | ADDRESS | HASH_VALUE');
  FOR r IN (SELECT inst_id, loaded_versions, invalidations, address, hash_value
            FROM gv$sqlarea WHERE sql_id = p_sql_id ORDER BY 1) LOOP
    DBMS_OUTPUT.PUT_LINE(r.inst_id || ' | ' || r.loaded_versions || ' | ' || r.invalidations || ' | ' || r.address || ' | ' || r.hash_value);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('From gv$sql:');
  DBMS_OUTPUT.PUT_LINE('INST_ID | CHILD_NUMBER | PLAN_HASH_VALUE | EXECUTIONS | IS_SHAREABLE');
  FOR r IN (SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
            FROM gv$sql WHERE sql_id = p_sql_id ORDER BY 1, 2) LOOP
    DBMS_OUTPUT.PUT_LINE(r.inst_id || ' | ' || r.child_number || ' | ' || r.plan_hash_value || ' | ' || r.executions || ' | ' || r.is_shareable);
  END LOOP;
  
  FOR i IN (SELECT address, hash_value FROM gv$sqlarea WHERE sql_id = p_sql_id) LOOP
    SYS.DBMS_SHARED_POOL.PURGE(i.address || ',' || i.hash_value, 'C');
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('*** after flush ***');
  
  DBMS_OUTPUT.PUT_LINE('From gv$sqlarea:');
  DBMS_OUTPUT.PUT_LINE('INST_ID | LOADED_VERSIONS | INVALIDATIONS | ADDRESS | HASH_VALUE');
  FOR r IN (SELECT inst_id, loaded_versions, invalidations, address, hash_value
            FROM gv$sqlarea WHERE sql_id = p_sql_id ORDER BY 1) LOOP
    DBMS_OUTPUT.PUT_LINE(r.inst_id || ' | ' || r.loaded_versions || ' | ' || r.invalidations || ' | ' || r.address || ' | ' || r.hash_value);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('From gv$sql:');
  DBMS_OUTPUT.PUT_LINE('INST_ID | CHILD_NUMBER | PLAN_HASH_VALUE | EXECUTIONS | IS_SHAREABLE');
  FOR r IN (SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
            FROM gv$sql WHERE sql_id = p_sql_id ORDER BY 1, 2) LOOP
    DBMS_OUTPUT.PUT_LINE(r.inst_id || ' | ' || r.child_number || ' | ' || r.plan_hash_value || ' | ' || r.executions || ' | ' || r.is_shareable);
  END LOOP;
END;
/