Another experience to share. Transferring statistics from a schema to schema(same or different) on another database.
Source Database on lnx133
INSTANCE_NAME HOST_NAME
---------------- -------------------------
maze lnx133
Login to the source database as the user for which we want to collect statistics & transport it to another database. In our example it is user "scott".
SQL> conn scott/xxxxx;
Connected.
SQL> sho user
USER is "SCOTT"
-- Create a table to store statistics by executing following procedure:
SQL> exec dbms_stats.create_stat_table('SCOTT', 'STATS');
PL/SQL procedure successfully completed.
In above statement --> SCOTT refers to a schema & STATS is the tablename.
Now we are ready to collect statistics in the above created table 'STATS', we can collect statistics as follows:
SQL> begin
dbms_stats.export_schema_stats(ownname=>'SCOTT', stattab=>'STATS');
end;
/
PL/SQL procedure successfully completed.
--Notice the table is populated.
SQL> select count(*) from stats;
COUNT(*)
----------
182
Statistics are now stored in table & we can go ahead and do a export of this table which will allow us to transport it between different databases.
Use expdp utitlity to take a backup of SCOTT.STATS table
[oracle@lnx133 ~]$ expdp system/xxxx directory=TESTDIR dumpfile=stats.dmp logfile=stats.log tables=scott.stats
Export: Release 11.2.0.2.0 - Production on Wed May 1 17:50:49 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=TESTDIR dumpfile=stats.dmp logfile=stats.log tables=scott.stats
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
. . exported "SCOTT"."STATS" 29.5 KB 182 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/backup/stats.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 17:51:28
Export dump being ready let's transfer the dumpfile from source db server(lnx133) to destination db server (lnx144).
[oracle@lnx133 backup]$ scp stats.dmp lnx144:/u01/app/oracle/backup/.
oracle@lnx144's password:
stats.dmp 100% 148KB 148.0KB/s 00:00
[oracle@lnx133 backup]$
Now on the destination database on lnx144 server, before we import the table using the dump file, lets delete the schema stats.
SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- -------------------------
demo lnx144
SQL> conn scott
Enter password:
Connected.
SQL> sho user
USER is "SCOTT"
SQL> EXEC DBMS_STATS.delete_schema_stats('SCOTT');
PL/SQL procedure successfully completed.
Importing stats table in scott schema
oracle@lnx144 backup]$ impdp system/xxxx directory=testdir dumpfile=stats.dmp logfile=impstats.log tables=scott.stats
Import: Release 11.2.0.2.0 - Production on Wed May 1 18:09:14 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=testdir dump file=stats.dmp logfile=impstats.log tables=scott.stats
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."STATS" 29.5 KB 182 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 18:09:26
Here we have 2 scenarios
A) If importing into a schema with same name then simply follow the next step.
SQL> begin
dbms_stats.import_schema_stats(OWNNAME=>'SCOTT', STATTAB=>'STATS');
END;
/
PL/SQL procedure successfully completed.
B) If importing into a schema that has a different name then, this is done as follows
Login as the user where the stats table was imported & issue the following sql
SQL> update stats set c5='NEW_SCHEMA_NAME';
SQL>commit;
Then import statistics.
SQL> begin
dbms_stats.import_schema_stats(OWNNAME=>'NEW_SCHEMA_NAME', STATTAB=>'STATS');
END;
/
PL/SQL procedure successfully completed.
I hope this post helps DBA folks to take care of one more thing.... .
