How to flush a single SQL from shared pool

Use  DBMS_SHARED_POOL.purge
exec  DBMS_SHARED_POOL.purge('ADRESS,HASH_VALUE','C',1);
Example :
 select address,hash_value, executions, loads, version_count,
 invalidations, parse_calls
 from v$sqlarea
 where sql_text like 'UPDATE OTPL_BASVURU_KULLANICI SET ORDER_ID%';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
070000012EC6FB98 3630826165 1 1 1 0 1
alter session set events '5614566 trace name context forever';    --for  10.2.0.4 or 10.2.0.5
 EXEC SYS.DBMS_SHARED_POOL.purge('070000012EC6FB98,3630826165','C',1);


or another   flush  sql 

alter session set events '5614566 trace name context forever';


select  ' EXEC SYS.DBMS_SHARED_POOL.purge('''||address||','||to_char(hash_value)||''',''C'',1);'
from  v$sqlarea where hash_value=&hash_value;


Note1:Be careful usage  of   'ADRESS,HASH_VALUE'      not  'ADRESS','HASH_VALUE'

Note2:The create statement for this package can be found in the $ORACLE_HOME/rdbms/admin/dbmspool.sql script

Note3:DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available through the fix for Bug 5614566. However, the fix is event protected. You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect.


event="5614566 trace name context forever"   #in init.ora for 10.2.0.4 or 10.2.0.5
or 
alter session set events '5614566 trace name context forever';

How to compile Invalid Object?

Operations such as upgrades, patches and DDL changes can invalidate schema objects.Here are  some  methods  to compile   objects .But I prefer  UTL_RECOMP (from sql  or PL/SQL ) or  $ORACLE_HOME/rdbms/admin/utlrp.sql (From Operting system)

1.  we  can  compile it manually like 
    alter  package serdar.xxx compile  body  ;
2. Manuel by the help  of a  script:we can  spool  the output  and  run it.Note  that  this script is not written for all types  like java classes
   
   Set heading off;
   set feedback off;
   set echo off;
   Set lines 999;
   spool compile.sql
   
   select  'alter '||
   decode(object_type,'SYNONYM',decode(owner,'PUBLIC','PUBLIC SYNONYM '||object_name,
   'SYNONYM '||OWNER||'.'||OBJECT_NAME)||' compile;',
   decode(OBJECT_TYPE ,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)||
   ' '||owner||'.'||object_name||' compile '||
   decode(OBJECT_TYPE ,'PACKAGE BODY','BODY;',' ;'))
   from dba_objects where status<>'VALID'
   order by owner,OBJECT_NAME;
   spool off
   @compile.sql
   
   
   
3. DBMS_DDL.ALTER_COMPILE: The same with "alter   procudere serdar.test compile"
   
   exec dbms_ddl.alter_compile ('PROCEDURE','SERDAR','TEST');
4. DBMS_UTILITY.compile_schema:The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema
   
   EXEC DBMS_UTILITY.compile_schema(schema => 'SERDAR');
5. UTL_RECOMP :it will  recompile all invalids.
   
   --Compile  all invalids  in the database
   exec  SYS.UTL_RECOMP.RECOMP_SERIAL ();
   --Compile schema  SERDAR
   exec SYS.UTL_RECOMP.RECOMP_SERIAL ('SERDAR');   
   
   --Compile all invalids in the database parallel (If we  have eneogh  CPU )
   
   exec  SYS.UTL_RECOMP.recomp_parallel(4);
6. UTLRP.SQL  : From  the  operating  system as sysdba.Note that this is an example  for UNIX systems
   
   SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
   
    

How to Recreate the OraInventory on UNIX Systems

How can I recreate the OraInventory  if it gets  removed

1. Locate the oraInst.loc file, which may be in different locations, depending on your system
   /var/opt/oracle/oraInst.loc file
   or
   /etc/oraInst.loc
2. Modify the file oraInst.loc file
   cp /etc/oraInst.loc /etc/oraInst.loc.bak     #e.x for AIX run with rot
   
   mkdir /data1/oracle2/oraInventory          #path is examp.Change it .run with oracle owner user
3. Change oraInst.loc
   inventory_loc=/data1/oracle2/oraInventory
   
   inst_group=oinstall #it can be also dba
4. Change the permissions
   chmod 644 /etc/oraInst.loc
5. For consistency, copy the file to Oracle home directory, (using your directory location):
   
   cp $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.bak
   
   cp /etc/oraInst.loc                  $ORACLE_HOME/oraInst.loc
6. Run Oracle Universal Installer from your Oracle home as below:
   
   cd $ORACLE_HOME/oui/bin
   
   ./runInstaller -silent -attachHome ORACLE_HOME="/data1/oracle2/orahome10gr2" ORACLE_HOME_NAME="PARTEST"
   
   Note: The -attachHome is only officially supported in 10.2 and higher. But, we found it works on our testing with 10.1.2
7. Check the inventory output is correct for your Oracle home:
   $ORACLE_HOME/OPatch/opatch lsinventory -detail
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

How to grant on v$ views

When we  need to  grant on v$views  to a  user  I faced  with  ORA-02030 error

SQL> grant select on v$sqlarea to serdar;

grant select on v$sqlarea to serdar
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The  problem  is caused   because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym

Try this 

SQL> grant select  on v_$sqlarea to serdar;
Grant succeeded.