ESB Instances not showing up on the console

Noticed that in 10.1.3.4 MLR#10 Oracle SOA Suite ESB instances were not erroring out nor were they invoking the BPEL processes downstream. The logs (opmn and log.xml) had nothing worth mentioning.  Downstream BPEL process was not being invoked.  It looked like the ESB RTs were not working the way they should be.  Looking at the ESB Dehydration store, saw that the tablespace used by user oraesb was full.  Clearing the tablespace (as this was a test instance) got the ESB RTs going. 


Found the following SQL useful in analyzing the tablespace - found this long time back, unknown author.
select d.status,
  db.name dbname,
  d.tablespace_name tsname,
  d.extent_management,
  d.allocation_type,
  to_char(nvl(d.min_extlen / 1024, 0),
    '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
    "ALLOC_SIZE (K)",
  d.contents "Type",
case
when(d.contents = 'TEMPORARY') then
  to_char(nvl(a.bytes / 1024 / 1024, 0),
  '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
else
  to_char(nvl(t.bytes / 1024 / 1024, 0),
  '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
end as "Size (M)",
  to_char(nvl((a.bytes - nvl(f.bytes, 0)) / 1024 / 1024,   0),
  '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
  "Used (M)",
  to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100,   0),
  '990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
  "Used (%)"
from sys.dba_tablespaces d,
    (select tablespace_name,
     sum(bytes) bytes
   from dba_data_files
   group by tablespace_name)
a,
    (select tablespace_name,
     sum(bytes) bytes
   from dba_temp_files
   group by tablespace_name)
t,
    (select tablespace_name,
     sum(bytes) bytes
   from dba_free_space
   group by tablespace_name)
f,
  v$database db
where d.tablespace_name = a.tablespace_name(+)
 and d.tablespace_name = f.tablespace_name(+)
 and d.tablespace_name = t.tablespace_name(+)
order by 10 desc;

Used the following script to purge all instances from ESB Dehydrations store.

DECLARE
 po dbms_aqadm.aq$_purge_options_t;
BEGIN
--before starting clean up temp tables
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_ACTIVITY';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_TRACKING_FIELD_VALUE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_FAULTED_INSTANCE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_TRANSACTION_STATUS';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_INSTANCE_RELATION_XML';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_SERVICE_RELATION';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_RELATION_XML';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_ACTIVITY';

--before purging the aq topics
 po.block := FALSE;
 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_ERROR_RETRY',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_ERROR',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_JAVA_DEFERRED',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_MONITOR',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_CONTROL',
   purge_condition => NULL,
   purge_options => po);
END;
/
exit;

/