An example of ORA-30009 Fault Handling

Background:
Just a netizen asked the ORA-30009 error, the database version is 10.2.0.5, the operating system version is RedHat 4; then I took into account the hardware and buffer constraints, the verification, the details are as follows:

Error message:
SQL> CREATE TABLE t AS SELECT rownum AS n, lpad ('*', 1000, '*') AS pad FROM dual CONNECT BY level <= 100000;
Create table t as select rownum AS n, lpad ('*', 1000, '*') AS pad FROM dual connect by level <= 100000
*
ERROR at line 1:
ORA-30009: Not enough memory for connect by operation

Procedure:

Check the limits to conclude that there is no problem.
[Oracle @ yangbo bdump] $ ulimit-
Core file size (blocks,-c) 0
Data seg size (kbytes,-d) unlimited
File size (blocks,-f) unlimited
Pending signals (-I) 1024
Max locked memory (kbytes,-l) 32
Max memory size (kbytes,-m) unlimited
Open File (-n) 1024
Pipe size (512 bytes,-p) 8
POSIX message queues (bytes,-q) 819200
Stack size (kbytes,-s) 10240
Cpu time (seconds,-t) unlimited
Max user processes (-u) 8192
Virtual memory (kbytes,-v) unlimited
File locks (-x) unlimited

Check whether the database buffer is set automatically.
SQL> show parameter buffer

NAME TYPE VALUE
-----------------------------------------------------------------------------
Buffer_pool_keep string
Buffer_pool_recycle string
Db_block_buffers integer 0
Log_buffer integer 2875392
Use_indirect_data_buffers boolean FALSE

At the same time, I checked the sga configuration. From experience, I found that it seems to be small.

19:46:31 SQL> show parameter sga

NAME TYPE VALUE
-----------------------------------------------------------------------------
Lock_sga boolean FALSE
Pre_page_sga boolean FALSE
Sga_max_size big integer 312 M
Sga_target big integer 312 M

Analysis:

According to the above analysis, the data to be inserted is in 100000 rows, but his sga configuration is only 312 M, the host memory is only 512 M, and the database is running on the virtual machine, in addition, the table with the 100000 rows of data has been inserted once and has not been submitted.
The ORA-30009 error is reported during the second insertion, which can be determined because the SGA is too small.

Processing Process:

1. Add memory to 1 GB
2. Expand SGA
SQL> alter system set sga_max_size = 512 M scope = spfile;
SQL> alter system set sga_target = 512 M scope = spfile;
3. Restart the database.
4. Execute again to solve the fault.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12