Database Reference
In-Depth Information
Table
Description
XML_DOCUMENT
This table stores all large objects in the system (for
example, instance_payload documents, dlv_message
documents, and so on). It stores the data as
binary
large objects (BLOBs)
. Separating the document
storage from the metadata enables the metadata to
change frequently without being impacted by the size
of documents.
Measuring database growth
It will be necessary to measure the size of the Dehydration Store database at
regular intervals to be able to see if there is enough free space available in it.
Database free space and growth can be measured in a variety of ways, the
easiest being executing a set of queries to get the free size of the tablespace
for a given schema. Apart from regular measurements, if you are planning to
execute purging, these free space measurements should be taken before and
after purging, to ensure there is a visible difference indicating the effectiveness
of the purge. Execute the free space measurement script that is provided here
to calculate the percentage and free space in MB in the
[PREFIX]_SOAINFRA
schema:
-- Measuring free space in SOA_INFRA tablespace
SELECT * FROM (SELECT C.TABLESPACE_NAME, ROUND(A.BYTES/1048576,2) MB_ALLOCATED,ROUND(B.BYTES/1048576,2) MB_FREE,ROUND((A.BYTES-B.BYTES)/1048576,2) MB_USED, ROUND(B.BYTES/A.BYTES * 100,2) TOT_PCT_FREE, ROUND((A.BYTES-B.BYTES)/A.BYTES,2) * 100 TOT_PCT_USED FROM (SELECT TABLESPACE_NAME, SUM(A.BYTES) BYTES FROM SYS.DBA_DATA_FILES A GROUP BY TABLESPACE_NAME) A, (SELECT A.TABLESPACE_NAME, NVL(SUM(B.BYTES),0) BYTES FROM SYS.DBA_DATA_FILES A, SYS.DBA_FREE_SPACE B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
(+) AND A.FILE_ID = B.FILE_ID (+) GROUP BY A.TABLESPACE_NAME) B, SYS.DBA_TABLESPACES C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME) WHERE TOT_PCT_USED >=0 AND TABLESPACE_NAME=
'DEV_SOAINFRA'
ORDER BY TABLESPACE_NAME;
The output of the previous free space SQL command will look similar to the fol-
lowing screenshot:
