solve ORA-1658 VIA errorstack

Sometimes a issue itself is not complicated, but it is hard due to other reason, such as lack of neccesary information.

For example, one day user come to us and complain that their application job got below error:
F|Nov 29 20:47:34|UNXSERVER|E|27451|GXXS01|conv|ABORTED-SQLCODE -1658 AT 1223 ErrText = |
F|Nov 29 20:47:34|UNXSERVER|E|27451|GXXS01|conv|ENDING DUE TO SERIOUS SQL ERROR|
F|Nov 29 20:47:34|UNXSERVER|E|27462|GXXS02|conv|ABORTED-SQLCODE -1658 AT 1223 ErrText = |
F|Nov 29 20:47:34|UNXSERVER|E|27462|GXXS02|conv|ENDING DUE TO SERIOUS SQL ERROR|

All they can offer us is above error message and a DB name.
Then how should a DBA look into it?

Above error is not a standard DB error message which should begin with: "ORA-".
But from "SQLCODE -1658", we know it should be a DB error, it is not in a standard format due to their application catch the error message and throw it out with its own exception defination.

Let's see what is 1658:
server003[oracle]_db058> oerr ora 1658
01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"// *Cause: Failed to find sufficient contiguous space to allocate INITIAL
// extent for segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
// tablespace or retry with a smaller value for INITIAL

OK, seems it is a simple tablespace free size issue.
But after checking in the DB, found there is not error message in alert.log.
The database have 30+ samiliar very tiny tablespaces.
So what tablespace caused the issue:

Tablespace                    Free MB   Total MB        PCT
------------------------- ----------- ---------- ----------
ICDM_20140801                       5         10         50
ICDM_20130901                       5         10         50
ICDM_20131201                       5         10         50
ICDM_20130501                       5         10         50
ICDM_20130701                       5         10         50
ICDM_20140101                       5         10         50
ICDM_20130801                       5         10         50
ICDM_20131001                       5         10         50
ICDM_20130301                       5         10         50
ICDM_20131101                       5         10         50
ICDM_20140201                       5         10         50
ICDM_20140501                       5         10         50
ICDM_20140601                       5         10         50
ICDM_20130401                       5         10         50
ICDM_20140301                       5         10         50
ICDM_20130601                       5         10         50
ICDM_20140401                       5         10         50
ICDM_20140701                       5         10         50
.............

Still user doesn't know anything, they got the error when running a job. But they don't know what is the current SQL and which table got the error on which tablespace.

Then i enabled the errorstack:
alter system set events '1658 trace name errorstack level 1';
Through above command i require to gernerate errorstack for error id ORA-1658 at DB level.

Then i ask user to re-run their job again.
Of course it fails again with error ORA-1658, but with the help of errorstack, this time we get the detail in alert.log:
Errors in file /server/backup/oracle/dump/getsstm2/diag/rdbms/db058/db058/trace/db058_ora_13006.trc:
ORA-01658: unable to create INITIAL extent for segment in tablespace FILE_20130101

OK, now we got the tablespace name: FILE_20130101.
After adding 4gb to FILE_20130101, the issue solved.