A reader, March 13, 2016 - 2:57 pm UTC
It causes an error 'no more data read from socket'
Alex Bud, March 13, 2016 - 4:47 pm UTC
'truncate table GTT drop storage' causes an error 'no more data read from socket'
March 14, 2016 - 1:52 am UTC
That's typically a client error. You'd need to give us some context.
SQL> create global temporary table gtt ( x int ) on commit preserve rows;
Table created.
SQL> insert into gtt values (1);
1 row created.
SQL> truncate table gtt drop storage;
Table truncated.
SQL> select * from gtt;
no rows selected
ORA-14462 : On GTT Truncate
Rajeshwaran, Jeyabal, March 14, 2016 - 11:27 am UTC
In case of Truncate on GTT while other session/transaction in use watch out for ORA-14462.
rajesh@PDB1> create global temporary table t(x int)
2 on commit delete rows;
Table created.
rajesh@PDB1> insert into t(x) values(55);
1 row created.
rajesh@PDB1> declare
2 pragma autonomous_transaction ;
3 begin
4 execute immediate 'truncate table t';
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-14462: cannot TRUNCATE temporary table in an autonomous transaction which is already in use by the parent transaction
ORA-06512: at line 4
rajesh@PDB1>
A reader, May 10, 2016 - 9:01 am UTC
Sorry for long "no comments period".
when I'm running something like that:
create or replace procedure ...
begin
execute immediate 'truncate table gtt drop storage';
commit;
end;
I'll take error ora-03113 at sqlplus (or no more data read from socket at SQL Developer)
How to avoid this error?
May 10, 2016 - 10:23 am UTC
Sorry, I'm not able to reproduce:
SQL> create global temporary table gtt ( x int ) on commit preserve rows;
Table created.
SQL> insert into gtt values (1);
1 row created.
SQL>
SQL> create or replace procedure p as
2 begin
3 execute immediate 'truncate table gtt drop storage';
4 commit;
5 end;
6 /
Procedure created.
SQL>
SQL> exec p;
PL/SQL procedure successfully completed.
SQL>
Could you show a copy-paste of your complete test case, starting with "create table"?
Chris
A reader, May 10, 2016 - 11:18 am UTC
MY GTT:
CREATE GLOBAL TEMPORARY TABLE CL_LINKS_PREV_TEMP
(
LINK_ID NUMBER(9, 0) NOT NULL
, RDATE_FROM DATE NOT NULL
, RDATE_TO DATE NOT NULL
, PERIOD_ID NUMBER(1, 0) NOT NULL
, TYPE_ID VARCHAR2(6 BYTE) NOT NULL
, CONSTRAINT CL_LINKS_PREV_TEMP_PK PRIMARY KEY (LINK_ID) USING INDEX (CREATE UNIQUE INDEX CL_LINKS_PREV_TEMP_PK ON CL_LINKS_PREV_TEMP (LINK_ID ASC) NOPARALLEL) ENABLE)
ON COMMIT PRESERVE ROWS TABLESPACE MIS_ETL_TEMP_TBS
NOPARALLEL;
AND MY MAIN procedures:
create or replace procedure load_bs
as
begin
for i in 1..10 loop
load_daily_offbs(null);
end loop;
end;
create or replace procedure load_daily_offbs(datCurrent date)
authid current_user
IS
...
BEGIN
.../*many rows with loading data to different GTT tables and transformation of that data*/
load_used_contract_links(to_date('31.12.2015','dd.mm.yyyy'),'010000'); /*this procedure truncates table mis_etl.cl_links_prev_temp and after that loads data to this table*/
.../*few selects from table mis_etl.cl_links_prev_temp and merges into other tables*/
END;
create or replace procedure load_used_contract_links(datCurrent date, sCurrentTypeID varchar)
authid current_user
IS
...
type arrT is varray(4) of varchar2(80);
arrTables arrT:=arrT('mis_dm.daily_bs','mis_dm.monthly_bs','mis_dm.daily_offbs','mis_dm.daily_transfer');
...
BEGIN
.../*few selects into variables via DBLink*/
execute immediate 'truncate table mis_etl.cl_links_prev_temp drop storage';
commit;
for i in 1..4 loop
execute immediate 'insert into mis_etl.cl_links_prev_temp select ... from ' || arrTables(i) || ' where ...';
commit;
end loop;
EXCEPTION
...
END;
And on truncating table (but not always) i'm having next error (ora-03113).
Trace file contains next information:
...
ORA-07445: exception encountered: core dump [audbfr()+12] [SIGSEGV] [ADDR:0x101010121B018A0] [PC:0x104F93DCC] [Address not mapped to object] []
[TOC00001-END]
[TOC00002]
========= Dump for incident 17776 (ORA 7445 [audbfr]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
swap info: free_mem = 990.52M rsv = 64.00M
alloc = 48.96M avail = 16384.00M swap_free = 16335.04M
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x101010121B018A0] [PC:0x104F93DCC, audbfr()+12] [flags: 0x0, count: 1]
Registers:
iar: 0x0000000104f93dcc lr: 0x0000000104f892b0
msr: 0xa00000000200d032 cr: 0x0000000042220042
r00: 0x0000000000000000 r01: 0x0ffffffffffebbf0 r02: 0x00000001216c68f0
r03: 0x0ffffffffffec630 r04: 0x0101010121b01878 r05: 0x0ffffffffffebc64
r06: 0x0ffffffffffebc62 r07: 0x0ffffffffffebc60 r08: 0x0000000000000000
r09: 0x0000000000000000 r10: 0x0000000000000000 r11: 0x07000101b9b1a418
r12: 0x0000000104f890a8 r13: 0x000000012173aae0 r14: 0x0000000000000000
r15: 0x0000000000000001 r16: 0x0000000000000000 r17: 0x0000000000000001
r18: 0x0000000000000055 r19: 0x0000000000000000 r20: 0x0700000000034d00
r21: 0x0000000000000000 r22: 0x0000000000000000 r23: 0x07000101b63a4480
r24: 0x0ffffffffffec358 r25: 0x00002ef2b569608f r26: 0x0000000000000055
r27: 0x0000000000000000 r28: 0x0000000000000055 r29: 0x0000000000000000
r30: 0x0000000121b01878 r31: 0x0000000000000055
audbfr()+0 ori r0, r0, 0
audbfr()+4 11 0
audbfr()+8 addi r0, r0, 0
> audbfr()+12 ld r8, 40(r4)
audbfr()+16 bc 0x30 <0x4f93e00>, 0x2, 0x4
audbfr()+20 addi r4, r0, 0
audbfr()+24 std r4, 200(r3)
audbfr()+28 sth r4, 0(r5)
audbfr()+32 std r5, 208(r3)
*** 2016-05-10 11:40:47.840
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=83zq2pkdhjwvc) -----
truncate table mis_etl.cl_links_prev_temp drop storage
[TOC00005]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
7000101b03f2308 69 procedure MIS_DM.LOAD_USED_CONTRACT_LINKS
7000101b78e4f90 313 procedure MIS_DM.LOAD_DAILY_OFFBS
70001019d35b588 5 procedure MIS_DM.LOAD_BS
7000101b78e8f78 1 anonymous block
[TOC00005-END]
[TOC00004-END]
...
May 10, 2016 - 1:46 pm UTC
Ah, ORA-07445. Looks like you've hit a bug.
You'll need to take this up with support.
FWIW you example works for me - 12.1.0.2 on Linux
A reader, May 10, 2016 - 11:36 am UTC
I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production on AIX
Alex Bud, May 11, 2016 - 5:58 pm UTC
Thanks a lot!