DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, August 20, 2014

ORA-03113 and ORA-03114 Error when trying to insert on Global Temporary table.

Issue :


I was getting ORA-03113 and ORA-03114  Error when trying to insert on Global Temporary table.


Reproduce the Issue:


Create Global Temporary table:


CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;


Insert Record on Global Temporary table:


SQL> insert into my_temp_table values (12,12);
insert into my_temp_table values (12,12)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3283
Session ID: 1530 Serial number: 4921


Cause:


There may be many reason for ORA-03113 error. In my case, For SYS user default temporary tablespace was not set.  Because of this we are not able to insert any record into Global temporary table.


SQL> select USERNAME,TEMPORARY_TABLESPACE from dba_users where username = 'SYS';
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                            TEMP_TS_GROUP   - In our case there is no tablespace was associated with this Group.



SQL> select * from dba_tablespace_groups;
no rows selected


Solution:


Assign a separate temporary tablespace to SYS user else add temp tablespace to TEMP_TS_GROUP.


SQL> alter user SYS  temporary tablespace TEMP;
User altered.
SQL> insert into my_temp_table values (12,12);
1 row created.





...WISH YOU GOOD LUCK...


1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.