Dear Friends,
I'm Getting the following error when running the GATHER_SCHEMA_STATS :
ORA-12012: error on auto execute of job "MySchema"."ST$8562_89"
ORA-00018: maximum number of sessions exceeded
ORA-06512: at "SYS.DBMS_STATS", line 35069 ORA-06512: at line 1
GATHER_SCHEMA_STATS :
connect SYS/*********@localhost:1521/dbinstance
ALTER system set parallel_adaptive_multi_user=false;
exec DBMS_STATS.set_global_prefs (pname => 'CONCURRENT',pvalue => 'ALL');
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=800;
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;
exec DBMS_STATS.UNLOCK_SCHEMA_STATS ( ownname => 'MY_SCHEMA');
exec DBMS_STATS.GATHER_SCHEMA_STATS('MY_SCHEMA',cascade => true,force => true,method_opt=>'FOR ALL COLUMNS SIZE 1');
exec DBMS_STATS.LOCK_SCHEMA_STATS ( ownname => 'MY_SCHEMA');
1/ the following query returns only 2 rows :
select s.status,s.*
from v$process p,v$session s
where s.paddr=p.addr
and s.status in ('INACTIVE','SNIPED');
2/ select * from v$resource_limit; after growing sessions & process numbers
- Current utilization before Gathering stats: sessions = 20 | process = 32
- Max utilization : sessions = 1000 | process = 700
- Initial allocation : sessions = 1000 | process = 700
Oracle Version = Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Thank you in advance for your help
Ce message a été modifié par : bin wang