When you want to gather stat of a table and oracle throw ORA-20000:
begin
dbms_stats.gather_table_stats(
ownname=> 'your_username',
tabname=> 'jbm' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "your_username"."jbm", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24270
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 2
SQL> grant analyze any to your_username;
SQL> exec dbms_stats.unlock_table_stats(ownname => 'your_username',tabname => 'jbm');
SQL> begin
dbms_stats.gather_table_stats(
ownname=> 'your_username',
tabname=> 'jbm' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;
/
PL/SQL procdure successfully completed.
nice post, its very help full.
ReplyDeleteYou're Welcome
Deletevery useful. Thanks
ReplyDeleteWork for me, Thanks
ReplyDeletevery nice post, thanks a lot!
ReplyDelete