Query causes DB to fail with ORA-0113

Question

I need to create a collection out of elements in a collection within an inline view. I've tryed to deaggregate the collection and aggregate it again using the collect and table functions, but it fails with ORA-03113.

Below is a simplified version of the query causing the issue.

In my Implementation I will have many levels of nested queries joined with unions in a single query that should aggregate all collections into a single one. For performance reasons (Expensive context switching), implementing PL/SQL code to aggregate the collections is not an option.

Thanks a lot for your feedback/suggestions.

Script

---

SELECT BANNER FROM V$VERSION
/

CREATE OR REPLACE TYPE OBJECT_ID_TAB_T IS TABLE OF NUMBER(11);
/

SELECT OWNER, CAST(COLLECT( MULTISET(SELECT COLUMN_VALUE FROM TABLE((OBJECT_ID_LIST) ))) AS OBJECT_ID_TAB_T) AS OBJECT_ID_LIST
FROM  (SELECT OWNER, OBJECT_NAME, CAST(COLLECT(OBJECT_ID) AS OBJECT_ID_TAB_T)  AS OBJECT_ID_LIST
       FROM ALL_OBJECTS
       GROUP BY OWNER, OBJECT_NAME
      )
GROUP BY OWNER
/

Results

---

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production    
PL/SQL Release 11.2.0.1.0 - Production                                          
CORE    11.2.0.1.0  Production                                                      
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                         
NLSRTL Version 11.2.0.1.0 - Production                                          

5 rows selected.
Type created.
SELECT OWNER, CAST(COLLECT( MULTISET(SELECT COLUMN_VALUE FROM TABLE((OBJECT_ID_LIST) ))) AS OBJECT_ID_TAB_T) AS OBJECT_ID_LIST
FROM  (SELECT OWNER, OBJECT_NAME, CAST(COLLECT(OBJECT_ID) AS OBJECT_ID_TAB_T)  AS OBJECT_ID_LIST
       FROM ALL_OBJECTS
       GROUP BY OWNER, OBJECT_NAME
      )
GROUP BY OWNER
*
Error at line 0
ORA-03113: end-of-file on communication channel
Process ID: 8000
Session ID: 154 Serial number: 164

Script Terminated on line 25.

Answer 1

The ORA-03113: end-of-file on communication channel error is a generic error as you are probably aware if you posted the question here. So rather than bore you with the possible causes, here is a list of things to look at to help you solve the problem. This error is so common that it is unlikely someone will find the exact cause so you will need to learn how to debug this particular error by digging into log and/or trace files.

Suggestion: I'd first like to suggest something to reduce the memory needed by the query, and reduce the complexity. Create an intermediate table and perform the query in separate steps. Oracle may be running into memory problems (especially if on Windows) and that can cause problems with the listener running out of memory and connections being terminated. You could do this with straight SQL as you said you don't want to use PL*SQL.

Problem Solving Ideas: Check the alert_sid.log on the server. The location of alert_sid.log is specified by the BACKGROUND_DUMP_DEST initialization parameter. It may indicate that the server has gone down.You may need to modify your re-transmission count. Next check other Oracle trace files.

Set Trace Options using Oracle configuration files.

---

1. SQLNET.ORA

• Set using Oracle Net Manager

• TRACE_DIRECTORY_CLIENT Establishes the destination directory for the client trace output. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

• TRACE_DIRECTORY_SERVER - Establishes the destination directory for the database server trace output. By default, the server directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

---

2. LISTENER.ORA

• Set using Oracle Enterprise Manager or Oracle Net Manager

• TRACE_LEVEL_listener_name - Specifies the level of detail the trace facility records for the listener. - off (equivalent to 0) provides no tracing - user (equivalent to 4) traces to identify user-induced error conditions - admin (equivalent to 6) traces to identify installation-specific problems - support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services

• TRACE_DIRECTORY_listener_name - Establishes the destination directory and file for the trace file. By default the directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows, and the file name is listener.trc.

---

3. CMAN.ORA

• Set using Oracle Net Manager

• TRACE_DIRECTORY - By default, the directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

---

Using the trace files created in the various Oracle configuration files should give you the information you need in identifying the root cause of the error. I would focus on issues surrounding the listener or problems due to shortage of memory.