Wednesday, May 9, 2012

ORA-20000: no statistics are imported while Refershing schema statistics from production to UAT in 11gR2


We have to refresh schema statistics from PROD to UAT , but while importing to UAT i was getting the below error (ORA-20000: no statistics are imported)

The schema name in prod is 'OBLLIVE' having tablespace 'prod'
The schema name in UAT is 'UATLIVE' having tablespace 'livetest'

IN PROD database server
=======================
SQL> show user
USER is "OBLLIVE"
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE (OWNNAME=>'OBLLIVE',STATTAB=>'STATS');

PL/SQL procedure successfully completed.
SQL> select table_name from user_tables where table_name like '%STATS%';

TABLE_NAME
------------------------------
STATS

SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME =>'OBLLIVE',STATTAB =>'STATS');

PL/SQL procedure successfully completed.


bash-4.1$ expdp tables=OBLLIVE.STATS directory=EXPDP_DIR dumpfile=STATS_OBLLIVE_07012012.dmp logfile=STATS_OBLLIVE_07012012.log

successful

IN UAT database server
========================

bash-4.1$ impdp REMAP_SCHEMA=obllive:uatlive REMAP_TABLESPACE=prod:livetest DIRECTORY=impdp_dir DUMPFILE=STATS_OBLLIVE_07012012.dmp 
LOGFILE=STATS_OBLLIVE_07012012.log

successful

SQL> select table_name from user_tables where table_name like '%STATS%';

TABLE_NAME
------------------------------
STATS

SQL> show user
USER is "UATLIVE"

SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (ownname =>'UATLIVE',stattab => 'STATS');
BEGIN DBMS_STATS.IMPORT_SCHEMA_STATS (ownname =>'UATLIVE',stattab => 'STATS'); END;

*
ERROR at line 1:
ORA-20000: no statistics are imported
ORA-06512: at "SYS.DBMS_STATS", line 10603
ORA-06512: at line 1

Solution/Workaround for this Error is as below

ACTION PLAN
============

Before proceeding with the import of statistics , Update the column C5 of of the stats table as follow :

1.
connect UATLIVE

update stats set c5 = 'UATLIVE' where c5='OBLLIVE';

SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (ownname =>'UATLIVE',stattab => 'STATS');

1 comment:

  1. sahu sir it didn't work here even after updating C5
    SQL> exec dbms_stats.IMPORT_DATABASE_STATS('LIVE_SCHEMA_KCI_STATS','KCI_CIT');
    BEGIN dbms_stats.IMPORT_DATABASE_STATS('LIVE_SCHEMA_KCI_STATS','KCI_CIT'); END;

    *
    ERROR at line 1:
    ORA-20000: no statistics are imported
    ORA-06512: at "SYS.DBMS_STATS", line 12825
    ORA-06512: at line 1

    ReplyDelete