Search This Blog

Total Pageviews

Wednesday, 9 July 2014

How to check pluggable database users ...

How to create user in pluggable database   !!!!!!!!!!!!! 


How to check  pluggable database users ... 

alter session set container=ANUJ1 ;
create user test1 identified by test123 container=anuj1 ;

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss' ;
set pagesize 200
col USERNAME format a25
col PDB_NAME format a15
select u.USERNAME,p.CON_ID,p.PDB_NAME,u.CREATED from CDB_USERS u ,dba_pdbs p
where p.PDB_ID=u.CON_ID
--and username like 'TEST%'
and p.CON_ID>2 ;


USERNAME                      CON_ID PDB_NAME        CREATED

------------------------- ---------- --------------- -------------------
TEST3                              3 ANUJ1           15-07-2013 15:36:32
TEST1                              3 ANUJ1           15-07-2013 15:28:34
TEST2                              3 ANUJ1           15-07-2013 15:36:20
TEST                               3 ANUJ1           14-07-2013 14:14:37

SQL> alter session set container=ANUJ1 ;
Session altered.

SQL> alter user test identified by test123 ;
User altered.


[oracle@localhost ~]$sqlplus test/test123@192.168.132.129:1521/anuj1

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 8 10:05:43 2014

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

Last Successful login time: Sun Jul 14 2013 15:14:45 +01:00

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


Second example ... 

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORDS                           READ WRITE NO

         5 ANUJ                           READ WRITE NO


SQL> alter session set  container=anuj;

Session altered.


SQL> create user test1 identified by test container=current ;

User created.


SQL> grant connect, resource to test1;

Grant succeeded.

[oracle@vbgeneric ~]$ sqlplus test1/test@localhost:1521/anuj

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 23 04:00:42 2017

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



2 comments:

Anuj Singh said...

set pagesize 200
col USERNAME format a25
col PDB_NAME format a15
select USERNAME,a.CON_ID,PDB_NAME,CREATED from CDB_USERS a ,dba_pdbs b
where b.PDB_ID=a.CON_ID
and username like 'ANUJ%'
and a.CON_ID>2 ;

Anuj Singh said...


set pagesize 200
col USERNAME format a25
col PDB_NAME format a15
select USERNAME,a.CON_ID,PDB_NAME,CREATED from CDB_USERS a ,dba_pdbs b
where 1=1
and b.PDB_ID=a.CON_ID
-- and username like 'ANUJ%'
--and a.CON_ID>2 ;


col USERNAME format a25
col PDB_NAME format a15
select USERNAME,a.CON_ID,PDB_NAME,CREATED from CDB_USERS a ,dba_pdbs b
where 1=1
-- and a.CON_ID >1
order by 1,3;

Oracle DBA

anuj blog Archive