OraFAQ Forum: Spatial » declare geom SDO_GEOMETRY causing oracle mem usage increase

Home » Server Options » Spatial » declare geom SDO_GEOMETRY causing oracle mem usage increase

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

declare geom SDO_GEOMETRY causing oracle mem usage increase [message #562137]

Sun, 29 July 2012 09:49

cy163
Messages: 1
Registered: July 2012

Junior Member

Hi, ALL

I've got milions of line objects to be inserted into a Oracle sptial table.

I use the following code to do the work. I am using Oracle 10g

declare geom  SDO_GEOMETRY;
begin geom:=SDO_GEOMETRY(2002, 8307, MDSYS_ELEM_INFO_ARRAY(1,2,1), MDSYS_SDO_ORDINATE_ARRAY(%s);
EXECUTE IMMIDIATE 'insert in %s values(%s, null, null,%d, :1, null)'USING geom;
END;

It works fine for hours. Then, the program got exception error. I noticed from the Windows Task Management that the amount of memory consumed by the oracle process increased as the program running.

I do not know why. I suspect that 'declare geom SDO_GEOMETRY' which actually is in a for loop cause too many varaible geom are declared consuming too many memory. But, I am not sure.

To avoid placing 'declare geom SDO_GEOMETRY' in the for loop, I also tried with declaring a global varible outside the for loop body using

create or replace PACKAGE MM IS

   geom ADO_GEOMETRY

END;

begin MM.geom:=SDO_GEOMETRY(2002, 8307, MDSYS_ELEM_INFO_ARRAY(1,2,1), MDSYS_SDO_ORDINATE_ARRAY(%s);
EXECUTE IMMIDIATE 'insert in %s values(%s, null, null,%d, :1, null)'USING MM.geom;
END;

but no luck too.

Or you may tell me the best way to insert a large amount of LINE objects into sptial table.

Report message to a moderator