Oracle DB user with password no expiry

Usually in default user profile, password expires for a user after certain period. If we want that password won't expire for a user as it may be required for the users used in the application end, we can implement that

1.   by creating a new profile with LIMIT password no expired and 
2.   assigning this profile to our target users.

create profile pro_pass_no_exp limit password_life_time UNLIMITED;

Profile created.

alter user dbbl profile pro_pass_no_exp;

User altered.

CDB & PDB Operation

List of PLugabel Database in the Container Database:

select CON_ID, NAME, OPEN_MODE from V$PDBS;

Connecting PLuggable Database:

alter session set container = pdb6;

to connect with the container database:

ALTER SESSION SET CONTAINER = CDB$ROOT;

To get the name of connected database:

show con_name

Starting Plugable Database:

From the current PDB:
alter pluggable database open;
From Container Database:
alter pluggable database pdb6 open;

Shutting Down Plugable Database:

From the current PDB:
alter pluggable database close;
From Container Database
alter pluggable database pdb6 close;

List of Common users in CDB:

select distinct USERNAME from CDB_USERS where common = 'YES';

List of Modifiable parameters in PDB level:

set lines 200
col name for a35
select NAME, ISPDB_MODIFIABLE from V$PARAMETER;




Values of CON_ID and definition:
0 = The data pertains to the entire CDB
1=  The data pertains to the root
2= The data pertains to the seed
3 - 254 = The data pertains to a PDB, Each PDB has its own container ID.

Container(CDB) & Plug-able(PDB) Database

Plug-able Database & Container Database:

Background Processes	Shared by root CDB and All PDBs
Control File	Single Control file for entire CDB
Redo Log	Single Redo Log for entire CDB
SYSTEM Tablespace	Separate SYSAUX tablespace for the root and for each PDB.
SYSAUX Tablespace	Separate SYSAUX tablespace for the root and for each PDB.
Temporary Tablespace	one default temporary tablespace for the entire CDB; but we can can create additional temporary tablespaces in individual PDBs
Undo Tablespace	One active undo tablespace is needed for a single-instance CDB, or one active undo tablespace is needed for each instance of an Oracle RAC CDB.
Default Tablespace	We can specify a separate default tablespace for the root and for each PDB
Physical Datafiles	There are separate datafiles for the root, the seed, and each PDB.
Database Character-set	A CDB uses a single character set. All of the PDBs in the CDB use this character set.
listener.ora, tnsnames.ora, and sqlnet.ora	Single copy of listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.

Common users in Oracle DB 12c

In a Multi tenant database,we can create user accounts within the PDB's just like we normally do. This user is container oriented. And this user wont have any access to other containers.

For example, let we want to have a DBA account called paul that would be able to create tablespaces in any PDB. For this purpose we will have to create a new kind of user account called a common account. 

I tried earlier as:

The common account naming format is similar to a normal account name - except that it starts with a special set of characters, C## by default. Too create a common user account called paul we will have to log into the root container and use the create user command as seen here:

SQL>create user c##paul identified by ; 

When the grant is issued from the ROOT container, the default scope of that grant is just to the ROOT container.  When a common user account is created, the account is created in all of the open PDB's of the pluggable database. At the same time, the account is not granted any privileges. 

To provide the connection permission to this user for all available pdbs:

grant create session to c##paul container=all;

Or, we can use:

create user c##paul identified by oracle container=all;

Then we will have to grant sysdba role to c#paul for all container.

grant sysdba to c##paul container=all;