12c SOA - Manual purging SOA Infra

Follow the steps for purging SOA infra:

Step1: Use the following query to check the total consumed space by all tables in SOA infra.
select sum(bytes)/1024/1024/1024 "size in GB" from dba_segments where    owner='CERT_SOAINFRA' and SEGMENT_TYPE='TABLE';

Step2: This query to check how much space is consumed by each table.
SELECT SEGMENT_NAME TABLE_NAME, SUM(BYTES) / (1024 * 1024 *1024) TABLE_SIZE_GB FROM USER_EXTENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME
ORDER BY TABLE_SIZE_GB DESC;

By using this command, we found that the table which consumes max space is Audit trail, thus we need to delete old data from this table in order to free some table space.

Step3: Use the following command to find the a set of data to be deleted. Generally we delete 50K data in batch manner.
select * from AUDIT_TRAIL where trunc(CI_PARTITION_DATE) < sysdate and rownum<=50000;

Note: You can also do the same based on count id and max partition date.

select max(CI_PARTITION_DATE) from audit_trail where COUNT_ID < 3600000

 Step4: Use the following command to delete the old records by deleting 50K records at a time.
Delete from (select * from AUDIT_TRAIL where trunc(CI_PARTITION_DATE) < sysdate) where  rownum<=50000;

Step5: Use following command to enable row movement.
ALTER TABLE AUDIT_TRAIL enable row movement;

Why we need to enable row movement?
Because by default it is disabled, enabling row movement will enable rows to move (eg: rowids to change).

Step6: Use the following command to shrink the table space.
ALTER TABLE AUDIT_TRAIL shrink space;

Why we need to shrink space?
This command helps us to recover space and amend the high water mark (HWM).

What is HWM?
The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.
For example, assume that you have a million row table that takes 30 seconds to read.  After deleting 900,000 rows, a full scan on the table will still take 30 seconds.
This is because the table high water mark is not re-set after delete operations. Thus we use Shrink space command.

Step7: Use the following command to disable row movement.
ALTER TABLE AUDIT_TRAIL disable row movement;

Step8: Use the following query to find the used space and free space for SOA infra
SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99')
 AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99')
 AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

Step8: Execute again the step 1 query and can see the purging has been done successfully and soa_infra size is decreased.

After the table space of SOA infra is reduced, we must enable auto purge from SOA EM console.


Useful Undo table queries:

Use following query to find the ACTUAL UNDO SIZE [MByte], UNDO RETENTION [Sec], NEEDED UNDO SIZE [MByte].
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
TO_NUMBER(SUBSTR(e.value,1,25)) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value)*TO_NUMBER(f.value)*g.undo_block_per_sec)/1048576 "NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';

Use following query to find ACTUAL UNDO SIZE [MByte], UNDO RETENTION [Sec] and OPTIMAL UNDO RETENTION [Sec]
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
to_number(SUBSTR(e.value,1,25)) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';