This was surprisingly more difficult than I expected. We know that we can export stats
from the dictionary to a table, and from the table to a file, and that file can be copied
and imported to another database for the stats to be imported. Easy right?
$ sqlplus /as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec1908:55:392017Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database12c Enterprise Edition Release 12.1.0.2.0-64bit Production
SQL>exec DBMS_STATS.CREATE_STAT_TABLE('SCHEMA','MYSTATS','USERS');
PL/SQLprocedure successfully completed.
SQL>@export_stats
BEGINDBMS_STATS.EXPORT_TABLE_STATS ( 'SCHEMA', 'MYTABLE', NULL, stattab =>'MYSTATS');
END;
*ERROR at line 1:
ORA-20002: Versionofstatisticstable"SCHEMA"."MYSTATS"is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at"SYS.DBMS_STATS", line 18000ORA-06512: at line 1
Hang on, I just created it! How can it be too old? Still, I will upgrade it if it insists.
SQL>exec dbms_stats.upgrade_stat_table( 'SCHEMA','MYSTATS')
PL/SQLprocedure successfully completed.
SQL>@export_stats
BEGINDBMS_STATS.EXPORT_TABLE_STATS ( 'SCHEMA', 'MYTABLE', NULL, stattab =>'MYSTATS');
END;
*ERROR at line 1:
ORA-20002: Versionofstatisticstable"SCHEMA"."MYSTATS"is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at"SYS.DBMS_STATS", line 18000ORA-06512: at line 1
Desperate times call for desparate measures! Fire up Oracle support and a search turns up
document ID 2004828.1. This says I am encountering bug 20822264 or 19280897. The solution
is to create the table using NLS_LENGTH_SEMANTICS of BYTE rather than CHAR which our
database has by default. I gave it a try: