You are here: Foswiki>Service Web>InstallCommons (08 Apr 2015, SebastianMueller)Edit Attach

What is Commons library?

The Commons library is a tool library from CERN they call it Commons 4 Oracle. The tools includes thinks like logger, notifier, schema analyser, and more. For example, with the setup notification it is possible to send emails to registered contributor. But more information about that, later on.


Create a new user "COMMONS" on the db. This information is for the databank admins. With following rights:


This following information is for FESA30, it works also for LSA.


grant execute on utl_mail to fesa30;

grant EXECUTE on commons.COM_CTX_MGR to fesa30;


DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'FESA30',

is_grant => true,

privilege => 'connect');



grant create job to FESA30;

grant create any job to FESA30;

Overview (from CERN-WIKI):

Structure Description
commons/ Top level directory
change_log.txt A time ordered list of all changes (what, by whom)
com_<component-name>/ A directory containing the package spec (.pls) and body (.plb) of the
named Commons 4 Oracle component (i.e. this structure is repeated
as many times as their are distinct components)
com_<component-name>.pls The package spec (public interface)
com_<component-name>.plb The package body (private implementation)
install_in_commons.sql The script to called to install Commons 4 Oracle, for the database
user COMMONS, which subsequently calls detailed scripts in the ins
tall directory. This should be run, before trying to use the install
.sql script to install Commons 4 Oracle for other database users on
the same machine.
install.sql The script to called to install Commons 4 Oracle, which subsequently
calls detailed scripts in the install directory. Before running this,
ensure you have created a database user called COMMONS on
the database, and installed Commons 4 Oracle in that schema,
using the install_in_commons.sql script.
install/ Directory containing detailed installation scripts
configuration_data.sql Contains the basic configuration data required to install and run
commons e.g. internal event definitions
define_admins.sql Template script to define administrators of the system where
commons will be deployed. Edit this before installation for your
environment, if necessary.
define_notifications.sql Template script to define default notifications within the system where
commons will be deployed
packages.sql Script which calls the .pls and .plb packages in each of the com_<co
mponent-name> directories
pre_install_checks.sql Script to check that the installation user has the necessary privileges
required to install Commons 4 Oracle. Note: If the database user
for which you are trying to install commons does not pass these
checks (i.e. missing privileges) - the installation will fail.
principal_objects.sql Script which creates the underlying required objects e.g. tables,
sequences etc.
reg_part_management.sql Script which registers the details of time range partitioned tables
which will be managed by the com_partition_mgr, e.g. com_logs
table. This includes partition sizes, and how long to keep old
partitions for
scheduler_jobs.sql Script which is submits the scheduler jobs to perform automated
tasks, such as daily reporting, job monitoring, and partition
update/ Directory containing detailed updated scripts, which depend on the
currently installed version of commons.
update.sql Script to update the currently installed version of commons, to the
next available version (if any) of commons. This tries to resolve the
installed version, and then calls the appropriate update script in the
update directory. It may be necessary to run this multiple times if
you have an old version of commons, and several updates have
been provided since your current version.
update_packages_only.sql Script to deploy the latest package spec and body code. Sometimes
there are fixes or extensions to commons which only affect the
PL/SQL code, and therefore which do not need a special update
script to be run - you can simply replace the Commons 4 Oracle
packages, and then re-compile your dependent objects.

First steps:

Checkout the commons-script from CERN-SVN

Run the script "install_in_commons.sql" with the commons user.

After this step make sure of the rights of the user. To try this seperate run the script "pre_install_checks.sql", this script will run automatically when the "install.sql" runs.

One bug are in the scripts.

1. Goto the file "configuration_data.sql" and comment this last rows without the last "commit;" !!

The table com_schema_analysis_types doesn't exist and it drops into the COMMONS update 1.6.0 to 1.7.0

-- com_object_mgr --> values for com_schema_analysis_types look-up table
insert into com_schema_analysis_types(analysis_type_id,analysis_type_name,analysis_type_description)
values(com_schema_analysis_type_seq.nextval,'MISSING_FK_INDEX_CHECK','Check if there are foreign keys not indexed.');

insert into com_schema_analysis_types(analysis_type_id,analysis_type_name,analysis_type_description)
values (com_schema_analysis_type_seq.nextval,'MISSING_PRIMARY_KEY_CHECK','Check if there are tables without primary key defined yet');

insert into com_schema_analysis_types(analysis_type_id,analysis_type_name,analysis_type_description)
values(com_schema_analysis_type_seq.nextval,'INVALID_CONSTRAINTS_CHECK','Check if there are invalid constraints.');

insert into com_schema_analysis_types(analysis_type_id,analysis_type_name,analysis_type_description)
values(com_schema_analysis_type_seq.nextval,'DISABLED_CONSTRAINTS_CHECK','Check if there are disabled constraints.');

insert into com_schema_analysis_types(analysis_type_id,analysis_type_name,analysis_type_description)
values(com_schema_analysis_type_seq.nextval,'INVALID_INDEX_CHECK','Check if there are invalid indexes.');

insert into com_schema_analysis_types(analysis_type_id,analysis_type_name,analysis_type_description)
values(com_schema_analysis_type_seq.nextval,'INVALID_VIEW_CHECK','Check if there are invalid views.');

The problems are under following issues to find // a CERN-Account is required

After these steps it is important that the following files are adapted.


In this file stands the contact data for the notifications


In this file stands an example for a notification trigger


When the steps above are complete, the "install.sql" script can be started.

After these steps the Common 4 Oracle Library is successful installed.


The updated script can be started by the "update.sql" script.

It is possible to start differently update process like:




But this scripts depend on changes on the database.

-- SebastianMueller - 14 Oct 2014
Topic revision: r4 - 08 Apr 2015, SebastianMueller
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback