Wednesday, May 1, 2013

Exporting stats of a Schema from one database to another

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.... .

No comments:

Post a Comment