ORA-20005:objectstatisticsarelocked==转

SQL> exec dbms_stats.unlock_table_stats(ownname =>'ECC_VIEW',tabname => 'TABLE_TEST1');
PL/SQL procedure successfully completed
or
SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname =>'ECC_VIEW');
PL/SQL procedure successfully completed ============(普通用户权限)==

SQL> exec dbms_stats.gather_table_stats(ownname =>'ECC_VIEW',tabname => 'TABLE_TEST1',cascade =>TRUE,estimate_percent => 20);
PL/SQL procedure successfully completed

////////////////

执行以下 脚本后正常
SQL> exec dbms_stats.unlock_schema_stats(ownname =>'test');

PL/SQL procedure successfully completed

////////////////////

发现一个用户下统计信息没有生成,
查询user_tab_modifications发现变动信息也超过10%

没有警告日志
执行
exec dbms_stats.gather_schema_stats(ownname =>'test',granularity => 'ALL',cascade => true);
还是没有生成

然后执行报
begin dbms_stats.gather_table_stats(ownname => 'test',tabname=> 'TCCLICOMH',granularity => 'ALL',cascade => true);end;

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: 在 "SYS.DBMS_STATS", line 13056
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 2

执行以下脚本后正常
SQL> execdbms_stats.unlock_schema_stats(ownname =>'test');===执行这个就正常了===

PL/SQL procedure successfully completed

SQL>exec dbms_stats.gather_schema_stats(ownname =>'test',granularity => 'ALL',cascade => true);

PL/SQL procedure successfully completed


原因是因为使用impdp只导入metadata_only 或(expdp的时候使用了contend=metadata_only)时 没有使用 useexclude=(table_statistics,index_statistics)
引起的

参考文档
Symptoms
---------
Either ofthe following two error messages are signaled:
1.ORA-38029: object statistics are locked
2.ORA-20005: object statistics are locked (stattype = ALL)

Cause
---------
PossibleCause 1:
DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lockstatistics on the table.

PossibleCause 2:
Using import(imp) or data pump import (impdp) to import a table without dataresults in the table's statistics being locked in 10gR2.

PossibleCause 3:
After anIMPORT is finished for which ROWS=N, the statistics for all tablesimported will be locked.
Part NumberB14233-04 Database Readme 10g Release 2 (10.2) (39.5 OriginalExport/Import)

PossibleCause 4: If the table is a queue table then the statistics areintended to be empty and locked so that dynamic sampling will beused due to the table's volatility. During an upgrade to 10gR2statistics on queue tables are deleted and then locked. In 10gR2when a queue table is created statistics are locked while stillempty.

Solution
---------
If the tableis a queue table then the statistics should remain empty and lockedso that dynamic sampling is used due to the volatility of queuetables. If the table is not a queue table, unlock the statisticsusing DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statisticson the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS andthe force=>true parameter.

Toprevent import (imp) from locking the table's statistics whenimporting a table without therows (rows=n), use statistics=none. Toprevent data pump import (impdp) from locking the table'sstatistics when importing a table without the rows(content=metadata_only), useexclude=(table_statistics,index_statistics).

  

爱华网本文地址 » http://www.413yy.cn/a/25101014/231225.html

更多阅读

怎么转呼啦圈 精 软性呼啦圈怎么转

怎么转呼啦圈 精——简介呼啦圈大家都很喜欢的运动。不但很有意思,还可以帮助身体脂肪运动起来。那么怎么转呼啦圈呢?下面的方法希望能够帮到大家。怎么转呼啦圈 精——方法/步骤怎么转呼啦圈 精 1、呼啦圈呼啦圈是分不同重量型

玩转小米:2 破解小米收费主题等的最新方法

玩转小米:[2]破解小米收费主题等的最新方法——简介 小米官方主题授权已改变机制,笔者之前发布的破解方法已经失效。但,上有政策,下有对策,不要低估我们米粉的力量!现已有最新的破解方法,亲测可行! 本经验就介绍一下:破解小米商店收费主题等

光盘cda转mp3格式方法 mp3转cda格式

?光盘cda转mp3格式方法????cda是光盘文件,如果大家把它复制到电脑会发现播放不出来,听不到音乐。无论你用什么格式的播放器。其实是因为你复制的cda文件只是一个快捷方式而已,大家看它的大小只有几KB或者44KB就知道了。正确的转换方

如何将支付宝的钱转到余额宝 怎么将余额转到余额宝

如何将支付宝的钱转到余额宝——简介有一百多块在支付宝里待着,闲着也闲着转到余额宝里吧!这样每天还会多那么一点点,多好!下面介绍如何将支付宝的钱转到余额宝如何将支付宝的钱转到余额宝——方法/步骤

谷氨酰转肽酶偏高的原因 谷丙转氨酶偏高的原因

谷氨酰转肽酶偏高的原因——简介谷氨酰转肽酶偏高的原因是什么?谷氨酰转肽酶是人体中广泛存在的一种酶,主要分布在肾内,胰和肝也较多,谷氨酰转肽酶偏高或者偏低可能表示此类器官出现病变,需要及时检查治疗,那么造成谷氨酰转肽酶偏高的原因

声明:《ORA-20005:objectstatisticsarelocked==转》为网友锈甲叩城墙分享!如侵犯到您的合法权益请联系我们删除