Detecting if your database is multitenant

Just a quick tip for this post today. I had a question come in asking how to detect whether a database was running as a container database.  My quick response was to use SYS_CONTEXT(‘USERENV’,’CON_ID’) for which the values will be

• 0 – this database is not configured as a container database at all (tisk tisk )
• 1- this database is a container database and you are currently in the root
• >1 – this database is a container database and you are currently in a pluggable

However, the poster then responded with an additional complication – they were building installation scripts for their application which customers would run, and that some of those customers would be running version 11 or ever lower! (The list of problems with lingering on those old versions is a topic for another blog post!). But if you try use SYS_CONTEXT(‘USERENV’,’CON_ID’) before version 12 you’ll get the error “ORA-02003: invalid USERENV parameter”.

Rather than resort to unwieldy dynamic SQL, here’s a solution to that dilemma taking advantage of a feature that people still don’t use much even though its very useful – PL/SQL conditional compilation and if you click on that link, you’ll see that its been around for nearly 2 decades!

.

SQL> set serverout on
SQL> declare
  2    has_container varchar2(1);
  3    in_container  varchar2(1);
  4  begin
  5  $IF DBMS_DB_VERSION.VER_LE_11_2
  6  $THEN
  7    has_container := 'N';
  8    in_container  := 'N';
  9  $ELSE
 10      has_container := case when to_number(sys_context('USERENV','CON_ID')) = 0 then 'N' else 'Y' end;
 11      in_container  := case when to_number(sys_context('USERENV','CON_ID')) > 1 then 'Y' else 'N' end;
 12      if has_container = 'Y' and in_container = 'N' then
 13          for i in ( select name from v$pdbs )
 14          loop
 15            dbms_output.put_line(i.name);
 16          end loop;
 17      end if;
 18  $END
 19    dbms_output.put_line('has_container='||has_container);
 20    dbms_output.put_line('in_container='||in_container);
 21
 22  end;
 23  /
PDB$SEED
PDB1
PDB2
has_container=Y
in_container=N

PL/SQL procedure successfully completed.

No dynamic SQL needed and we even get to list the pluggables if we are in the root container.

TL;DR: When it comes to writing cross-version database code, conditional compilation is your friend.