Home » SQL & PL/SQL » SQL & PL/SQL » Temporary LOBs, temp space, PGA (DB 12.x)
Temporary LOBs, temp space, PGA [message #667123] Fri, 08 December 2017 06:13 Go to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
If I create a temporary LOB with dbms_lob.createtemporary and load it up with 50MB or so of data, it always goes to a temporary segment. This is indeed the documented behaviour:Quote:
CREATETEMPORARY Procedures
This procedure creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace.
Is there some way to prevent this, so that it will remain in PGA rather than going to disc? I had hoped that giving myself a massive PGA (not just with pga_aggregate_target, I've tried _pga_max_size and _smm_max_size too) would help, but no good. Whether I create the LOB to go through buffer cache or not doesn't make any difference, neither does changing workarea_size_policy..

I realize that what I would like to do may be impossible, but if anyone has an idea to keep temporary LOBs in PGA only that would be very useful.

Thank you for any insight.
Re: Temporary LOBs, temp space, PGA [message #667126 is a reply to message #667123] Fri, 08 December 2017 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tried many ways (in 11gR2 only) but found none that allow to not use the temporary tablespace even with small LOB. Sad

Re: Temporary LOBs, temp space, PGA [message #667127 is a reply to message #667126] Fri, 08 December 2017 11:26 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Thanks for confirming the bad news, Michel.

(ps - glad you are still talking to me, probably won't be allowed to after 29 March 2019
Re: Temporary LOBs, temp space, PGA [message #667128 is a reply to message #667127] Fri, 08 December 2017 11:53 Go to previous message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have just checked if there are any new session parameters or function parameter (hidden or not) related to this in 12.2 but found none.

(You''ll have to pay the VAT for my posts. Smile )

Previous Topic: sequence problem?
Next Topic: i need to know hows this happening?
Goto Forum:
  


Current Time: Tue Oct 16 03:23:15 CDT 2018