Here we will see how to copy schema statistics from one schema
to another. Basically there are two methods for doing this:
- using
DBMS_STATS or
- using
Data Pump
This is especially useful when doing large
data transfers between either schemas or databases and statistics are needed
fast. Remember that data density, row chaining or index cluster factor changed
due to the data import and index rebuilds.
Method 1: Using DBMS_STATS
Extract the statistics
First we need to login as schema owner which
from which we want to extract the statistics.
In the follwing example the
schema owner and schema is is “SCOTT”. Logged in as scott we create a table
holding the statistic information:
exec dbms_stats.create_stat_table('SCOTT','STATISTICS');
The arguments required are:
<SCHEMANAME>, <TABLENAME>.
To extract the statistics run:
begin
dbms_stats.export_schema_stats(ownname =>
'SCOTT',stattab => 'STATISTICS');
end;
/
Transfer the statistics
Now all statistics from that particular
schema are stored in the table named “STATISTICS”. The table must be transfered
to the destination schema (in the same or another database). This can be done
for instance with exp/imp or via a database link.
For exporting the statistics with export/import:
exp system/manager file=stats_table.dmp
log=stats_table.log tables=SCOTT.STATISTICS
Importing is done with:
imp system/manager file=stats_table.dmp
log=stats_table_imp.log
fromuser=<source user name>
touser=<destination user name>
Import the statistics
Import into another database but with the
same schema name
If the schema name in the destination
database is identical to the schema name in the source database (“SCOTT” in
source and “SCOTT” in destination) the stats can be imported with:
begin
exec dbms_stats.import_schema_stats(ownname
=> 'SCOTT',stattab => 'STATISTICS');
end;
/
Import into another or the same database but
with a different schema name
If the name of the destination schema is
changed you have to adjust your statistic table before importing the stats. In
the following example we import the stats into a schema called “TEST”.
In the stats table the column named “C5″
contains the name of the schema the statistics were exported from and will be
imported to. If you want to import the stats into another schema you have to
modify this column first:
update statistics set c5='TEST';
and afterwards import the statistics (loggen
in as user TEST or SYS):
begin
exec dbms_stats.import_schema_stats(ownname
=> 'SCOTT',stattab => 'STATISTICS');
end;
/
Method 2: Using data pump
Beside using DBMS_STATS you can use data pump
to transfer table and index statistics from one database to another.
When doint a normal export/import table and
index statistics are transfered along with the data. If you want to transfer
only the statistics you can use the following call:
impdp system/manager
dumpfile=<directory>:<file> logfile=<directory>:<logfile
name> remap_schema=<source>:<destination>
remap_tablespace=<source>:<destination> schemas=<name of source schema(s)>
contents=STATISTICS