More on how to display the current container in a multitenant database

My listener is listening for connections to a service called "myservice1", which runs out of the pluggable database "pdb1":

lsnrctl status
Service "pdb1" has 1 instance(s). <-- the default service for the pluggable database
  Instance "cdb", status READY, has 1 handler(s) for this service...

Service "myservice1" has 1 instance(s). <-- service defined by the DBA
  Instance "cdb", status READY, has 1 handler(s) for this service...

In the code snippet below, I am executing a script called disp_session.sql which will

connecting directly to a service served by the listener

switching schema within my session

The following will display the username, current schema, and the service my connction is connecting to:

alter session set current_schema=scott;
set lines 200
col service format a20
col container format a20
col username format a20
col "container ID" format a20
col schemaname format a20
select sys_context ('userenv','SERVICE_NAME')   service,
       sys_context ('userenv','CON_NAME')       container,
       sys_context ('userenv','CON_ID')         "container ID",
       sys_context ('userenv','CURRENT_USER')   username,
       sys_context ('userenv','CURRENT_SCHEMA') schemaname
from dual;

oracle@server1.oric.no:[cdb]# sqlplus system@myservice1 @disp_session.sql

Session altered.

SERVICE              CONTAINER            container ID         USERNAME             SCHEMANAME
-------------------- -------------------- -------------------- -------------------- --------------------
myservice1           pdb1                 4                    SYSTEM               SCOTT

Useful information when debugging client connections ;-)

How to modify a unified auditing policy to make exceptions based on login information

The audit policy had been created as follows:

create audit policy all_select_policy actions select;

Here is how you can alter an existing policy so that the policy will make an exception for session created by user "DBAADMIN"

alter audit policy ALL_SELECT_POLICY 
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBAADMIN'')' 
evaluate per Session;

Documentation can be found here
The oracle-supplied policy ORA_LOGIN_FAILURES automatically audits all failed login attempts. You can alter it to exclude certain uninteresting connections, such as for example DBSNMP, like this:

alter audit policy ORA_LOGON_FAILURES
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBSNMP'')'
evaluate per session;

For more information about the SYS_CONTEXT function, check the official 12.2 documentation.

How to create a proxy authenticated user in Oracle

Create a general user for authentication:

CREATE USER APP_POOL
IDENTIFIED BY app_pool_users123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO APP_POOL;

To illustrate how proxy authentication can be used efficiently, I create two roles:

create role app_pool_role1 not identified;
create role app_pool_role2 not identified;

Grant object privileges on two different tables to the two new roles:

grant select on SCOTT.DEPT to app_pool_role1;
grant select on SCOTT.EMP to app_pool_role2;

Create a user:

CREATE USER VEGARD
IDENTIFIED BY vegard123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:

GRANT CREATE SESSION TO VEGARD;
GRANT APP_POOL_ROLE1 TO VEGARD;
GRANT APP_POOL_ROLE2 TO VEGARD;
ALTER USER VEGARD DEFAULT ROLE ALL;

Change the user so it connects through a proxy user.
Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:

ALTER USER VEGARD
GRANT CONNECT THROUGH APP_POOL
WITH ROLE APP_POOL_ROLE1;

Let's connect to the database:

sqlplus app_pool[vegard]/app_pool_users123@pdb01

Verify the connection and session settings by using sys_context:

select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
       decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
       sys_context('USERENV','CURRENT_SCHEMA') "current schema",
       sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
       decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
       sys_context('USERENV','PROXY_USER') "proxy user"
from dual;

Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:

AuthIdentity used	auth method	current schema	grantee of privileges used	identification type	proxy user
VEGARD	proxy	VEGARD	VEGARD	Password	APP_POOL

Which roles are enabled?

select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
from dual
union
select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
from dual
;

Result shows that only APP_POOL_ROLE1 is enabled:

Role granted?
APP_POOL_ROLE1: TRUE
APP_POOL_ROLE2: FALSE

Let's verify that the roles are actually working.
Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:

VEGARD@pdb01 SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


VEGARD@pdb01 SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

There is nothing preventing the user VEGARD from connecting directly to the database:

sqlplus vegard/vegard123@pdb01
VEGARD@pdb01 SQL> show user
USER is "VEGARD"

When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:

VEGARD@vegdb01 SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

Some advantages with using proxy connections:

* No more password administration for individual users
* Proxy authentication allows for role based access control
* Identity preservation of the real user behind the proxy user is automatically set up

Keep in mind that you still have to create and provision the end user accounts.

How to use dbms_session to set client_identifier in your session

Logged in as user scott, I set the following in my session:

EXEC DBMS_SESSION.SET_IDENTIFIER (client_id=>'vegardk');

Verify that your session info is set:

SELECT SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) "client identifier", SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) "current user" 
FROM DUAL;

client identifier	current user
vegardk	SCOTT

The same information can be found in v$session, columnn CLIENT_IDENTIFIER.

There is also another column in v$session called CLIENT_INFO.
A value for this column can be generated by calling a different procedure, dbms_application_info.set_client_info.

For example:

exec dbms_application_info.set_client_info('Execute summary');

Check v$session:

SELECT USERNAME,CLIENT_IDENTIFIER,CLIENT_INFO
FROM V$SESSION 
WHERE USERNAME='SCOTT';

Result:

USERNAME	CLIENT_IDENTIFIER	CLIENT_INFO
SCOTT	vegardk	Execute summary

A good way to pick up up any relevant session information already set is to use NVL:

SELECT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) 
FROM DUAL;

If client_identifier is set, we pick up that value. If not, we use the current_user, which is always set.
This could also be used as a default value for a column definition:

  CREATE TABLE T1 (
  ...
  LAST_CHANGED_BY     VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL
 );

How to create a range-list partitioned table, using interval partitioning and subpartition template

Note: I am placing the subpartitions, the initial partition, the table definition itself, and the LOB segments, in different tablespaces simply to illustrate this possiblity.

CREATE TABLE COUNTRY_TABLE
(
  ID                  VARCHAR2(36 CHAR)         NULL,
  DYEAR               NUMBER(4)                 NULL,
  COUNTRY             VARCHAR2(50 CHAR)         NULL,
  CREATED             TIMESTAMP(6)              DEFAULT systimestamp  NULL,
  CREATED_BY          VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL,
  LAST_CHANGED        TIMESTAMP(6)              DEFAULT systimestamp  NULL,
  LAST_CHANGED_BY     VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL,
  VERSION             NUMBER(5)                 DEFAULT 1 NULL,
  DOCUMENT            CLOB                      NULL,
  PERIOD AS (
                CAST ( CREATED AS DATE)
             ) VIRTUAL
)
LOB (DOCUMENT) STORE AS SECUREFILE (
 TABLESPACE  scott_data
)
PARTITION BY RANGE(PERIOD) INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') )
SUBPARTITION BY LIST (COUNTRY)
 SUBPARTITION TEMPLATE
(
 SUBPARTITION SP_GERMAN VALUES ('GERMANY','SWITZERLAND','AUSTRIA') TABLESPACE USERS
,SUBPARTITION SP_FRENCH VALUES ('FRANCE','BELGIUM','LUXENBOURGH')  TABLESPACE SCOTT_DATA
,SUBPARTITION SP_SCANDI VALUES ('NORWAY','SWEDEN','DENMARK') TABLESPACE USERS
,SUBPARTITION SP_ENGLISH VALUES ('ENGLAND', 'WALES', 'SCOTLAND','IRELAND') TABLESPACE SCOTT_DATA
,SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE USERS
)
(
PARTITION PRE_2018 VALUES LESS THAN (TO_DATE('2018-01','YYYY-MM') ) TABLESPACE SCOTT_DATA
)
TABLESPACE USERS
ROW STORE COMPRESS ADVANCED
;

Analyze the table quickly;

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'SCOTT', TABNAME=>'COUNTRY_TABLE',GRANULARITY=>'AUTO');

After creation, the DD confirms the default partition with 5 subpartitions were created:

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
PRE_2018	PRE_2018_SP_GERMAN	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_FRENCH	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_SCANDI	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_ENGLISH	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_DEFAULT	0	07.03.2019 10:41:21

Let's insert an "old" record, which sets the column CREATED to 2016-01:

INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2017,'FRANCE',1, TO_TIMESTAMP('2016-01','yyyy-mm') );

Analyze the table with dbms_stats again, and we see that the PRE_2018 subpartition for countries with French language, now has one row:

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
PRE_2018	PRE_2018_SP_GERMAN	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_FRENCH	1	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_SCANDI	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_ENGLISH	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_DEFAULT	0	07.03.2019 10:42:20

The rows was inserted into the PRE_2018_SP_FRENCH subpartition, as expected.

Now, let's insert a value which is greater than 2018-01, which I specified as the high value for my initial partition, called "PRE_2018":

-- Setting the created column to January 2ond, 2018:
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-01-02','YYYY-MM-DD') );
COMMIT;

This creates 4 new subpartitions with system generated names, with one row inserted into one of these:

   
PARTITION_NAME
   
SUBPARTITION_NAME
   
NUM_ROWS
   
LAST_ANALYZED
   
    
SYS_P2187
     
SYS_SUBP2182
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2183
     
1
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2184
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2185
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2186
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_GERMAN
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_FRENCH
     
1
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_SCANDI
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_ENGLISH
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_DEFAULT
     
0
     
07.03.2019 10:56:32
   

Another row for January 2018 will go into the same subpartition:

-- Setting created to January 3rd, 2018:
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-01-03','YYYY-MM-DD') );

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
SYS_P2187	SYS_SUBP2183	2	07.03.2019 10:59:06

When inserting rows for a new month, addition 4 new subpartitions will be created, etc:

-- February 1st, 2018
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-02-01','YYYY-MM-DD') );

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
SYS_P2205	SYS_SUBP2200	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2201	1	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2202	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2203	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2204	0	07.03.2019 11:00:47

Display the current locks in a tree-like fashion

Oracle provides some built-in scripts and views that can be used to monitor locks in the database


http://docs.oracle.com/database/121/ADMIN/monitoring.htm#ADMIN11255

To illustrate this, I will open a session that updates a table, then create another session that updates the same table:

Session# 1:

select SYS_CONTEXT('userenv','con_name') "container name",
       SYS_CONTEXT('userenv','con_id') "container id",
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          362

--Update a the table rows:
 update emp set sal=1000;

14 rows updated.

I will keep the session alive, without doing commit/rollback.

From session# 2:

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          130

--Update the same rows as in session# 1:
update emp set sal=2000;

This session will not give you the prompt back, as it is trying to aquire a lock on the table rows.

I then ran the utllockt.sql script:

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utllockt.sql

Output (abbreviated):

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED  MODE_HELD     LOCK_ID1          LOCK_ID2
----------------- ----------------- --------------- ------------ ----------------- -----------------
362               None                
   130            Transaction       Exclusive       Exclusive     524308             17836

The leftmost session (362) is blocking the sessions listed underneath it.

Let's check what the session with SID = 130 is doing:

SELECT sess.sid,sess.serial#,sess.sql_id,s.plan_hash_value, s.child_number,sess.client_info,substr(sql_text,1,30) "sql text",sess.program,sess.pq_status,
        decode(sess.blocking_session_status,'VALID','blocked', 'NO HOLDER','Not blocked') "blocked?",
        sess.blocking_session "blocking session SID",sess.event
FROM V$SESSION sess inner join v$sql s 
on (sess.sql_id = s.sql_id)
WHERE sess.SID IN (130)
and sess.sql_child_number = s.child_number;

This query confirms the output from the Oracle supplied utllockt.sql, and reveals what the blocking session is trying to do, in this case, to execute DML on table rows as that are already being locked for update:

SID	SERIAL#	SQL_ID	PLAN_HASH_VALUE	sql text	PROGRAM	PQ_STATUS	blocked?	blocking session SID	EVENT
130	9755	1gpj28ptjj43p	1494045816	update emp set sal=2000	sqlplus@	ENABLED	blocked	362	enq: TX - row lock contention

How to use the 12c Privilege Analysis feature

One of the many New features in Oracle 12c is the ability to perform analysis of privileges that are assigned to a user.

Privilege Analysis sorts under Oracles "Database Vault" option, but can be used without enabling Database Vault.
Note that you need a valid License for Database Vault option to use the privilege analysis package.

To check if it's allready in use:

SELECT
   NAME             ,
   DETECTED_USAGES  ,
   FIRST_USAGE_DATE ,
   LAST_USAGE_DATE,
   LAST_SAMPLE_DATE,
   CURRENTLY_USED,
   DESCRIPTION   
 FROM
   DBA_FEATURE_USAGE_STATISTICS
 WHERE
   FIRST_USAGE_DATE IS NOT NULL
   AND NAME LIKE 'Privilege%';

From my example database, this is the output:

NAME	DETECTED_USAGES	FIRST_USAGE_DATE	LAST_USAGE_DATE	LAST_SAMPLE_DATE	CURRENTLY_USED	DESCRIPTION
Privilege Capture	11	27.05.2017 03:41:35	05.08.2017 03:46:46	05.08.2017 03:46:46	TRUE	Privilege Capture is being used

Here's how to set it up:

1. Create the capture:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.create_capture(
    name        => 'my_policy',
    type        => DBMS_PRIVILEGE_CAPTURE.g_context,
    condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
  );
END;
/

2. Check that the capture was created:

COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30
SET LINESIZE 100

SELECT name,
       type,
       enabled,
       roles,
       context
FROM   dba_priv_captures
ORDER BY name;

3. Enable the capture

BEGIN
  DBMS_PRIVILEGE_CAPTURE.enable_capture('my_policy');
END;
/

When a representative time has passed, you can disable the capture and generate results:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.disable_capture(
    name        => 'saga2_felles_policy'
  );

  DBMS_PRIVILEGE_CAPTURE.generate_result(
    name        => 'saga2_felles_policy'
  );
END;
/

You can check the results of the capture by using these queries:

SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN username FORMAT A20
COLUMN used_role FORMAT A30
COLUMN path FORMAT A50
COLUMN sys_priv FORMAT A30
COLUMN obj_priv FORMAT A8
COLUMN object_owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A11

prompt ================================
prompt Which privileges have been used?
prompt Look in DBA_USED_SYSPRIVS
prompt ================================
SELECT username, sys_priv
FROM   dba_used_sysprivs
WHERE  capture = 'my_policy'
ORDER BY username, sys_priv;

prompt ================================
prompt How were the privileges granted
prompt to the user?
prompt Look in DBA_USED_SYSPRIVS_PATH
prompt ================================
SELECT username, sys_priv, used_role, path
FROM   dba_used_sysprivs_path
WHERE  capture='my_policy'
order by username,sys_priv;

prompt ================================
prompt What object privileges were necessary?
prompt Look in DBA_USED_OBJPRIVS
prompt ================================
SELECT username, obj_priv, object_owner, object_name, object_type
FROM   dba_used_objprivs
WHERE  capture = 'my_policy';

prompt ================================
prompt How were the object privileges granted to the user?
prompt Look in DBA_USED_OBJPRIVS_PATH
prompt ================================

SELECT username, obj_priv, object_owner, object_name, used_role, path
FROM   dba_used_objprivs_path
WHERE  capture = 'my_policy';

exit

To drop the usage of Privilage Capture all together:

BEGIN
  DBMS_PRIVILEGE_CAPTURE.drop_capture(
    name        => 'my_policy'
  );
END;
/

How to display the current container in an Oracle multitenant database

You can display the current container your session is currently connected to using three different methods.

First, connect to either the container database or one of the pluggable databases running out of your container database. In this example, I chose to connect to the CDB.


1. Display the current container using the "show con_name" or "show con_id" in sqlplus

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT --> We are connectted to the ROOT container.

Switch to a different container, and try again:

SQL> alter session set container=vpdb01;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
VPDB01 --> We are now connected to the container named VPDB01.


SQL> show con_id

CON_ID
------------------------------
6 --> The current container id is 6.

2. Display the current container using the SYS_CONTEXT function

In the example below I have thrown in a couple of other useful parameters, along with the
"con_name" and the "con_id" parameters:

col "container id" format a20
col "container name" format a20
col "container id" format 99
col "Current schema" format a20
col SID format a10
set lines 200

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema       SID
-------------------- -------------------- -------------------- --------
VPDB01               6                    SYSTEM               63

From the output above, we see that we are connected to the container name VPDB01 with con_id 6.

Switch to the root container again, and verify that we are now connected to the ROOT container:

SQL> alter session set container=CDB$ROOT;

Session altered.

Rerun the SYS_CONTEXT statement:

  
select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema       SID
-------------------- -------------------- -------------------- --------
CDB$ROOT             1                    SYSTEM               63

3. Display the current container using V$CONTAINERS view

This final method only makes sense if you are connected to a non-root container.

SQL> alter session set container=vpdb01;

Session altered.

The result:

SQL> select CON_ID,DBID,name,OPEN_MODE
  2  from v$containers order by con_id;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         6 2049231443 VPDB01                         READ WRITE

If you are connected to the root container, and when querying the v$container, the resulting output will list all containers in your cdb:

SQL> select CON_ID,DBID,name,OPEN_MODE
  2  from v$containers order by con_id;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         1 2294367729 CDB$ROOT                       READ WRITE
         2 3254699093 PDB$SEED                       READ ONLY
         6 2049231443 VPDB01                         READ WRITE

3 rows selected.

I have also written a short blog post about the same topic here, but with a slightly different angle.

How to create and start a service with dbms_service

Create the service. The first parameter name is the service name, can be up to 64 characters long.
The second parameter is the network name of the service as used in SQLNet connect descriptors for client connections

# sqlplus / as sysdba
SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

Start the service:

SQL> exec dbms_service.start_service('online_users');

Update the service_names parameter. Without this, your new service would exist, but have to be manually started with every database restart:

SQL> alter system set service_names='proddb01, online_users' scope=both;

Check the parameter settings:

show parameter service

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
service_names                        string                            online_users, proddb01

A typical connect string when using JDBC would be

connect string : jdbc:oracle:thin:@//prodserver01:1521/online_users

If you are relying on tns naming, in other words, using a local tnsnames.ora file, make sure you set up your tnsnames.ora correctly.
The following is an example of a tnsnames.ora entry for the service we just created:

online_users =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = online_users)
    )
  )

Note: If you have specified a value for the database parameter domain_name, you need to add this to the SERVICE_NAME-clause of your tnsnames.ora entry, otherwise you will have trouble connecting:

show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      mydomain.com

In the case shown above, we have indeed specified a value for domain_name, and therefore the service_name will inherit this value by default.
So make sure you specify the FQDN in the SERVICE_NAME clause for your tnsnames.ora entry, like this:

(SERVICE_NAME = online_users.mydomain.com)

The command

lsnrctl services

will show you how the services are automatically registered with the listener.
The first 3 services are automatically created and always created by default with any Oracle installation. The last service registered with the listener is the one we created manually previously in this post:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
Services Summary...
Service "proddb01" has 1 instance(s).
  Instance "proddb01", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "proddb01.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "proddb01XDB.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=myserver.mydomain.com)(PORT=19394))
Service "online_users.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER

If for some reason the service is not registered automatically with the listener within reasonable time, you can try to force a registration, like this:

alter system register;

If your listener is running on the default port 1521, there should be no need to touch your listener configuration, except stop/start/reload.
If you use a non-default listener configuration, such as a port != 1521 or a listener name != LISTENER, you need to configure the parameter local_listener.
In the exammple below, I configure the listener to run on port 1526 instead of 1521:

alter system set local_listener='(address=(protocol=tcp)(host=myserver)(port=1526))' scope=both;

Once again, note that in this case, the parameter db_domain is set so you only need to include the server name, not the domain name. If you do use the FQDN, it doesn't make any difference to your setup.





Verify that your connection can be used by using sqlplus to create a connection:

sqlplus scott/tiger@online_users
SQL> select sys_context('userenv','SERVICE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
online_users.mydomain.com

To remove a service:

-- stop the service
SQL> exec dbms_service.stop_service('online_users');

-- after stopping the service, it may be deleted
SQL> exec dbms_service.delete_service('online_users');

PL/SQL procedure successfully completed.

A useful query for checking relevant parameters:

set lines 200
column name format a30
column network_name format a30
column value format a60

select name,value
from v$system_parameter
where name in ('service_names','db_domain','local_listener');

SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

How to use SYS_CONTEXT to display user information

Oracle provides a built-in namespace called USERENV, which describes the current session.

The function SYS_CONTEXT can be used to return the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

You can use the SYS_CONTEXT to retreive userinformation from the namespace USERENV, some examples below:

SELECT SYS_CONTEXT ('userenv','OS_USER') "OS user", 
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','IDENTIFICATION_TYPE') "Identification type",
       SYS_CONTEXT('userenv','IP_ADDRESS') "IP",
       SYS_CONTEXT('userenv','HOST') "Host name",
       SYS_CONTEXT('userenv','SID') "SID",
       SYS_CONTEXT('userenv','SERVICE_NAME') "Service"
FROM DUAL;

Executed from a remote session, logged into the database as user SCOTT, the output from the query above will be:

OS user	Current schema	Identification type	IP	Host name	SID	Service
SCOTT	SCOTT	LOCAL	192.168.1.2	MYDOMAIN\MYCLIENT	170	myservice.mydomain.com

To find the serial# of your session, you will need to query the v$session dynamic performance view. you need to have SELECT privileges on the V$SESSION view to use this query, so first, a grant is needed.

SQL> show user
USER is "SYS"

GRANT SELECT ON V_$SESSION TO SCOTT;

Now, as user SCOTT, you can execute the query

SELECT SID, SERIAL#
FROM V$SESSION
WHERE SID=SYS_CONTEXT('USERENV','SID');


Source: Oracle Documentation The 12.2 SYS_CONTEXT documentation can be found here

How to create a DDL trigger in a schema

CREATE TABLE AUDIT_DDL (
 d date,
 OSUSER varchar2(255),
 CURRENT_USER varchar2(255),
 HOST varchar2(255),
 TERMINAL varchar2(255),
 owner varchar2(30),
 type varchar2(30),
 name varchar2(30),
 sysevent varchar2(30));

connect uid/pwd

CREATE OR REPLACE TRIGGER audit_ddl_trg after ddl on schema
BEGIN
IF(ora_sysevent='TRUNCATE') THEN
 null; -- If we do not care about truncate
ELSE
 INSERT INTO audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
 values(
  sysdate,
  sys_context('USERENV','OS_USER') ,
  sys_context('USERENV','CURRENT_USER') ,
  sys_context('USERENV','HOST') , 
  sys_context('USERENV','TERMINAL') ,
  ora_dict_obj_owner,
  ora_dict_obj_type,
  ora_dict_obj_name,
  ora_sysevent
  );
 END IF;
END;
/

Source: Don Burleson http://www.dba-oracle.com/t_ddl_triggers.htm