SYED USER CONNECTIVITY PRACTICE

1 ) SQL> select sys_context('userenv','con_id') con_id, sys_context('userenv','con_name') from dual;

CON_ID
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
2
PDB$SE



 2) SQL>select  b.con_id, a.pdb_name, a.status,b.open_mode
from dba_pdbs a, v$pdbs b
where a.pdb_name=b.name
order by b.con_id;
   CON_ID PDB_NAME             STATUS    OPEN_MODE
---------- -------------------- --------- ----------
         2 PDB$SEED             NORMAL    READ ONLY



   3) SQL>select username,common common_user ,con_id
       from cdb_users
        where account_status='OPEN'
          and username  LIKE 'SYS%'
          ORDER BY con_id,username;
USERNAME
--------------------------------------------------------------------------------
COM     CON_ID
--- ----------
SYS
YES          2

SYSTEM
YES          2



query for local users
SQL>select username,common common_user, con_id
   from cdb_users
   where account_status='open'
AND username NOT LIKE'SYS%';


SQL> SHOW CON_NAME;

CON_NAME
------------------------------


ALTER SESSION SET CONTAINER = pdb1;


SQL> SHOW CON_NAME;

CON_NAME
------------------------------
CDB$ROOT



COLUMN pdb_name FORMAT A20

SQL> SELECT pdb_name, status
FROM   dba_pdbs
ORDER BY pdb_name;

PDB_NAME             STATUS
-------------------- ---------
PDB$SEED             NORMAL
PDBTEST1             NORMAL
PDBTEST2             NORMAL
PDBTEST3             NORMAL




SQL> ALTER PLUGGABLE DATABASE pdbtest1 OPEN READ WRITE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdbtest2 OPEN READ WRITE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdbtest3 OPEN READ WRITE;

Pluggable database altered.




SQL> SELECT name, open_mode
  2  FROM   v$pdbs
  3  ORDER BY name;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBTEST1                       READ WRITE
PDBTEST2                       READ WRITE
PDBTEST3

ALTER SESSION SET CONTAINER = PDBTEST1
SQL> SHOW CON_NAME;

CON_NAME
------------------------------
PDBTEST1


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/oracle/oradata/CDBTES/undotbs01.dbf
/data/oracle/oradata/CDBTES/PDBTEST1/system01.dbf
/data/oracle/oradata/CDBTES/PDBTEST1/sysaux01.dbf
/data/oracle/oradata/CDBTES/PDBTEST1/PDBTEST1_users01.dbf




SQL> ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered.

SQL> SHOW CON_NAME,CON_ID;

CON_NAME
------------------------------
PDB$SEED
SQL>


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/oracle/oradata/CDBTES/pdbseed/system01.dbf
/data/oracle/oradata/CDBTES/pdbseed/sysaux01.dbf
/data/oracle/oradata/CDBTES/undotbs01.dbf

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');