Making existing SQLPLUS Scripts 12c and Container DB (PDB) compatible

by Simon Pane on May 26, 2015 12:00:00 AM

Oracle 12c introduces new catalog features including CDB_ dictionary views (which include a CON_ID column) superseding the DBA_ views that most DBA sqlplus scripts are based upon. However, existing DBA sqlplus scripts can easily be modified using just a few simple sqlplus techniques to be compatible with 11g, as well as all types of 12c databases including legacy and container databases. The following simple SQL and sqlplus techniques can be used to make a "universal script" that is compatible with all versions.

Illustrating the Issue

Let's say for sake of example that we have a simple 10g/11g monitoring script that's checking the amount of freespace in each tablespace by querying the DBA_TABLESPACE_USAGE_METRICS view. On our 10g or 11g database the following query gives the necessary information:

SQL> select version from v$instance;
 
 VERSION
 -----------------
 11.2.0.4.0
 
 SQL> select tablespace_name, tablespace_size, used_percent
  2 from DBA_TABLESPACE_USAGE_METRICS
  3 order by tablespace_name;
 
 TABLESPACE_NAME TABLESPACE_SIZE USED_PERCENT
 ------------------------------ --------------- ------------
 FCCDEV 256000 .053125
 SYSAUX 1024000 31.0617188
 SYSTEM 1024000 9.19453125
 TEMP 1024000 0
 UNDOTBS1 1024000 .015625
 USERS 256000 1.275
 
 6 rows selected.
 
 SQL>

Now will the same query work on a 12c database? Of course it will:

SQL> select version from v$instance;
 
 VERSION
 -----------------
 12.1.0.2.0
 
 SQL> select tablespace_name, tablespace_size, used_percent
  2 from DBA_TABLESPACE_USAGE_METRICS
  3 order by tablespace_name;
 
 TABLESPACE_NAME TABLESPACE_SIZE USED_PERCENT
 ------------------------------ --------------- ------------
 SYSAUX 4194302 .773048769
 SYSTEM 4194302 1.05991414
 TEMP 4194302 0
 UNDOTBS1 4194302 .031280532
 USERS 4194302 .003051759
 
 SQL>

It executes successfully on the 12c database but there's a problem: the query is only returning the data from the root container (or more accurately, from the container in which the statement was executed). The PDB data is missing, I have both open and closed PDBs in this database:

SQL> select con_id, name, open_mode from V$CONTAINERS order by con_id;
 
  CON_ID NAME OPEN_MODE
 ---------- ------------------------------ ----------
  1 CDB$ROOT READ WRITE
  2 PDB$SEED READ ONLY
  3 TEST1 READ WRITE
  4 LDB3 MOUNTED
 
 SQL>

The LDB3 PDB is closed (mounted) so I'm not interested in monitoring the tablespace freespace in it but I am interested in the details from the opened TEST1 PDB. To get the required information we need to make two or three (Third being optional) changes:

1) Change the view from DBA_ to CDB_ 2) Add the CON_ID column to the output 3) Add the CON_ID column to the ORDER BY clause

Hence (executing from CDB$ROOT) the query becomes:

SQL> select con_id, tablespace_name, tablespace_size, used_percent
  2 from CDB_TABLESPACE_USAGE_METRICS
  3 order by con_id, tablespace_name;
 
  CON_ID TABLESPACE_NAME TABLESPACE_SIZE USED_PERCENT
 ---------- ------------------------------ --------------- ------------
  1 SYSAUX 4194302 .773048769
  1 SYSTEM 4194302 1.05991414
  1 TEMP 4194302 0
  1 UNDOTBS1 4194302 .031280532
  1 USERS 4194302 .003051759
  3 AUDIT_DATA 64000 .2875
  3 SYSAUX 4194302 .410843091
  3 SYSTEM 4194302 .474167096
  3 TPCCTAB 1024000 5.63203125
 
 9 rows selected.
 
 SQL>

So that works fine, but as it stands we have two versions of the query and therefore we need two monitoring scripts.

Building Blocks for the Universal Script

Applying a number of simple sqlplus techniques can help us with this and will allow us to make the single universal version of the sqlplus script. 1) Use a SQLPLUS variable: The sqlplus DEFINE command allows us to define variables. We can easily define a variable that tells us which view prefix to use depending on whether the database version is 11g or 12c.

SQL> COLUMN view_prefix NEW_VALUE view_prefix
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','CDB','DBA') view_prefix FROM v$instance;
 
 VIE
 ---
 CDB
 
 SQL>

2) Dynamically build the view name: The second tip is that in sqlplus to concatenate a variable with a string a period must be used to show where the variable name ends:

SQL> prompt &view_prefix
 CDB
 
 SQL> prompt &view_prefix._TABLESPACE_USAGE_METRICS
 CDB_TABLESPACE_USAGE_METRICS
 
 SQL>

Plugging that into the original query gives:

SQL> select tablespace_name, tablespace_size, used_percent
  2 from &view_prefix._TABLESPACE_USAGE_METRICS
  3 order by tablespace_name;
 old 2: from &view_prefix._TABLESPACE_USAGE_METRICS
 new 2: from CDB_TABLESPACE_USAGE_METRICS
 
 TABLESPACE_NAME TABLESPACE_SIZE USED_PERCENT
 ------------------------------ --------------- ------------
 AUDIT_DATA 64000 .2875
 SYSAUX 4194302 .410843091
 SYSAUX 4194302 .773048769
 SYSTEM 4194302 1.05991414
 SYSTEM 4194302 .474167096
 TEMP 4194302 0
 TPCCTAB 1024000 5.63203125
 UNDOTBS1 4194302 .031280532
 USERS 4194302 .003051759
 
 9 rows selected.
 
 SQL>

But we're missing the container ID column. 3) Add columns dynamically using additional sqlplus variables: We can "optionally" include columns such as the CON_ID column using the same technique:

SQL> COLUMN view_prefix NEW_VALUE view_prefix NOPRINT
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','CDB','DBA') view_prefix FROM v$instance;
 
 SQL> COLUMN con_id_col NEW_VALUE con_id_col NOPRINT
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','con_id,','') con_id_col FROM v$instance;
 
 SQL> select &con_id_col tablespace_name, tablespace_size, used_percent
  2 from &view_prefix._TABLESPACE_USAGE_METRICS
  3 order by &con_id_col tablespace_name;
 old 1: select &con_id_col tablespace_name, tablespace_size, used_percent
 new 1: select con_id, tablespace_name, tablespace_size, used_percent
 old 2: from &view_prefix._TABLESPACE_USAGE_METRICS
 new 2: from CDB_TABLESPACE_USAGE_METRICS
 old 3: order by &con_id_col tablespace_name
 new 3: order by con_id, tablespace_name
 
  CON_ID TABLESPACE_NAME TABLESPACE_SIZE USED_PERCENT
 ---------- ------------------------------ --------------- ------------
  1 SYSAUX 4194302 .773239504
  1 SYSTEM 4194302 1.05991414
  1 TEMP 4194302 0
  1 UNDOTBS1 4194302 .003814699
  1 USERS 4194302 .003051759
  3 AUDIT_DATA 64000 .2875
  3 SYSAUX 4194302 .410843091
  3 SYSTEM 4194302 .474167096
  3 TPCCTAB 1024000 5.63203125
 
 9 rows selected.
 
 SQL>

Note that the comma is in the variable and not in the column list in the SQL SELECT or ORDER BY clauses. The script is now dynamically determining whether to use the CDB_ or DBA_ view and similarly dynamically adding the CON_ID column to the SELECT and ORDER BY clauses. (And of course should be executed from the root container.) And the exact same script still works on the 11g database using the 11g version of sqlplus! Similarly the optional column (including the comma) defined in the sqlplus variable could be used in an aggregation GROUP BY clause. However, if the query has no other aggregation columns then we might need to add a constant to the GROUP BY clause (and ORDER BY), otherwise the GROUP BY would have no columns listed and the universal sqlplus script is executed against an 11g database. For example:

SQL> COLUMN view_prefix NEW_VALUE view_prefix NOPRINT
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','CDB','DBA') view_prefix FROM v$instance;
 
 SQL> COLUMN con_id_col NEW_VALUE con_id_col NOPRINT
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','con_id,','') con_id_col FROM v$instance;
 
 SQL> select &con_id_col min(extended_timestamp), max(extended_timestamp)
  2 from &view_prefix._AUDIT_TRAIL
  3 group by &con_id_col 1 order by &con_id_col 1;
 old 1: select &con_id_col min(extended_timestamp), max(extended_timestamp)
 new 1: select con_id, min(extended_timestamp), max(extended_timestamp)
 old 2: from &view_prefix._AUDIT_TRAIL
 new 2: from CDB_AUDIT_TRAIL
 old 3: group by &con_id_col 1 order by &con_id_col 1
 new 3: group by con_id, 1 order by con_id, 1
 
  CON_ID MIN(EXTENDED_TIMESTAMP) MAX(EXTENDED_TIMESTAMP)
 ---------- ---------------------------------------- ----------------------------------------
  3 13-MAY-15 11.54.52.106301 AM -06:00 13-MAY-15 12.16.18.941308 PM -06:00
 
 SQL>

Finally, once we're done testing and debugging, we can get rid of the ugly "old" and "new" statements using:

SET VERIFY OFF

Implementing these techniques will allow modifications of most existing DBA sqlplus scripts to create universal versions, of which will be compatible with 11g (and likely earlier) databases as well as 12c legacy and container databases.

Deeper Dive

What if our monitoring query is based on an underlying catalog table and not a dictionary view? For example, let's say that our objective is to report on users and the last time the database password was changed. The password change date isn't presented in the DBA_USERS or CDB_USERS view, but it is in the underlying SYS.USER$ table. Hence the monitoring query might be something like:

SQL> select name, ptime from SYS.USER$
  2 where type#=1 order by name;
 
 NAME PTIME
 ------------------------ ---------
 ANONYMOUS 23-APR-15
 ...
 SYSTEM 23-APR-15
 XDB 23-APR-15
 XS$NULL 23-APR-15

If we look at the view definition of any of the CDB_ views it is apparent that the view traverses the open PDBs by using the new 12c "CONTAINERS" function which accepts a table name as the only argument. When run from the root container the CONTAINERS() function will traverse all open PDBs (assuming the common user used has local PDB permission to access the referenced table). NOTE: Prior to 12.1.0.2 the CONTAINERS function was called CDB$VIEW. Thus, we can use the new function as follows:

SQL> select con_id, name, ptime from CONTAINERS(SYS.USER$)
  2 where type#=1 order by con_id, name;
 
  CON_ID NAME PTIME
 ---------- ------------------------ ---------
  1 ANONYMOUS 23-APR-15
 ...
  1 SYSTEM 23-APR-15
  1 XDB 23-APR-15
  1 XS$NULL 23-APR-15
  3 ANONYMOUS 23-APR-15
 ...
  3 SYSTEM 23-APR-15
  3 XDB 23-APR-15
  3 XS$NULL 23-APR-15

Or to make the script universal so the single script can be run on both 11g and 12c:

SQL> COLUMN view_prefix NEW_VALUE view_prefix NOPRINT
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','CONTAINERS(SYS.USER$)','SYS.USER$') view_prefix FROM v$instance;
 
 SQL> COLUMN con_id_col NEW_VALUE con_id_col NOPRINT
 SQL> SELECT DECODE(SUBSTR(version,1,INSTR(version,'.')-1),'12','con_id,','') con_id_col FROM v$instance;
 
 SQL> select &con_id_col name, ptime from &view_prefix.
  2 where type#=1 order by &con_id_col name;
 old 1: select &con_id_col name, ptime from &view_prefix.
 new 1: select con_id, name, ptime from CONTAINERS(SYS.USER$)
 old 2: where type#=1 order by &con_id_col name
 new 2: where type#=1 order by con_id, name
 
  CON_ID NAME PTIME
 ---------- ------------------------ ---------
  1 ANONYMOUS 23-APR-15
 ...
  1 XDB 23-APR-15
  1 XS$NULL 23-APR-15
  3 ANONYMOUS 23-APR-15
 ...
  3 XDB 23-APR-15
  3 XS$NULL 23-APR-15
 
 SQL>

A final question might be: why isn't the PDB$SEED database shown in the results? The answer is that a new 12c initialization parameter EXCLUDE_SEED_CDB_VIEW controls whether the seed database is displayed in CDB_ view (or CONTAINERS() function calls). EXCLUDE_SEED_CDB_VIEW is dynamic and session modifiable:

SQL> show parameter EXCLUDE_SEED_CDB_VIEW
 
 NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 exclude_seed_cdb_view boolean TRUE
 
 SQL> select con_id, count(1) from cdb_users group by con_id;
 
  CON_ID COUNT(1)
 ---------- ----------
  1 18
  3 20
 
 SQL> alter session set EXCLUDE_SEED_CDB_VIEW=FALSE;
 
 Session altered.
 
 SQL> select con_id, count(1) from cdb_users group by con_id;
 
  CON_ID COUNT(1)
 ---------- ----------
  1 18
  2 17
  3 20
 
 SQL>

Other tools

A final question is whether this technique will still work if the SQL script is run through other tools? The answer is: " it depends". It depends on whether the other tools support the "define" command and the use of script variables. Specifically, Oracle SQL Developer and the newer sqlcl tool does. The above examples work fine in SQL Developer and sqlcl using the standard sqlcl "default" sqlformat. Other sqlformat options in sqlcl show some issues (testing with sqlcl version 4.2.0.15.121.1046). Learn more about Pythian’s expertise in Oracle and SQL Server.

Topics: Microsoft SQL Server Technical Track Pythian Distribution Agent Transactional Replication

No Comments Yet

Let us know what you think

Share this