So, even some statistics (unfortunately, not all -- the last time I checked in 11.2) are reported at the Service Level. Thus, I can see that the users of NEW_APP1 consumed more CPU and DB Time and generated more changes and redo than users of FINANCE ! (Obviously, V$SERVICE_STATS reports statistics from the beginning of the instance so you should either user StatsPack (I haven't verified StatsPack reporting of statistics by individual service) or AWR (if you have the Diagnostic Pack licence) or your own collection scripts to report statistics for a specific window of time).
.
.
.

The newly created user HEMANT belongs to the Database. The user is not tied to a Service. He can use either Service (whichever is running) to connect to the Database. But the two Services have two different TNSNAMES.ORA entries --- differing by the SERVICE_NAME specification.

Thus, the DBA could configure some application servers to use one service name and other application servers to use another service name.

An example of such a configuration is where the first set of application servers could be for Finance Applications called "FINANCE" and the second set of servers could be for HR Applications (in the *same* database) called "HR". Here I create the two services but start only the FINANCE service.

The user could connect to FINANCE but the request to HR returned ORA-12514.
(Notice how "tnsping HR" is successful but the connection is not ? That is because tnsping only tests if the listener is running, it does not test if the database instance and service are both running).

So, using multiple services, the DBA can "provision" the same database to multiple applications. The entry-point is the SERVICE_NAME, not the USERNAME. Users and Services are independent of each other.

14 June, 2016

It is generally accepted that service definition is required in RAC environments.

However, the concept of Services was made available in 8i --- predating RAC. Services can be defined in non-OPS / non-RAC / non-MultiTenant / MultiTenant environments. A single PDB in a 12c MultiTenant database can host multiple services.

A quick start to the implementation.

Note : srvctl is to be used to create and manage services in a RAC environment. srvctl registers the services with the Cluster Registry. In a Non-RAC environment, use DBMS_SERVICE.

If I ALTER the table to NOCOMPRESS (which can take effect only on *new* rows, not existing rows), I cannot DROP a column. This is because Oracle is unsure if there is a mix of Comressed and Non-Compressed rows in the table now.