Wednesday, July 11, 2012

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

I was in the process of creating the Swingbench Order Entry Schema with scale factor 100 when I noticed that the schema creation failed.

On looking at the alert log, I found the following message:
Tue Jul 10 21:47:51 2012
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Tue Jul 10 21:47:53 2012
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_37158936.trc  (incident=2745):
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb/incident/incdir_2745/testdb_ora_37158936_i2745.trc
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_37158936.trc  (incident=2746):
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)

Fix:
The oewizard had suggested that a schema of scale factor 100 needs around 60 GB of temp file space.
My host machine(database server) did have 16GB of memory, so I didn't make any changes to it.

So I just added the tempfiles as follows:
SQL> alter tablespace temp add tempfile '/orcldata/testdb/temp02.dbf' size 31G;

Tablespace altered.

SQL> alter tablespace temp add tempfile '/orcldata/testdb/temp03.dbf' size 31G;

Tablespace altered.

SQL> alter tablespace temp add tempfile '/orcldata/testdb/temp04.dbf' size 31G;

Tablespace altered.

SQL> select FILE_NAME, (bytes)/1024/1024 from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
(BYTES)/1024/1024
-----------------
/orcldata/testdb/temp01.dbf
               20

/orcldata/testdb/temp02.dbf
            31744

/orcldata/testdb/temp03.dbf
            31744

/orcldata/testdb/temp04.dbf
            31744

This fixed the issue.