Sunday, 11 January 2015

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


Popular Posts