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 jyothi datafile '/oradata/ORCL/datafile/pdb1/jyo1.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/jyo1.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

JYOTHI