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');
============
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');
sahu sir it didn't work here even after updating C5
ReplyDeleteSQL> 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