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.
