Steps for Importing and Exporting statistics from one database to other database.

Steps for Importing and Exporting statistics from one database to other database.
=================================================================================

1) exec dbms_stats.create_stat_table(ownname=>'OWNER_NAME',stattab=>'MY_STAT_TABLE');

   example: exec dbms_stats.create_stat_table(ownname=>'SYS'stattab=>'MY_STAT');

2) exec dbms_stats.export_schema_stats(ownname=>'ENTER_THE_OWNER_NAME',
   stattab=>'ENTER_THE_TABLE_NAME_CREATED_ABOVE',statown=>'ENTER_OWNER_OF_MY_STAT_TABLE');

   example: dbms_stats.export_schema_stats(ownname=>'SCOTT',stattab=>'MY_STAT',STATOWN=>'SYS')

3) export the table created with statistics data contained. (as a .dmp file)

4) Transfer the data to another database where statistics is to be imported.

5) import the (.dmp file) to the datbase.

6) exec dbms_stats.import_schema_stats(ownname=>'Enter_the_own_name_statistics_gathered_schema',
   stattab=>'ENTER_THE_TABLE_NAME_CREATED_ABOVE',statown=>'Enter_the_owner_name_of_the_table_containing_data_of_statistics',force=>true);

   example: exec dbms_stats.import_schema_stats(ownname=>'SCOTT',stattab=>'MY_STAT',statown=>'SYS',force=>true);

Purging a SQL plan from shared pool. "Purging a Single cursor from shared pool"

This Document is applicable for Oracle 11g Release 2.
======================================

Identify the problemetic sql_id.

Find the Address and hash_value of the sql_id

In my example sql_id is 'arxk2v5bw36mp'

col OWNER format a10
col NAMESPACE format a10
col TYPE format a10

select d.OWNER,d.NAMESPACE,d.TYPE,d.SHARABLE_MEM,d.KEPT,d.FULL_HASH_VALUE,
s.sql_id,s.address,s.hash_value
from v$db_object_cache d,v$sqlarea s
where d.name=s.SQL_TEXT
and s.sql_id='&sql_id'

OWNER      NAMESPACE  TYPE       SHARABLE_MEM KEP FULL_HASH_VALUE                  SQL_ID        ADDRESS          HASH_VALUE
---------- ---------- ---------- ------------ --- -------------------------------- ------------- ---------------- ----------
           SQL AREA   CURSOR            12616 NO  7541aaf7b87e0b3babf642d957c19a75 arxk2v5bw36mp C0000006B0B15310 1472305781
           SQL AREA   CURSOR                0 NO  7541aaf7b87e0b3babf642d957c19a75 arxk2v5bw36mp C0000006B0B15310 1472305781
           SQL AREA   CURSOR             4720 NO  7541aaf7b87e0b3babf642d957c19a75 arxk2v5bw36mp C0000006B0B15310 1472305781


Flush the SQL olan form library cache.
========================================
exec dbms_shared_pool.purge('C0000006B0B15310,1472305781','C',65);

select d.OWNER,d.NAMESPACE,d.TYPE,d.SHARABLE_MEM,d.KEPT,d.FULL_HASH_VALUE,
s.sql_id,s.address,s.hash_value
from v$db_object_cache d,v$sqlarea s
where d.name=s.SQL_TEXT
and s.sql_id='&sql_id'

Output.

SQL> exec dbms_shared_pool.purge('C0000006B0B15310,1472305781','C',65);

PL/SQL procedure successfully completed.

SQL> /
Enter value for sql_id: arxk2v5bw36mp
old   5: and s.sql_id='&sql_id'
new   5: and s.sql_id='arxk2v5bw36mp'

no rows selected

Note: In above example 65 means the whole object from shared pool will be purged.
      i.e plan of query will be purged from library cache. And C means object "sql statement" that is not    a package/procedure/function/trigger/sequence.