Bulk Undeployment in Oracle Soa Suite

This script is for Soa Suite 11g but you can easly modify it to 12c. I think there are a few differences in namespaces. You should have a backup table and a function to access deployed-composites.xml. After running plsql block you should create a zip with the file deployed-composites/deployed-composite.xml and upload it to your mds. Restart is needed to afterwards. Before restart you should avoid doing any change to composites from console which can modify deployed-composites.xml so it's better to restart afterwards. Be sure to upload a valid xml file, otherwise your soa suite will give error while starting. We have close to 1000 composites and a large dehydration store. Undeploying in normal way takes too long even for one composite and doing undeployment manuelly gives us flexibility to redeploy the composites easily if needed (with same deployment id's etc). You have to create 3 useful views in order to run script or you can work on it and combine into a single view as well. 

create table TMP_DEPLOYEDXML
(
  id               NUMBER,
  deployed_xml_org XMLTYPE,
  deployed_xml_mod XMLTYPE,
  created_time     DATE default sysdate,
  updated_time     DATE
)

create or replace function get_composite_data(fullpath in varchar2)
return clob is
vchunk varchar (32000);
vexport number(1);
pi number;
pd number;
pc number;
pv number;
pt number;
retClob clob;
begin
select PATH_PARTITION_ID,PATH_DOCID,PATH_CONTENTID,PATH_VERSION,PATH_CONTENT_TYPE pt into pi,pd,pc,pv,pt from your_mds.mds_paths  p 
where
p.path_fullname=fullpath
and path_high_cn is null;
if(pt=0) then
your_mds.mds_internal_shredded.exportDocumentById(vchunk,vexport,pi,pd,pc,pv,1);
retClob:=retClob||vchunk;
while (vexport=0)
  loop
   your_mds.mds_internal_shredded.exportDocumentById(vchunk,vexport,1,null,pc,null,null);
   retClob:=retClob||vchunk;
   end loop;
 return retClob;
 
 else 
   SELECT to_clob(SD_CONTENTS) into retClob FROM your_mds.MDS_STREAMED_DOCS WHERE SD_CONTENTID=pc AND SD_PARTITION_ID=pt;   
    return retClob;
 end if;
end;

create or replace view composite_list_all as

select "COMPOSITEXML","DN","STATE","CMODE","DEPLOYEDTIME" from  (

select

'/deployed-composites/'||replace(extractvalue(a.column_value,'/composite-revision/@dn'),'!','_rev')||'/composite.xml' compositexml,

extractvalue(a.column_value,'/composite-revision/@dn') dn,

extractvalue(a.column_value,'/composite-revision/@state') state,

extractvalue(a.column_value,'/composite-revision/@mode') cmode,

extractvalue(a.column_value,'/composite-revision/composite[1]/@deployedTime') deployedtime

 from

table(xmlsequence(extract(xmltype(get_composite_data('/deployed-composites/deployed-composites.xml')),'/deployed-composites/composite-series/composite-revision'))) a

);

create or replace view composite_list_default as

select

extractvalue(a.column_value,'/composite-series/@default') dn

 from

table(xmlsequence(extract(xmltype(get_composite_data('/deployed-composites/deployed-composites.xml')),'/deployed-composites/composite-series'))) a;

create or replace view composite_list as

select a.compositexml,a.dn,a.state,a.cmode,a.deployedtime,substr(a.dn,0,instr(a.dn,'/')-1) PARTITION_NAME,substr(a.dn,instr(a.dn,'/')+1,instr(a.dn,'!')-instr(a.dn,'/')-1) COMPOSITE_NAME,

substr(a.dn,instr(a.dn,'!')+1,length(a.dn)-instr(a.dn,'!')) COMPOSITE_REVISION,

decode(b.dn,null,'NO','YES') is_default from composite_list_all a,composite_list_default b

where

a.dn=b.dn(+);

declare
deployed xmltype;
n number;
vloop number:=0;
vmod number:=0;
cnt number;
tmpcnt number;
vscnt number:=0;
begin
select xmltype(get_composite_data('/deployed-composites/deployed-composites.xml')) into deployed from dual;
select nvl(max(id),0)+1 into n from tmp_Deployedxml;
insert into tmp_deployedxml(id,deployed_xml_org) values (n,deployed);
for x in (select * from composite_list where is_default!='YES')
  loop
    vloop:=vloop+1;
    dbms_application_info.set_client_info('deployedxml: '||vloop||'/'||vmod);
    cnt:=0;

    for comp in (select   
                extractvalue(a.column_value,'/component/@name','xmlns="http://xmlns.oracle.com/sca/1.0"') component_name
                from table(xmlsequence(extract(xmltype(get_composite_data(x.compositexml)),
                                                 '/composite/component',
                                                 'xmlns="http://xmlns.oracle.com/sca/1.0"')))a 
                                                 )
    loop
      select  count(1) into tmpcnt from your_soainfra.cube_instance where domain_name=x.partition_name and composite_name=x.composite_name
      and component_name=comp.component_name and composite_revision=x.composite_revision and state=1;
      cnt:=cnt+tmpcnt;
    end loop;
    if(cnt=0) then 

/* be sure that you dont have any waiting instance - you can comment this part and set vscnt to 0 if you don't update composite_revision column of running instances manually */
      select count(1)
        into vscnt
        from your_soainfra.dlv_subscription s
       where s.composite_name = x.composite_name
         and s.composite_revision = x.composite_revision
         and s.domain_name = x.partition_name and s.state=0;
if(vscnt=0) then
vmod:=vmod+1;
dbms_output.put_line(x.dn);
select deletexml(deployed,
'/deployed-composites/composite-series/composite-revision[@dn="'||x.dn||'"]') into deployed
from dual;
else
dbms_output.put_line('cant undeploy '||x.dn);
end if;
    end if;
  end loop;
update tmp_deployedxml set deployed_xml_mod=deployed,updated_time=sysdate where id=n;
dbms_output.put_line('select x.deployed_xml_mod.getclobval() from tmp_deployedxml x where x.id='||n);
commit;
dbms_output.put_line(vmod||' modified');
end;