transfer stats from one schema to another

exporting from schema ORIG_USER and importing into MYUSER

1. create stattab DBMS_STATS.CREATE_STAT_TABLE(ownname=>'ORIG_USER',stattab=>'STATS_FOR_TEST');
2. create table with list of tables: create table test_tabs(object_name varchar2(200));
insert into test_tabs (select ...)
3. export statsselect 'exec dbms_stats.export_table_stats(ownname=>''ORIG_USER'',tabname=>'''|| object_name ||''',stattab=>''STATS_FOR_TEST'');' cmd from (select distinct object_name from test_tabs) t;
4. delete stats on target schemaselect 'exec dbms_stats.import_table_stats(ownname=>''MYUSER'',tabname=>'''|| object_name ||''',force=>true);' cmd from (select distinct object_name from test_tabs) t;
5. copy table with stats: create table myuser.STATS_FOR_TEST as (select * from orig_user.STATS_FOR_TEST);
6. modify the MYUSER.STATS_FOR_TEST:update stats_for_test set c5='MYUSER';
update stats_for_test set ch1 = ora_hash(ch1) where ch1 is not null; <== to mask histogram values
7. import statsselect 'exec dbms_stats.import_table_stats(ownname=>''MYUSER'',tabname=>'''|| object_name ||''',stattab=>''STATS_FOR_TEST'',force=>true);' cmd from (select distinct object_name from test_tabs) t;

STATS Locked after import

Metalink: ORA-38029 "Object Statistics Are Locked" - Possible Causes [ID 433240.1]

To prevent import (imp) from locking the table's statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

Gathering statistics

If database was created using DBCA, an automatic statistics gathering procedure should be already set up. It can be observed under the Scheduler jobs. You need to be logged in as "sys as sysdba" to see it.

To gather statistics manually you can use the ANALYZE SQL command, or DBMS_STATS package. You can also use EM, but that's just an interface for the DBMS_STATS.

When gathering statistics you can use the COMPUTE or ESTIMATE option. COMPUTE will gather fill stats when ESTIMATE will only take a representative sample of the rows in the table thus performing faster but not guaranteeing maximum correctness like COMPUTE does.

Gathering stats using the DBMS_STATS
Summary of DBMS_STATS subprograms

Most frequently needed are:
GATHER_DATABASE_STATS
GATHER_FIXED_OBJECTS_STATS
GATHER_INDEX_STATS
GATHER_SCHEMA_STATS
GATHER_TABLE_STATS

There is also a "DELETE_%" procedure corresponding to each "GATHER_%' procedure.

It is also possible to EXPORT and IMPORT statistics. You could have several different sets of statistics for different purposes.