A quick introduction and a fun database trick
By christian on Jul 28, 2011
Hi, welcome to the SOA blog.
For my first trick, here's a little workaround for a problem that you may encounter if you wish to process large payloads, attached to a SOAP packet using HTTP/MIME, in Oracle SOA 11g.
The problem is that the SOA engine stages the payload into the database prior to processing it. This staging area is a temporary datafile and can be quite small with the default database creation scripts.
Here's how you can see how big it is:
SELECT A.tablespace_name tablespace, D.filename, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
SELECT B.name, C.name filename, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size, C.name
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.filename, D.mb_total;
This query will list all the tablespaces and datafiles in your system and their sizing. You're looking for the tablespace named <db_prefix>_iastemp.dbf. This is the temporary datafile that we use.
If it's smaller than the size of the largest payload you wish to process as an attachment, and probably multiples thereof, you'll need to increase the size of the datafile.
alter database tempfile '<path>_iastemp.dbf' resize 512M;
This will resize this tempfile to 512M. Obviously you should substitute your tempfile from the listing for your database, and you will want to pick an appropriate size for your payload requirements.