This content has been marked as final. Show 5 replies
Please help me out troubleshoot this issuetwo different STATS_TABLE exist; one for "source_schema" & another for "dest_schema".
It is a BAD idea to use lower case schema names.
I have changed the object names while posting..so dont worry about them...they all were in upper case while executing....Can you please elaborate on "different
stats table for each schema"
user SCOTT can have table EMP
user BRYCE can have table EMP.
EMP table is different for each schema.
The same can be true for STATS_TABLE.
Each schema has its own STATS_TABLE.
Agreed...But if you look at the statements....the stats_table has been made to reside in the destination schema ....and the import statment executes without any errors....so that means it did find the stat table...because it finds the stats table in its own schema.
njafri wrote:It looks as if you're trying to import the stats for an index that Oracle won't be able to find in the stats_table.
exec DBMS_STATS.CREATE_STAT_TABLE('dest_schema','STATS_TABLE' ,'tablespace'); exec dbms_stats.export_index_stats(ownname=>'source_schema',indname=>'source_ind',stattab=>'STATS_TABLE',statown=>'dest_schema'); exec dbms_stats.import_index_stats(ownname=>'dest_schema',indname=>'dest_index',stattab=>'STATS_TABLE',statown=>'dest_schema',force=>TRUE);
If you want this to work you'll have to modify the contents of the stats table something like - update c1 where type = 'I' and c1 = 'SOURCE_IND'.
Oracle Corp. does not approve of this strategy - at least, one of my clients was told by an Oracle support analyst that they would not be supported if they had problems after using this technique.
Personally I would use dbms_stats.get_index_stats() to read the source set of stats, then dbms_stats.set_index_stats() to write them to the target index.
Update: I've just posted [http://jonathanlewis.wordpress.com/2010/01/06/copy-stats/] on my blog.
Edited by: Jonathan Lewis on Jan 6, 2010 2:26 PM