Trigger to Startup Pluggable Databases and Services

The following trigger starts up all pluggable databases once the container database has been started and calls a stored procedure which starts up services which have been set up for the pluggable databases.

The Trigger

CREATE OR REPLACE TRIGGER SYS.OPEN_ALL_PLUGGABLES
   after startup
   on  database
BEGIN
   execute immediate 'alter pluggable database all open';
   sys.start_pdb_service_proc;
END open_all_pdbs;
/

The Stored Procedure

CREATE OR REPLACE PROCEDURE SYS.START_PDB_SERVICE_PROC IS


v_sql1 varchar2(32000);
v_sql2 varchar2(32000):= 'alter session set container=CDB$ROOT';

cursor serv is
select PDB
     , NAME
  from v$services
 where PDB not in ('CDB$ROOT');

begin

dbms_output.enable(100000);

for i in serv loop

 begin


   dbms_output.put_line(i.name || ' ' || i.pdb);

    v_sql1 := 'alter session set container=' || i.pdb || '';

    execute immediate v_sql1;
    dbms_service.start_service(i.name);

   exception
   when others then
   dbms_output.put_line(sqlerrm);

end;

end loop;


  execute immediate v_sql2;

exception
when others then
dbms_output.put_line(sqlerrm);
end;
/