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?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 19 08:55:39 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('SCHEMA','MYSTATS','USERS');
PL/SQL procedure successfully completed.
SQL> @export_stats
BEGIN DBMS_STATS.EXPORT_TABLE_STATS ( 'SCHEMA', 'MYTABLE', NULL, stattab => 'MYSTATS'); END;
*
ERROR at line 1:
ORA-20002: Version of statistics table "SCHEMA"."MYSTATS" is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 18000
ORA-06512: at line 1
|
Hang on, I just created it! How can it be too old? Still, I will upgrade it if it insists.
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> exec dbms_stats.upgrade_stat_table( 'SCHEMA','MYSTATS')
PL/SQL procedure successfully completed.
SQL> @export_stats
BEGIN DBMS_STATS.EXPORT_TABLE_STATS ( 'SCHEMA', 'MYTABLE', NULL, stattab => 'MYSTATS'); END;
*
ERROR at line 1:
ORA-20002: Version of statistics table "SCHEMA"."MYSTATS" is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 18000
ORA-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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> drop table SCHEMA.MYSTATS
2 /
Table dropped.
SQL> alter session set nls_length_semantics=byte;
Session altered.
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('SCHEMA','MYSTATS','USERS');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.EXPORT_TABLE_STATS ( 'SCHEMA', 'MYTABLE', NULL, stattab => 'MYSTATS')
PL/SQL procedure successfully completed.
|
Phew!
For completeness, here is what I did to export the table. I create the directory to use:
1
2
|
SQL> Create or replace directory exp_dir as '/path/to/dir';
SQL> grant read, write on directory exp_dir to schema;
|
Then I export
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ expdp SCHEMA directory=EXP_DIR tables=MYSTATS dumpfile=MYSTATS
Export: Release 12.1.0.2.0 - Production on Tue Dec 19 09:19:53 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Starting "SCHEMA"."SYS_EXPORT_TABLE_01": SCHEMA/******** directory=EXP_DIR tables=MYSTATS dumpfile=MYSTATS
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCHEMA"."MYSTATS" 19.12 KB 12 rows
Master table "SCHEMA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCHEMA.SYS_EXPORT_TABLE_01 is:
/path/to/dir/MYSTATS.dmp
Job "SCHEMA"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 19 09:27:04 2017 elapsed 0 00:06:58
|