Trigger to Startup Pluggable Databases and Services
Jump to navigation
Jump to search
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; /