USING SELECT 'X' in query/sub-queries

USING SELECT 'X' in query/sub-queries.

--------------------------------------------------------
--  DDL for Table TAB1
--------------------------------------------------------

  CREATE TABLE "ATEST"."TAB1"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(20 BYTE)
   ) ;

Insert into ATEST.TAB1 (ID,NAME) values (1,'AAA');
Insert into ATEST.TAB1 (ID,NAME) values (2,'BBB');
Insert into ATEST.TAB1 (ID,NAME) values (3,'EEE');
Insert into ATEST.TAB1 (ID,NAME) values (4,'FFF');


--------------------------------------------------------
--  DDL for Table TAB2
--------------------------------------------------------

  CREATE TABLE "ATEST"."TAB2"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(20 BYTE)
   ) ;

Insert into ATEST.TAB2 (ID,NAME) values (1,'CCC');
Insert into ATEST.TAB2 (ID,NAME) values (2,'DDD');


Get records that exits in TAB1 and not in TAB2 using select 'X' :

select * from TAB1 f where not exists (select 'X' from TAB2 where id=f.id);
ID    NAME
--    ---- 
4    FFF
3    EEE

IN the above query we get output of all the records from TAB1 that doesnt match with TAB2 ID's.
Hence we do not get the records with ID's 1 & 2 as they only exits in TAB1.
This is just like using "select * from TAB1 f where not exists (select ID from TAB2 where id=f.id);"


Get records that exits in TAB1 and in TAB2 using select 'X' :

select * from TAB1 f where exists (select 'X' from TAB2 where id=f.id);

ID    NAME
--    ---- 
1    AAA
2    BBB

IN the above query we get output of all the records from TAB1 that exist with same ID in TAB2 .
Hence we get only records with ID 1 & 2 as they exists in both TABLES.
This is just like using "select * from TAB1 f where exists (select ID from TAB2 where id=f.id);"

validate_con_names: PDB$SEED is not open catconInit: Unexpected error returned by validate_con_names Unexpected error encountered in catconInit; exiting

[oracle@Linux03 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d

$ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to /tmp/utl32k_output_catcon_19470.lst
catcon: See /tmp/utl32k_output*.log files for output generated by scripts
catcon: See /tmp/utl32k_output_*.lst files for spool files, if any
validate_con_names: PDB$SEED is not open
catconInit: Unexpected error returned by validate_con_names
Unexpected error encountered in catconInit; exiting

[oracle@Linux03 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:27:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       MOUNTED
PDBORCL       MIGRATE
PDBORCL2       MIGRATE
PDBORCL1       MIGRATE

SQL> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

CHECK THE STATUS OF PLUGGABLE DATABASE.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       MOUNTED
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED

WE NEED TO START PDB$SEED PLUGGABLE DATABASE in UPGRADE STATE FOR THAT 

SQL> SHUTDOWN IMMEDIATE;


Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE;

ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       MIGRATE
PDBORCL       MIGRATE
PDBORCL2       MIGRATE
PDBORCL1       MIGRATE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@Linux03 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin

[oracle@Linux03 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d
$ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to /tmp/utl32k_output_catcon_19826.lst
catcon: See /tmp/utl32k_output*.log files for output generated by scripts
catcon: See /tmp/utl32k_output_*.lst files for spool files, if any
catcon.pl: completed successfully

ALTER SYSTEM SET max_string_size=extended ORA-02097: parameter cannot be modified because specified value is invalid

MAX_STRING_SIZE PARAMETER:

The extended data types functionality is controlled using the MAX_STRING_SIZE initialization parameter. The default value is STANDARD, which restricts the maximum sizes to the traditional lengths. Setting the parameter value to EXTENDED allows for the new maximum lengths.

This is a pre-requisite for enabling Extended Data Types in Oracle Database 12c. Which helps us to increase the max size of varchar data type from 4000 bytes to 32767 bytes.

[oracle@Linux03 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:10:02 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string STANDARD

SQL> alter system set max_string_size=EXTENDED scope=spfile;

System altered.

SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string STANDARD
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.

SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string EXTENDED

CREATE ORACLE DATABASE SERVICES/SERVICE_NAME

dbms_service.create_service :  

We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name. 

oracle@LINUX201:[~] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         4 ORCL
         1 SYS$BACKGROUND
         2 SYS$USERS

************************************************************

LEST CREATE A NEW DB SERVICE 

************************************************************

SAMPLE :

BEGIN

DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',

 network_name=>'QPDEV.WORLD'); (or)  network_name=>'QPDEV');

 END;

 /


SQL> begin
 dbms_service.create_service('ORCLTEST','ORCLTEST');
end;
/

PL/SQL procedure successfully completed.

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         4 ORCL
         1 SYS$BACKGROUND
         2 SYS$USERS

SQL> begin
 DBMS_SERVICE.START_SERVICE('ORCL');
end;
/  2    3    4

PL/SQL procedure successfully completed.

************************************************************

CHECK IF THE SERVICE IS CREATED AND STARTED

************************************************************

SQL> select SERVICE_ID,name from V$SERVICES;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         7 ORCLTEST
         4 ORCL
         1 SYS$BACKGROUND
         2 SYS$USERS

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls
samples  shrept.lst  tnsnames.ora

************************************************************

LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:

************************************************************

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201.world.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLTEST)
    )
  )

************************************************************

LETS TEST IT :

************************************************************

oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))
OK (10 msec)
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

You can also use more advanced features like failover parameters like below

Examples

DBMS_SERVICE.CREATE_SERVICE('ORCL','ORCL.WORLD');

DECLARE
   params dbms_service.svc_parameter_array;
   BEGIN
      params('FAILOVER_TYPE')            :='TRANSACTION';
      params('REPLAY_INITIATION_TIMEOUT'):=1800;
      params('RETENTION_TIMEOUT')        :=86400;
      params('FAILOVER_DELAY')           :=10;
      params('FAILOVER_RETRIES')         :=30;
      params('commit_outcome')           :='true';
      params('aq_ha_notifications')      :='true';
      DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
   END;

Restricting database access to Hostname or IP addeess

**************************************************************

                      FROM MY CLIENT MACHINE:

**************************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)

**************************************************************
NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :
**************************************************************

ON DB SERVER:

oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes  ## I have just added this line to test if it works

[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-00584: Valid node checking configuration error
 TNS-12560: TNS:protocol adapter error

As expected it doesn't work as listener needs to know where to accept or deny connections from.
Now I tweak the sqlnet.ora file to below

[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03)  ## I only want to accept connections from my localhost i.e Linux03

RESTART THE LISTENER NOW.

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

 (OR)

[oracle@Linux03 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-MAR-2016 15:28:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

****************************************************
NOW I TRY TO CONNECT FROM CLIENT AGAIN :
****************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
TNS-12537: TNS:connection closed

C:\Windows\System32>


Nope it doesn't allow me to connect.

*************************************************************
Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see
**************************************************************

[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)

**************************************************************************
SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER
*************************************************************************


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)

YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :

tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)

PDB ADMIN account created while Pluggable database creation doesn't have DBA level privs

PDB ADMIN ACCOUNT DOESN'T HAVE PERMISSIONS TO CREATE/DROP by DEFAULT unless explicitly granted:

********************* ******************************
pdborcl1 is the user created while creating PDB via DBCA:
********************* ******************************

[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1/oracle@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:36:23 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Mar 07 2016 11:21:22 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select table_name from tabs;

no rows selected

SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> exit

***************************** ******************************
NOW LOGIN AS USER pdborcl1_usr2 who has a table created in his user
***************************** ******************************

[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:39:21 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Mar 07 2016 11:46:08 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select table_name from tabs;

TABLE_NAME
--------------------------------------------------------------------------------
TEST

CREATE LOCAL USERS IN 12C DATABASE

******************** ********************************** **************
CREATE LOCAL USERS IN 12C PDB DATABASE:
******************** ********************************** **************

When connected to a multi-tenant database the management of users and privileges is a little different to traditional Oracle environments. In multi-tenant environments there are two types of user.

Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Likewise, there are two types of roles.


******************** **************
Create Local Users
******************** **************

When creating a local user the following requirements must all be met.

You must be connected to a user with the CREATE USER privilege.
The username for the local user must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
The following example shows how to create local users with and without the CONTAINER clause from the root container.

CONN / AS SYSDBA

-- Switch container while connected to a common user.

ALTER SESSION SET CONTAINER = pdborcl1;

-- Create the local user using the CONTAINER clause.

CREATE USER pdborcl1_usr1 IDENTIFIED BY pdborcl1_usr1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO pdborcl1_usr1 CONTAINER=CURRENT;

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr1/pdborcl1_usr1@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:52:50 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing option

SQL> sho user
USER is "PDBORCL1_USR1"

******************** ------ ******************** ----- **************
-- Connect to a privileged user in the PDB.
******************** ------ ******************** ----- **************

CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.

SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
SQL> GRANT CREATE SESSION TO pdborcl1_usr2;

SQL> exit

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:56:29 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

SQL> show user
USER is "PDBORCL1_USR2"

If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.
This dba role will be only specific to prdborcl1 local PDB database.

SQL> grant pdb_dba to PDBORCL1_USR2;

Grant succeeded.


SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges

As per above doc (https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG4414) PDB_DBA Granted automatically to the local user that is created when you create a new PDB from the seed PDB. No privileges are provided with this role.

IF I try to use a dba user in pdborcl1 and try to connect onto pdborcl2. It wont work

oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:13:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

NOW I LOGGED IN AS SUPER USER AND GRANTED DBA TO PDBORCL1_USR2;

[oracle@Linux03 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:32:01 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER SESSION SET CONTAINER = pdborcl1 ;

Session altered.

SQL> grant dba to PDBORCL1_USR2;

connect back as PDBORCL1_USR2 user and tried to create table :

SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

SQL> show user
USER is "PDBORCL1_USR2"

SQL> create table test (name varchar2(20));

Table created.

SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST

SQL> insert into test values ('arvind') ;

1 row created.

SQL> commit;

SQL> select * from test;

NAME
--------------------
arvind

SQL> create view test1 as select * from test;

View created.

Note: I was able to have a great deal of priv as dba but only till "pdborcl1". I still do not have any priv on "prdorcl2" or any other DB's in CDB.

~~~~~~~~~~~~~
NOW LETS TRY LOGGEING INTO PDBORCL2 (Nope still doest work), as this dba role has been granted to pdborcl1_usr2 only for PDB "pdborcl1"
~~~~~~~~~~~~~

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:35:11 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied



*************************** **********************************
Now lets create same user in OTHER PDB "PDBORCL2"
*************************** **********************************

[oracle@Linux03 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:48:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER SESSION SET CONTAINER = pdborcl2;

Session altered.

SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;

User created.

SQL> grant CREATE SESSION,dba to pdborcl1_usr2;

Grant succeeded.

SQL> conn pdborcl1_usr2/pdborcl1_usr2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:50:33 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
PDBORCL2
SQL> show user
USER is "PDBORCL1_USR2"
SQL> select username from dba_users where username like 'PDB%';

USERNAME
--------------------------------------------------------------------------------
PDBORCL2
PDBORCL1_USR2

Conclusion :

1) You can have same users with same username and pass in two diff PDB's as local user.Yet they have no permissions on each other.
2)  Youe will need CDB_DBA -- it provides the privileges required for administering a CDB, such as SET CONTAINER, SELECT ON PDB_PLUG_IN_VIOLATIONS,   and SELECT ON CDB_LOCAL_ADMIN_PRIVS