Copy Oracle stats without DDL-CodePudding

I need to copy stats from production to test database but all sample code I found use create_stat_table procedure, but I cannot create tables on source database. Is there any chance to do this different way? Maybe I can generate some INSERT statements from system views (like dba_tab_statistics) and run on destination db?

CodePudding user response：

You can use the get_*_stats to fetch the details from prod, then pass these by calling set_*_stats in test.

For example, get the stats like this:

create table t as 
  select level tid, date'2022-01-01'   level c1 
  from   dual
  connect by level <= 100;
  
exec dbms_stats.gather_table_stats ( user, 't' ) ;
  
declare
  numrows number;
  numblks number;
  avgrlen number;
begin
  dbms_stats.get_table_stats (
     ownname => user, 
     tabname => 'T', 
     numrows => numrows, 
     numblks => numblks,
     avgrlen => avgrlen
  );
  
  dbms_output.put_line ( 
    numrows || '; ' || numblks || '; ' || avgrlen 
  );
end;
/

100; 4; 11

Then load them like this (I'm dropping the table in the same DB for simplicity)

drop table t purge;
create table t ( tid integer, c1 date );

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
<null>     <null>     <null>  

select num_rows, blocks, avg_row_len 
from   user_tables
where  table_name = 'T';

begin
  dbms_stats.set_table_stats (
     ownname => user, 
     tabname => 'T', 
     numrows => 10, 
     numblks => 4,
     avgrlen => 11
  );
end;
/

select num_rows, blocks, avg_row_len 
from   user_tables
where  table_name = 'T';

  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
        10          4          11

Copying the stats for all the columns and indexes this way is a faff though. If you need this, speak with whoever manages the database and get them to copy these over for you!