copy_part_stat.sql

spool 04_copy_part_stat.log

conn / as sysdba

execute sys.dbms_stats.export_schema_stats(ownname=>'test',statown=>'OPER',stattab=>'STAT_TABLE',statid=>'test_20160831');

execute sys.dbms_stats.export_schema_stats(ownname=>'test',statown=>'OPER',stattab=>'STAT_TABLE',statid=>'test_20160831');

--execute sys.dbms_stats.export_schema_stats(ownname=>'test',statown=>'OPER',stattab=>'STAT_TABLE',statid=>'test_20160831');

execute sys.dbms_stats.export_schema_stats(ownname=>'test',statown=>'OPER',stattab=>'STAT_TABLE',statid=>'test_20160831');

disconnect;

@/testdb/change/env/env_test.sql

conn &v_test_un/&v_test_pw

set time on set timing on set echo on set linesize 150 set pagesize 400

EXEC DBMS_STATS.COPY_TABLE_STATS('test','test','PART148','PART145');

select table_name,PARTITION_NAME,num_rows,AVG_ROW_LEN,BLOCKS from user_tab_partitions where table_name in ('test') order by table_name, partition_name;

select index_name , partition_name from user_ind_partitions where status !='USABLE';

select index_name from user_indexes where status='UNUSABLE';

select distinct status from user_indexes; select distinct status from user_ind_partitions;

 

 

REF:

COPY_TABLE_STATS      (new 11g parameters)
Copy statistics from one table partition to another	dbms_stats.copy_table_stats(       ownname      IN VARCHAR2,       tabname      IN VARCHAR2,       srcpartname  IN VARCHAR2,       dstpartname  IN VARCHAR2,         scale_factor IN NUMBER  DEFAULT 1,         flags        IN NUMBER  DEFAULT NULL,         force        IN BOOLEAN DEFAULT FALSE);
	SELECT partition_name FROM user_tab_partitions WHERE table_name = 'SALES';      exec dbms_stats.copy_table_stats('SH',      'SALES', 'SALES_Q3_2002', 'SALES_Q3_2003');