Skip to content

Copy table stats from one database to another database

March 31, 2014

MALU is DBA User who owns stats table STAT_TAB.
If user is not DBA than He should have => grant analyze any to malu;
else would receive error ORA-20000: TABLE "SCOTT"."EMP" does not exist or insufficient privileges
Step1) First create a stat table in the source database. The statistics table is created in MALU schema.
################################################################################################################

conn as MALU

exec dbms_stats.create_stat_table(ownname => 'MALU', stattab => 'STAT_TAB');

select * from tab where TNAME='STAT_TAB';

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STAT_TAB TABLE

Step2) Export the table statistics.
################################################################################################################
 col output for a4000
 select 'exec dbms_stats.export_table_stats(ownname=>'''||OWNER||''',tabname=>'''||TABLE_NAME||''',statown=>''MALU'',stattab=>''STAT_TAB'',cascade=>TRUE);' Output
 from dba_tables
 where owner in
 (
 'SCOTT','MTEST'
 )
 and table_name in
 ('EMP','CUST')
 order by OWNER,table_name ;
exec dbms_stats.export_table_stats(ownname=>'MTEST',tabname=>'CUST',statown=>'MALU',stattab=>'STAT_TAB',cascade=>TRUE);
exec dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMP',statown=>'MALU',stattab=>'STAT_TAB',cascade=>TRUE);

Step3) Execute the commands generated by above command connecting as MALU
################################################################################################################

Step4) Then take export backup of table STAT_TAB from schema MALU. COPY the dump file to target server.
################################################################################################################
 cat > exp_stat.ctl
 userid=MALU/malu
 dumpfile=DATA_PUMP_DIR
 tables=STAT_TAB
 dumpfile=exp_stat.dmp
 LOGFILE=exp_stat.log
 expdp parfile=exp_stat.ctl
..
 . . exported "MALU"."STAT_TAB" 17.5 KB 28 rows
 ..

Step5) Import the dump file into target database by using impdp or imp utility. Here i imported the dump file in MALU Schema at target server.
##################################################################################################################################################
 cat > imp_stat.ctl
 userid=MALU/malu
 dumpfile=DATA_PUMP_DIR
 tables=STAT_TAB
 dumpfile=exp_stat.dmp
 LOGFILE=imp_stat.log

Run Import
============
impdp parfile=imp_stat.ctl


Step6) Import the statistics into application schemas
################################################################################################################
REM Generates commands for Deleting existing stats on tables
REM -------------------------------------------------------------
set line 400
col output for a4000
 select 'exec dbms_stats.delete_table_stats(ownname=>'''||OWNER||''',tabname=>'''||TABLE_NAME||''',cascade_indexes => true,
 cascade_columns => true);' Output
 from dba_tables
 where owner in
 (
 'SCOTT','MTEST'
 )
 and table_name in
 ('EMP','CUST')
 order by OWNER,table_name ;


exec dbms_stats.delete_table_stats(ownname=>'MTEST',tabname=>'CUST',cascade_indexes => true,cascade_columns => true);
exec dbms_stats.delete_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade_indexes => true,cascade_columns => true);

REM Generates commands for Importing New stats for tables
REM ------------------------------------------------------
 select 'exec dbms_stats.import_table_stats(ownname=>'''||OWNER||''',tabname=>'''||TABLE_NAME||''',statown=>''MALU'',stattab=>''STAT_TAB'',cascade=>TRUE);' Output
 from dba_tables
 where owner in
 (
 'SCOTT','MTEST'
 )
 and table_name in
 ('EMP','CUST')
 order by OWNER,table_name ;

exec dbms_stats.import_table_stats(ownname=>'MTEST',tabname=>'CUST',statown=>'MALU',stattab=>'STAT_TAB',cascade=>TRUE);
exec dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'EMP',statown=>'MALU',stattab=>'STAT_TAB',cascade=>TRUE);

select owner,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,LAST_ANALYZED from dba_tables where table_name in('EMP') and owner='SCOTT';


Step7) Drop the stats_table in target server.
################################################################################################################
 EXEC DBMS_STATS.drop_stat_table('MALU','STAT_TAB');

#########################################
 Other Info
#########################################
 For Table
 --------------
 Exec dbms_stats.delete_table_stats (
 ownname => 'SCOTT',
 tabname => 'SALES',
 cascade_indexes => true,
 cascade_columns => true
 );
 REM exec dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMP',stattab=>'STAT_TAB', cascade=>TRUE);
For Schema
 -----------
 exec dbms_stats.export_schema_stats('SCOTT','dictstattab',statown => 'MALU');



Download Same Here


Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: