Common User Vs Local User In 12c

by

COMMON USER:

1.A common user is a Database user, which perform an activity in all the containers including root container of the CDB.

2.A common user can only be created in root container.

3.Common username must start with C## prefix.

4.While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL.

5.It is not recommended to create objects under common user.

6. Once we create an common user it will automatically get replicated to the all pdb's.

7. Privileges for the common user will vary from the CDB & PDB.
1
2
3
4
5
SQL> show parameter common
 
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
common_user_prefix string C##

CDB TABLESPACE INFO:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SET LINES 132 PAGES 100
COL con_name FORM A15 HEAD "Container|Name"
COL tablespace_name FORM A15
COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg."
COL apm FORM 999,999,999,999 HEAD "Alloc|Space Meg."
--
COMPUTE SUM OF fsm apm ON REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
--
WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
FROM cdb_free_space cf1
,v$containers c1
WHERE cf1.con_id = c1.con_id
GROUP BY c1.con_id, cf1.tablespace_name),
y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm
FROM cdb_data_files cd
,v$containers c2
WHERE cd.con_id = c2.con_id
GROUP BY c2.con_id
,cd.tablespace_name)
SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm
FROM x, y, v$containers v
WHERE x.con_id = y.con_id
AND x.tablespace_name = y.tablespace_name
AND v.con_id = y.con_id
UNION
SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.con_id, vc2.name, tf.tablespace_name
ORDER BY 1, 2;

Create a common user:

1
2
3
4
5
6
7
8
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
 
create user C##<username> identified by "<Password>" account unlock;
or
create user C##<username> identified by "<Password>" account unlock container=all;

Account status check:

1
2
3
4
5
6
7
8
select con_id,username,common,oracle_maintained from cdb_users where username='<Username>';
 
Ex:
 
CON_ID USERNAME COMMON ORA
---------- -------------------- ---------- ---
1 C##WHALE YES N
3 C##WHALE YES N

LOCAL USER:

1.Local users are the normal database user, which can be created only in PDBs.

2.With the appropriate privileges, a local user can access object in a common user’s schema

Creating local user in PDB:

1
2
3
create user <Username> identified by "<Password>" account unlock;
 (or)
create user <Username> identified by "<Password>" container=current account unlock;
Note : Container clause is not mandatory in PDB because default value has been set to current.

Account status check:

1
2
3
4
5
6
7
8
9
select con_id,username,common,oracle_maintained from cdb_users where username='<User_Name>’;
 
Ex:
 
SQL> select con_id,username,common,oracle_maintained from cdb_users where username='WHALE';
 
CON_ID USERNAME COMMON ORA
---------- --------------------------------
3 WHALE NO N