Home » SQL & PL/SQL » SQL & PL/SQL » Create table as select generates ORA-03113 (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi)
Create table as select generates ORA-03113 [message #286274] Fri, 07 December 2007 01:31 Go to next message
Buchas
Messages: 83
Registered: March 2006
Member
Hello,

I am running long-time (~2,5h) warehouse data load-up scripts, and they crash always with the same error:
CREATE TABLE DIMENSIONS.TMP01_T
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 


The table DIMENSIONS.TMP01_T is created using CREATE TABLE AS SELECT.... statement.

The table DIMENSIONS.TMP01_T actually gets created in the database (with ~3million records). The next statement is
CREATE INDEX index_name1  ON  DIMENSIONS.TMP01_T ( KOMISO_GAVEJAS_KEY ) TABLESPACE &INDEX_SPACE;

It does not get executed. I assume Oracle crash somewhere in the middle of these two.

The warehouse data load-up scripts are run using sqlplus utility on the server PC.

The error is received on productional database where Redo logs are running. On test database where no redo logs are running it was not possible to repeat the error.

I post so many various details because if i try searching info on ORA-03113, i find smth like:
Quote:


Copied from: http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm
For example, ORA-3113 could be signaled for any of these scenarios:

- Server machine crashed
- Your server process was killed at O/S level
- Network problems
- Oracle internal errors / aborts on the server
- Client incorrectly handling multiple connections
- etc.. etc.. etc.. - a lot of possible causes !!


How do I know which scenario is my case?

So far I have stiked out :
- Server machine crashed (not possible, databse continues to operate);
- Network problems (not possible, scripts were executed on server PC);
- Client incorrectly handling multiple connections (not sure about that, but scripts use one connection only).

Here is also the core dump log:
LABEL:          CORE_DUMP 
IDENTIFIER:     A63BEB70 

Date/Time:       Wed Dec  5 22:35:47 WET 2007 
Sequence Number: 11639 
Resource Name:   SYSPROC 

Description 
SOFTWARE PROGRAM ABNORMALLY TERMINATED 

Probable Causes 
SOFTWARE PROGRAM 

User Causes 
USER GENERATED SIGNAL 

        Recommended Actions 
        CORRECT THEN RETRY 

Failure Causes 
SOFTWARE PROGRAM 

        Recommended Actions 
        RERUN THE APPLICATION PROGRAM 
        IF PROBLEM PERSISTS THEN DO THE FOLLOWING 
        CONTACT APPROPRIATE SERVICE REPRESENTATIVE 

Detail Data 
SIGNAL NUMBER 
           6 
USER'S PROCESS ID: 
                372756 
FILE SYSTEM SERIAL NUMBER 
          12 
INODE NUMBER 
       40962 
PROCESSOR ID 
           2 
CORE FILE NAME 
/oracle/10.2.0/db_1/admin/dis/cdump/core_372756/core 
PROGRAM NAME 
oracle 
STACK EXECUTION DISABLED 
           0 


If you have any ideas what is happening, help please. I am totally confused by this error.
Re: Create table as select generates ORA-03113 [message #286289 is a reply to message #286274] Fri, 07 December 2007 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your case the most likely is:
Quote:

Oracle internal errors

You should call Oracle support.

Regards
Michel
Re: Create table as select generates ORA-03113 [message #286301 is a reply to message #286289] Fri, 07 December 2007 02:28 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
Thanks for the quick responce.
How do I call Oracle support then? Smile I am located in Lithuania, Vilnius.
Re: Create table as select generates ORA-03113 [message #286304 is a reply to message #286301] Fri, 07 December 2007 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://metalink.oracle.com/metalink/plsql/ml2_gui.startup

But you must have a CSI (Client Support Identifier).

Regards
Michel
Re: Create table as select generates ORA-03113 [message #286307 is a reply to message #286301] Fri, 07 December 2007 02:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You have several options:
- Ask for the responsible person at your company to raise a service request at metalink.oracle.com.

- go to www.oracle.com/support and select your country.

MHE
Re: Create table as select generates ORA-03113 [message #286316 is a reply to message #286307] Fri, 07 December 2007 02:49 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Maaher wrote on Fri, 07 December 2007 08:32


- Ask for the responsible person at your company to raise a service request at metalink.oracle.com.

MHE


That would not be an option in my company. There isn't a single responsible person here (a fact regularly proven on a Friday night) Very Happy
Re: Create table as select generates ORA-03113 [message #286489 is a reply to message #286316] Fri, 07 December 2007 10:35 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Check the corresponding trace file in bdump for details. Also see the alert log.

select name, value from v$parameter where name = 'background_dump_dest';

select 'alert_' || value || '.log' from v$parameter where name = 'db_name';

Previous Topic: Identifying floating point or fractional numbers in sql
Next Topic: Special character handling in PLSQL
Goto Forum:
  


Current Time: Sat Dec 10 16:52:55 CST 2016

Total time taken to generate the page: 0.08356 seconds