模拟ORA-00020错误

有些同学会遇到ORA-00020错误，这个错误是怎么来的呢？

我们来在测试机上模拟下

首先减小processes的值

SQL> alter system set processes=39 scope =spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size		    2252824 bytes
Variable Size		  180359144 bytes
Database Buffers	  125829120 bytes
Redo Buffers		    4718592 bytes
Database mounted.
Database opened.

SQL> set linesize 300 pagesize 300
SQL> select * from v$resource_limit;

RESOURCE_NAME		       CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION	LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes					24		27	   40			40
sessions					27		30	   82			82
enqueue_locks					22		36	 1140		      1140
enqueue_resources				21		21	  580		 UNLIMITED
ges_procs					 0		 0	    0			 0
ges_ress					 0		 0	    0		 UNLIMITED
ges_locks					 0		 0	    0		 UNLIMITED
ges_cache_ress					 0		 0	    0		 UNLIMITED
ges_reg_msgs					 0		 0	    0		 UNLIMITED
ges_big_msgs					 0		 0	    0		 UNLIMITED
ges_rsv_msgs					 0		 0	    0			 0
gcs_resources					 0		 0  UNLIMITED		 UNLIMITED
gcs_shadows					 0		 0  UNLIMITED		 UNLIMITED
smartio_overhead_memory 			 0		 0	    0		 UNLIMITED
smartio_buffer_memory				 0		 0	    0		 UNLIMITED
smartio_metadata_memory 			 0		 0	    0		 UNLIMITED
smartio_sessions				 0		 0	    0		 UNLIMITED
dml_locks					 0		 0	  360		 UNLIMITED
temporary_table_locks				 0		 0  UNLIMITED		 UNLIMITED
transactions					 0		 0	   90		 UNLIMITED
branches					 0		 0	   90		 UNLIMITED
cmtcallbk					 0		 2	   90		 UNLIMITED
max_rollback_segments				11		11	   90		     65535
sort_segment_locks				 3		 3  UNLIMITED		 UNLIMITED
k2q_locks					 0		 0	  164		 UNLIMITED
max_shared_servers				 1		 1  UNLIMITED		 UNLIMITED
parallel_max_servers				 0		 0	   10		      3600

27 rows selected.

可以看到在11.2中该值最小为40

SQL> select * from v$version where rownum <=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

执行下面的脚本测试

[oracle@o11204 ~]$ cat /tmp/test.sh
for i in {1..30}
do

sqlplus -s test/test & <<EOF
set serveroutput on
begin
dbms_lock.sleep(20);
end;
/
EOF

done

这时就会出现提示00020

查看limit会发现最大值达到了40

SQL> select * from v$resource_limit;

RESOURCE_NAME		       CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION	LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes					39		40	   40			40
sessions					43		43	   82			82
enqueue_locks					27		35	 1140		      1140
enqueue_resources				21		49	  580		 UNLIMITED
ges_procs					 0		 0	    0			 0
ges_ress					 0		 0	    0		 UNLIMITED
ges_locks					 0		 0	    0		 UNLIMITED
ges_cache_ress					 0		 0	    0		 UNLIMITED
ges_reg_msgs					 0		 0	    0		 UNLIMITED
ges_big_msgs					 0		 0	    0		 UNLIMITED
ges_rsv_msgs					 0		 0	    0			 0
gcs_resources					 0		 0  UNLIMITED		 UNLIMITED
gcs_shadows					 0		 0  UNLIMITED		 UNLIMITED
smartio_overhead_memory 			 0		 0	    0		 UNLIMITED
smartio_buffer_memory				 0		 0	    0		 UNLIMITED
smartio_metadata_memory 			 0		 0	    0		 UNLIMITED
smartio_sessions				 0		 0	    0		 UNLIMITED
dml_locks					 0		26	  360		 UNLIMITED
temporary_table_locks				 0		 2  UNLIMITED		 UNLIMITED
transactions					 0		 2	   90		 UNLIMITED
branches					 0		 0	   90		 UNLIMITED
cmtcallbk					 0		 2	   90		 UNLIMITED
max_rollback_segments				11		11	   90		     65535
sort_segment_locks				 3		 8  UNLIMITED		 UNLIMITED
k2q_locks					 0		 0	  164		 UNLIMITED
max_shared_servers				 1		 1  UNLIMITED		 UNLIMITED
parallel_max_servers				 0		 0	   10		      3600

27 rows selected.

如果出现这个提示，在资源允许的情况下加大processes的值就可以了