monitoring CDB and PDB tablespaces and users - ORACLE 12 C Database

SQL> CONN system/oracle@pdb1

Connected.



SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oradata/orcl/undotbs01.dbf

/oradata/ORCL/datafile/pdb1/system01.dbf

/oradata/ORCL/datafile/pdb1/sysaux01.dbf

/oradata/ORCL/datafile/pdb1/users01.dbf



SQL> create tablespace dbacentre datafile '/oradata/ORCL/datafile/pdb1/dba1.dbf' size 10M;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/undotbs01.dbf

/oradata/ORCL/datafile/pdb1/system01.dbf

/oradata/ORCL/datafile/pdb1/sysaux01.dbf

/oradata/ORCL/datafile/pdb1/users01.dbf

/oradata/ORCL/datafile/pdb1/dba1.dbf



SQL> create user sarath identified by sarath default tablespace jyothi;

User created.

SQL> grant connect,resource to sarath;

Grant succeeded.

SQL> conn sarath/sarath

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.



Needs to mention tns of PDB database name

SQL> conn sarath/sarath@pdb1
Connected.
SQL> create table test(names char(30));
Table created.
SQL> insert into test values ('records');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.

Connecting CDB Database and comparing the tablespace names

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

again connecting to PDB database and will have a look

SQL> conn system/oracle@pdb1

Connected.

SQL>  SHOW CON_NAME

CON_NAME

------------------------------

PDB1

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

TEMP

USERS

dbacentre