The most recent posts in our series about Installing Sonar were focused on Oracle, and everything you need to know about this database to install a platform dedicated to code analysis with Sonar (and Jenkins, and Tomcat).

At the end of the post about Oracle installation, I told you to note the address of the administration console of Oracle.

Today we will use this console to create an Oracle User that will allow us to have a SONAR schema in our database.

If you did not keep the url of the Oracle console, no problem. You should have a Windows menu that allows to launch it.

Note that you have as many consoles as databases installed, and a corresponding Windows service. We shall see at the end of this article how to disable it.

Launching the Oracle console

You can use this menu to start the Oracle console and to get a login screen. We will use the login `SYSTEM ‘ (created during the creation of the database).

If you remember well, I used the same password ‘system’ as the username.

Once logged in, you will get into the homepage of your database instance.

We will not show all the screens and options of the console. In fact, we will use it only in order to create the Sonar schema.

Creating a tablespace with Oracle

Sonar needs a database schema to keep the results of the analysis and other information displayed on the dashboard (and the analysis parameters, the Quality profile with metrics configurations, etc.). For various reasons, including performance, we want to separate this schema from other objects in the database. It will be easier to optimize it if we assign it its own file on the hard disk.

This file, will store all the tables, indexes, and other objects for Sonar. It is called a tablespace.

The screen to manage tablespaces is accessible from the tab ‘Server’ in the console and the menu ‘Storage’.

A sub-menu ‘Tablespaces’ displays the list of existing tablespaces:

From this screen, we can create a new tablespace, using the button ‘Create’ in the top right of the screen.

In this creation window, we will enter the following infos:

• The name of the tablespace file, with a .DBF (DataBase File) extension: SONAR.DBF.
• The directory that will contain this file (always choose the same directory).
• The original size of this file. Note that we start with 2 GB directly.
• Very important: your tablespace must be ‘AUTOEXTENDED’, that is, capable to increase its size when the previous limit has been reached. And you must specify the increment, to say how many MB this file will be increased when necessary.

Some recommendations:

• You will wish to centralize the .Dbf files in the same directory or on the same hard drive. If you want to perform many analyzes for a large number of applications with an high size of code, a dedicated server may be necessary. Use a hard drive C:\ on which you install the softwares, and for which the size and powermess are not critical, and a second hard drive larger and faster for your tablespaces and the location of the code to analyze.
• To increment the tablespace file when the initial size is exceeded is an expensive operation in terms of performance. I always replace the proposed default value, often too low, to an increment of approximately 10% of the original size. Oracle will not spend too much time to extend the tablespace at the expense of the performance of the database.

The SQL command corresponding to this tablespace creation is the following:

Once the tablespace is created, we can see it in the previous list:

Note that I also increased the size of the tablespace ‘UNDOTBS1’. This tablespace is reserved to the ‘rollback’ file. Without going into details, the correct size for this tablespace is an important factor for the performance of your database.

And do not forget: if this tablespace ‘UNDO’ or one dedicated to Sonar is not ‘AUTOEXTEND’, you will certainly get an error message sooner or later, during code analysis. Situation that I encounter regularly among new users.

Creating a SONAR user with Oracle

Creating an Oracle schema means creating an user, the owner of the schema, with certain rights and privileges that we will attribute.

Again in the ‘Server’ tab of the Oracle console, locate the menu ‘Security’ and the sub-menu ‘User’.

In the screen that lists the different users, locate the button ‘Create’ at the top right of the screen. In the window for creating a new user, we will enter the following data:

• The name of the user ‘SONAR’ (which will be also the name of our schema).
• A password: choose ‘sonar’, that’s the most simple. Confirm the password by entering it a second time.
• We choose the tablespace for this user: obviously the tablespace ‘SONAR’ that we created earlier.
• There is also a temporary tablespace to choose: leave the ‘TEMP’ default option.

Wait, this is not finished. We must assign to that user the rights to create objects (tables, indexes, sequences, etc.) for the SONAR schema.

In the ‘Roles’ tab of this screen, select the role ‘DBA’ in the left list and pass it to the right list with the aid of the ‘>’ button. The role ‘CONNECT’ is a user with read privileges. We need DBA rights to be able to manage the objects in the database.

The button ‘Accept’ allows us to confirm the creation of our user SONAR. Here are the corresponding SQL commands:

In the screen that lists existing users, veirfy that our user has been correctly created.

Voilá. We can get out of the console.

Deactivate the Oracle console

One last thing: I recommend you to disable the Oracle console. It has the tendency to launch all sorts of uncontrolled operations on the database, usually to update statistics or check the health of database objects, This is extremely power hungry. Your PC will slow to a complete stop, and of course at the most undesirable time. What’s more annoying than a machine that takes 10 minutes to start or that interrupts a super important presentation to your boss or your best customer because your PC becomes uncontrollable.

Now that we have created our tablespace and the SONAR user, we no longer need the console. In fact, we’re done with Oracle, and except in case of an incident, we will no longer need to access the console.

To disable it, open the ‘Services’ window and find the corresponding service.

In the ‘General’ tab, choose “Disabled” in order not to run this service when starting you PC.

The ‘Stop’ button will also allow you to stop it.

 

This article is the latest on installing Oracle: you now have the knowledge needed to manage the database for your code analysis platform. This may look complex the first time you practice it, but this is not something that you will use every day, so don’t worry.

The next post will be dedicated to the installation of SONAR. Finally!

 

This post is also available in Leer este articulo en castellano and Lire cet article en français.