Database Reference
In-Depth Information
Since Oracle won't automatically open PDBs, a little bit of code in combination with a startup trigger can help in
this situation. Consider the following sample:
SQL> create table pdbtab tablespace users
2 as select name, 'YES' autostart, 'RW' openmode
4 from v$pdbs
3 where name <> 'PDB$SEED';
Table created
SQL> alter table pdbtab add constraint c_autostart
2 check (autostart in ('YES','NO'));
Table altered
SQL> alter table pdbtab add constraint c_openmode
2 check (openmode in ('RO','RW'));
Table altered.
With the “pdbtab” table in place, it is possible to define a trigger that fires after the database has been started. It is
possible to read the list of PDBs in the table “pdbtab”, and execute a piece of dynamic SQL to open the PDB according
to the requested open mode. A possible trigger could be written like this one:
SQL> create or replace trigger autoPDBtrig
2 after startup on database
3 declare
4 v_role v_$database.database_role%type;
5 begin
6 select database_role into v_role from v$database;
7 if v_role = 'PRIMARY' then
8 for i in (select name,openmode from pdbtab a where exists
9 (select 1 from v$pdbs b where b.name = a.name)
10 and autostart='YES')
11 loop
12 execute immediate 'alter pluggable database ' ||
13 i.name || ' open ' || case when i.openmode = 'RO'
14 then 'read only' end;
15 end loop;
16 end if;
17* end;
SQL> /
Trigger created.
The trigger first checks if the database is in the primary role. If not then PDBs should probably not be opened.
Following this verification the code traverses all the entries in PDBTAB for PDBs that exist in the CDB, with the intention
of opening them accordingly. This way you have all your PDBs open when the CDB started in a user-defined fashion.
You need to add an entry to the pdbtab table after deploying a new PDB or it will not be taken into consideration during
the start of the CDB.
Search WWH ::




Custom Search