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.
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.