存储过程收集统计信息ORA-20000报错解决记录
存储过程如下:
create or replace procedure ad.table_analy_bill( p_BillMonth in number,--bill_month
p_tail in number,
p_nInsId in number,
p_nSeqId in number, --no u
p_nStatus out number,
p_szErrorMsg out varchar2
) is
v_sql varchar2(2000);貔貅的戴法
table_name varchar2(200);
香蕉鸡蛋羹
v_BillMonth number(8);
begin
v_BillMonth:=to_number(substr(to_char(p_BillMonth),1,6));
v_sql := 'CA_CYCLE_RUN';
table_name:='CA_CYCLE_RUN'||'_'||p_tail;
dbms_stats.gather_table_stats(ownname=>'AD',tabname =>upper(table_name),degree => 20,no_invalidate => FALSE );
/*v_sql := 'CA_POCKET';
table_name:='CA_POCKET'||'_'||p_tail;
dbms_stats.gather_table_stats(ownname=>'AD',tabname =>upper(table_name),degree => 20,no_invalidate => FALSE );*/
/*v_sql := 'CA_BILL';
table_name:='CA_BILL'||'_'||p_tail||'_'||v_BillMonth;
dbms_stats.gather_table_stats(ownname=>'AD',tabname =>upper(table_name),degree => 20,no_invalidate => FALSE );*/
/*v_sql := 'CA_BILL_ITEM';
table_name:='CA_BILL_ITEM'||'_'||p_tail||'_'||v_BillMonth;
dbms_stats.gather_table_stats(ownname=>'AD',tabname =>upper(table_name),degree => 20,no_invalidate => FALSE );*/
/*v_sql := 'CA_BILL_PROD';
table_name:='CA_BILL_PROD'||'_'||p_tail||'_'||v_BillMonth;
dbms_stats.gather_table_stats(ownname=>'AD',tabname =>upper(table_name),degree => 20,no_invalidate => FALSE );*/
p_nStatus := 0;
p_szErrorMsg := 'Runing succesd: table_analy_bill';
exception
when others then
p_nStatus:=-1;
p_szErrorMsg:='encounter a exception,sqlcode:'||sqlcode||',sqlerrm:'||sqlerrm||',sql:'||v_sql;
Dbms_Output.put_line(p_szErrorMsg);
return;
end;
win10配置要求第⼀个报错,该⽤户收集统计信息权限不⾜
Ora-20000: Unable To Gather Statistics Concurrently, Insufficient Privileges [ID 1329367.1
SOLUTION:
艺术表现手法FND_STATS is a wrapper of DBMS_STATS. FND_STATS internally calls DBMS_STATS. To adopt the Concurrent Statistics Gathering feature, ur (through which generating stats is being executed) should have the below three privileges:
- MANAGE ANY QUEUE
情绪的词语- CREATE JOB
- MANAGE SCHEDULER预算分析报告
Becau the Gather Tablestatistics is run as APPS ur in the databa and this ur does not have above priviliges, the concurrent request or the FND_STATS or DBMS_STATS fails.
赋权后使⽤AD⽤户重新登录,调⽤存储过程
sqlplus AD/hdyYYHJ8_28jh@10.11.59.108/SHWLW
declare
p_BillMonth number:=2018131;
p_tail number:=0;
p_nInsId number:=NULL;
p_nSeqId number:=NULL;
松鼠鳜鱼
忆惜p_nStatus number:=0;
p_szErrorMsg varchar2(200):='';
begin
ad.table_analy_bill(p_BillMonth,p_tail,p_nInsId,p_nSeqId,p_nStatus,p_szErrorMsg);
end;
/
出现第⼆个报错,
Unable To Gather Statistics, Receive "ORA-20000: Resource Manager Plan Is Not Active or is not managing CPU usage" Error Following Upgrade to 12c (⽂档 ID 2049013.1)
SOLUTION:
alter system t resource_manager_plan = 'DEFAULT_PLAN' scope=both