12c Statspack Hack

Starting from a comment on an old statspack/AWR page, with a near-simultaneous thread appearing on OTN, (do read both) here’s a quick summary of getting statspack onto 12c with containers. (For non-container databases it’s a standard install).

Option 1 – completely valid

At present, you have to install the perfstat account on a pluggable database if you want to do it legally. On the plus side this means you could install it once then clone, unplug, and re-plug it elsewhere – though you might have to play around each time enabling a new statspack job.

Option 2 – slightly suspect

You could edit the install and report scripts to change explicit references to the perfstat schemaname to be c##perfstat instead and then install it on cdb$root.

Option 3 – definitely naughty [updated – see comments, especially #3]

There is a hidden parameter which defines the “common user prefix” (_common_user_prefix) as “c##”. You could set this to null in your parameter file, bounce the database, install statspack in cdb$root, remove the parameter from the parameter file, and bounce the database again.

Warning – if you do this you will have to set the parameter to true if you ever want to drop the schema, so make sure you’ve documented what you did and what you will have to do.

There is an easier, naughty option (see comments).  Edit the spcusr.sql script to delete the call to set “container=current”, and execute the “alter session” statement from the spcreate.sql script that sets “_oracle_script” to true before running spcreate.sql (Alternatively, move the “alter session” statement to a point in the file before the call to @@spcusr.sql).

Option 4 – production strategy

Wait for Oracle to modify the statspack install procedure to make a cdb$root install legally possible. (I suspect there was an “official” hack at some point given that “alter session” line in the spcreate.sql script.)