copy_part_stat.sql

最新推荐文章于 2025-02-08 15:50:30 发布

weixin_33749242

最新推荐文章于 2025-02-08 15:50:30 发布

阅读量99

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/feiyun8616/p/5893256.html

本文介绍如何使用 Oracle 的 DBMS_STATS 工具来复制表分区统计数据，并提供了具体的 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');

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');

转载于:https://www.cnblogs.com/feiyun8616/p/5893256.html