12c database features and steps

Oracle Database 12c introduced a new feature called “multitenant.” The multitenant feature provides the ability for a single instance to manage multiple databases. The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).

In Oracle 12c and above, we have two types of databases:

  1. Container DataBase (CDB) :

Container Database (CDB): This is the database that is created when that database supports Oracle’s multitenant option. It’s also called the ROOT container and is the CDB$ROOT within the data dictionary views of the CDB

  • Pluggable DataBase (PDB) :

Pluggable Database (PDB): These are the databases that are stored within the CDB. A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB

SQLPLUS / AS SYSDBA

Before execute the query we need to set the oracle script true

SQL> alter session set “_ORACLE_SCRIPT”=true;

Session altered.

SQL> commit;

SQL> CREATE user a1 identified by a1;

User created.

SQL> grant connect,resource to a1;

Grant succeeded.

SQL> commit;

Commit complete.

SQL> conn a1/a1;

Connected.

SQL> show user;

USER is “A1”

SQL>

STARTUP OPEN;

SQL> CREATE USER QP01 IDENTIFIED BY QP01;

SQL> Grant connect, resource to qp01;

Oracle 12c

Create user

SQL> create user c##qpone identified by qpone;  Container Database

SQL> alter session set container=querypanel;       Pluggable database (querypanel is the pluggable database name).

desc v$version

SELECT banner FROM v$version WHERE ROWNUM = 1;

Select BANNER from v$version;

desc v$instance;

SELECT INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME FROM v$instance;

SELECT INSTANCE_NAME,HOST_NAME,STATUS,INSTANCE_ROLE,DATABASE_TYPE FROM V$INSTANCE;

select DATABASE_STATUS,STATUS,INSTANCE_NAME FROM v$instance;

desc dba_data_files;

SELECT FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS FROM dba_data_files;

desc dba_users;

SELECT USERNAME,DEFAULT_TABLESPACE,PROFILE FROM DBA_USERS;

desc database_properties;

select PROPERTY_NAME,DESCRIPTION from database_properties

desc user_users;

select USERNAME,ACCOUNT_STATUS from user_users;

desc all_users;

select USERNAME,CREATED from all_users;

desc session_roles;