All geek questions in one place

The request causes the database to fail using ORA-0113

I need to create a collection of elements in a collection inside an inline view. I tried to disaggregate the collection and copy it again using the collection and table functions, but it does not work with ORA-03113.

The following is a simplified version of the request that is causing the problem.

In my implementation, I will have many levels of nested queries connected to joins in one query, which should combine all the collections into one. For performance reasons (expensive context switching), implementing PL / SQL code to aggregate collections is not an option.

Thanks so much 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.
+6
source share
1 answer

ORA-03113 : end of file on communication channel error is a common error, as you probably know if you posted a question here. Therefore, instead of carrying you with possible reasons, here is a list of things that you need to pay attention to to help you solve the problem. This error is so common that it is unlikely that anyone will find the exact cause, so you will need to learn how to debug this specific error by inserting log and / or trace files.

Offer . First, I would suggest something to reduce the amount of memory needed for the request and reduce complexity. Create a staging table and run the query in separate steps. Oracle may run into memory problems (especially on Windows), which can cause problems with shutting down the listener and terminating connections. You can do this with direct SQL, as you said you did not want to use PL * SQL.

Ideas for solving problems . Check alert_sid.log on the server. The location of alert_sid.log is set by the initialization parameter BACKGROUND_DUMP_DEST. This may indicate that the server is down. You may need to change the number of retransmissions. Then check other Oracle trace files.

Set trace parameters using Oracle configuration files .

1. SQLNET.ORA

  • Install using Oracle Net Manager

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

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

2. LISTENER.ORA

  • Install using Oracle Enterprise Manager or Oracle Net Manager

  • TRACE_LEVEL_listener_name - defines the level of detail of the trace of the object record for the listener. - off (equivalent to 0) does not provide tracing - user tracing (equivalent to 4) to identify user error conditions - admin (equivalent to 6) tracing to identify installation problems - support (equivalent to 16) provides trace information for troubleshooting information For Oracle Support Services.

  • TRACE_DIRECTORY_listener_name - sets the target 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

  • Install using Oracle Net Manager

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

Using trace files created in various Oracle configuration files should provide you with the necessary information to determine the root cause of the error. I would focus on problems related to the listener or problems due to lack of memory.

+1
source

Source: https://habr.com/ru/post/918365/

More articles:


All Articles