导入分区表时有时候会出现如下错误:
IMP-00003: ORACLE error 20001 encountered
ORA-20001: Invalid or inconsistent input values
ORA-06512: at "SYS.DBMS_STATS", line 6053
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20001:
"BEGIN DBMS_STATS.SET_TABLE_STATS(NULL,'"HCL"','"SYS_SUBP5290"'"
",NULL,NULL,-1,-1,-1,0); END;"
可能是在导入数据后再导入统计信息时出错误,经过测试发现导出数据量比较小的情况下不会出现该过错,具体原因待查。避免该类错误在导入时加上statistics=none,说明不再导入统计信息:
imp c/c full=n file=H.dmp tables=(hcl) buffer=12800000 statistics=none log=a.log commit=y
对于导入数据很大的情况,如果buffer设置的过小则会出现导入错误:IMP-00032: SQL 语句超过缓冲区长度
这样导入的用户中的表或索引没有做统计分析。
如果在导入完成后要对该用户下的所有表和索引进行统计分析,可执行如下过程:
sql>set serverout on
sql>exec dbms_output.enable(2000000) --设置较大的输出缓冲
sql>exec p_anayze_schema(username); --username改成实际的shcema名
统计过程如下:
create or replace procedure p_anayze_schema(as_username in varchar2) is
/*
该存储过程主要是对表及索引进行分析,
对于包含有子分区sub-partition的表需要注意一下granularity参数。具体参考:
granularity - the granularity of statistics to collect (only pertinent
if the table is partitioned)
default' - gather global- and partition-level statistics
'subpartition' - gather subpartition-level statistics
'partition' - gather partition-level statistics
'global' - gather global statistics
all' - gather all (subpartition, partition, and global) statistics
*/
vi_per number(3) := 100;
vs_username varchar2(64);
vi_start number := dbms_utility.get_time;
begin
vs_username := upper(as_username);
for vcr_rec in (select segment_name,
segment_type,
ceil(sum(bytes) / 1024 / 1024) segment_size
from user_segments
where segment_name not like 'TMP_%'
and segment_name not like 'BIN$%'
group by segment_name, segment_type) loop
case
when vcr_rec.segment_type = 'INDEX' then
case
when vcr_rec.segment_size <= 100 then
vi_per := 100;
when vcr_rec.segment_size <= 300 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete old schema index statistics;
dbms_stats.delete_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze index compute statistics;
dbms_stats.gather_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name,
estimate_percent => vi_per,
degree => 2);
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'TABLE' then
--
case
when vcr_rec.segment_size <= 150 then
vi_per := 100;
when vcr_rec.segment_size <= 500 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete table analyze statistics
dbms_stats.delete_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze table compute statistics;
dbms_stats.gather_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name,
estimate_percent => vi_per,
cascade => true,
granularity => 'all',
degree => 2,
method_opt => 'for all indexed columns');
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'TABLE PARTITION' then
case
when vcr_rec.segment_size <= 150 then
vi_per := 100;
when vcr_rec.segment_size <= 500 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete table analyze statistics
dbms_stats.delete_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze table compute statistics;
dbms_stats.gather_table_stats(ownname => vs_username,
tabname => vcr_rec.segment_name,
estimate_percent => vi_per,
cascade => true,
granularity => 'all',
degree => dbms_stats.default_degree,
method_opt => 'for all indexed columns');
exception
when others then
null;
end;
when vcr_rec.segment_type = 'INDEX PARTITION' then
case
when vcr_rec.segment_size <= 100 then
vi_per := 100;
when vcr_rec.segment_size <= 300 then
vi_per := 50;
else
vi_per := 20;
end case;
begin
--delete old schema index statistics;
dbms_stats.delete_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name);
exception
when others then
null;
end;
begin
--analyze index compute statistics;
dbms_stats.gather_index_stats(ownname => vs_username,
indname => vcr_rec.segment_name,
estimate_percent => vi_per,
degree => dbms_stats.default_degree);
exception
when others then
null;
end;
dbms_output.put_line(vcr_rec.segment_name||'---size:'||vcr_rec.segment_size||'m, took '||ceil((dbms_utility.get_time - vi_start)/100)||'s');
vi_start := dbms_utility.get_time;
/** when vcr_rec.segment_type = 'lobindex' then
vi_start := dbms_utility.get_time;
when vcr_rec.segment_type = 'lobsegment' then
vi_start := dbms_utility.get_time;**/
else
dbms_output.put_line(vcr_rec.segment_type);
end case;
end loop;
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/559237/viewspace-468158/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/559237/viewspace-468158/