Managing PDB
Rename Pluggable Database
Manage Pluggable database
Drop Pluggable database
Security In Pluggable database
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
----------------------------- ----------
TEST 3
as you see in the above query, I already created pluggable database called test. and it's in Read write Mode.
Rename Pluggable database
SQL> alter pluggable database TEST close immediate ;
Pluggable database altered.
SQL> alter pluggable database TEST open restricted ;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
TEST READ WRITE
SQL> alter pluggable database TEST rename global_name to new ;
Pluggable database altered.
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
--------------------------------------- ----------
new 3
if you are not connected to pluggable database or set session container then you will recicve error message
ORA-65046: operation not allowed from outside a pluggable
SQL> alter pluggable database new close immediate ;
Pluggable database altered.
SQL> alter pluggable database new open ;
Pluggable database altered.
Manage Pluggable Database
Back to root container using / as sysdba like below :
SQL> conn / as sysdba
Connected.
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
--------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
new 3
We Control which list for for tablespace & datafiles by using con_id.
List tablespace in root container
SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=1;
TABLESPACE_NAME CON_ID
------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1
CDATA 1
List Database In root Container
SQL> select file_name, con_id from cdb_data_files where con_id=1;
FILE_NAME CON_ID
--------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/users01.dbf 1
/u01/app/oracle/oradata/db12c/undotbs01.dbf 1
/u01/app/oracle/oradata/db12c/sysaux01.dbf 1
/u01/app/oracle/oradata/db12c/system01.dbf 1
/u01/app/oracle/oradata/db12c/gls/test.dbf 1
Temp Tablespace in root container
SQL> select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME CON_ID
----------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf 1
Create Tablespace
SQL> create tablespace test datafile '/u01/app/oracle/oradata/db12c/gls/test03.dbf' size 20M;
Tablespace created.
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;
TABLESPACE_NAME CON_ID
------------------------------ ----------
SYSTEM 1
TEST 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSAUX 2
TEMP 2
SYSTEM 2
TEMP 3
SYSAUX 3
PDB_TEST 3
SYSTEM 3
14 rows selected.
Create temp tablespace
SQL> create temporary tablespace temp_test tempfile '/u01/app/oracle/oradata/db12c/gls/temp_test.dbf' size 20M ;
Tablespace created.
SQL> select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME CON_ID
--------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf 1
/u01/app/oracle/oradata/db12c/gls/temp_test.dbf 1
The Same Steps in root container for create tablespace and temp tablespace for pluggable database.
Security In Pluggable Database
In This Section we will discuss how to manage Users, roles and privileges.
before Demonstration you need to know what is the difference between two users type :
Common : when you create this kind of users in root it's automatically replicated in all Pluggable database.
Local : this kind of users only created on pluggable database that you are connected to it now. and dose not effect on others pluggable database.
To Create Common Users you need to be connected to root container.
SQL> conn / as sysdba
Connected.
SQL> create user c##hyd identified by hyd123 ;
User created.
SQL> select username, common, con_id from cdb_users where username like 'C##%';
USERNAME COM CON_ID
------------------------------------ --- ----------
C##TEST YES 1
C##OSAMA YES 1
C##TEST YES 3
C##OSAMA YES 3
SQL> grant create session to c##hyd ;
Grant succeeded.
SQL> conn c##hyd/hyd123@test12c:1521/db12c ;
Connected.
Let's connect to pluggable database :
The user i will created it here will not appear in root container.
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create user test identified by test ;
User created.
SQL> grant create session to test ;
Grant succeeded.
SQL> select username, common, con_id from cdb_users where username ='TEST';
USERNAME COM CON_ID
------------------ -------- ----------------
TEST NO 3
SQL> conn test/test@test12c:1521/new ;
Connected.
Same rules and conditions applied on Roles if you created in Root Container it will be replicated to pluggable database, on other hand if you created in Pluggable database it will be local without effecting Container.
Let's connect to Root Container
SQL> conn / as sysdba
Connected.
SQL> create role c##root_role ;
Role created.
SQL> select role, common, con_id from cdb_roles where role='C##ROOT_ROLE';
ROLE COM CON_ID
--------------------- ---------- ----------
C##ROOT_ROLE YES 1
C##ROOT_ROLE YES 3
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create role test2;
Role created.
SQL> select role, common, con_id from cdb_roles where role='TEST2';
ROLE COM CON_ID
---------------- -------- ----------
TEST2 NO 3
SQL> create role hr container=all ;
create role hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
SQL> create user hr identified by hr container=all ;
create user hr identified by hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
---> you cannot create a common role inside a PDB.
Check user privileges :
SQL> select grantee, privilege, common, con_id from cdb_sys_privs where privilege='CREATE SESSION' and grantee='TEST';
GRANTEE PRIVILEGE COM CON_ID
-------------- ------------------------ ---------- -----
TEST CREATE SESSION NO 3
Drop Pluggable Database
SQL > drop pluggable database new including datafiles;
Pluugable database dropped.
Move Datafiles Online 12c
For the first time in Oracle Database you can move objects online without offline or shutdown database which is very useful and helpful for any DBA's
Check the example below :
SQL> select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db12c/system01.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf
Let's move system01.dbf
SQL> alter database move datafile '/u01/app/oracle/oradata/db12c/system01.dbf' to '/u01/system.dbf';
SQL> select File_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/u01/system.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf
Manage Pluggable database
Drop Pluggable database
Security In Pluggable database
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
----------------------------- ----------
TEST 3
as you see in the above query, I already created pluggable database called test. and it's in Read write Mode.
Rename Pluggable database
SQL> alter pluggable database TEST close immediate ;
Pluggable database altered.
SQL> alter pluggable database TEST open restricted ;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
TEST READ WRITE
SQL> alter pluggable database TEST rename global_name to new ;
Pluggable database altered.
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
--------------------------------------- ----------
new 3
if you are not connected to pluggable database or set session container then you will recicve error message
ORA-65046: operation not allowed from outside a pluggable
SQL> alter pluggable database new close immediate ;
Pluggable database altered.
SQL> alter pluggable database new open ;
Pluggable database altered.
Manage Pluggable Database
Back to root container using / as sysdba like below :
SQL> conn / as sysdba
Connected.
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
--------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
new 3
We Control which list for for tablespace & datafiles by using con_id.
List tablespace in root container
SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=1;
TABLESPACE_NAME CON_ID
------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1
CDATA 1
List Database In root Container
SQL> select file_name, con_id from cdb_data_files where con_id=1;
FILE_NAME CON_ID
--------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/users01.dbf 1
/u01/app/oracle/oradata/db12c/undotbs01.dbf 1
/u01/app/oracle/oradata/db12c/sysaux01.dbf 1
/u01/app/oracle/oradata/db12c/system01.dbf 1
/u01/app/oracle/oradata/db12c/gls/test.dbf 1
Temp Tablespace in root container
SQL> select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME CON_ID
----------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf 1
Create Tablespace
SQL> create tablespace test datafile '/u01/app/oracle/oradata/db12c/gls/test03.dbf' size 20M;
Tablespace created.
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;
TABLESPACE_NAME CON_ID
------------------------------ ----------
SYSTEM 1
TEST 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSAUX 2
TEMP 2
SYSTEM 2
TEMP 3
SYSAUX 3
PDB_TEST 3
SYSTEM 3
14 rows selected.
Create temp tablespace
SQL> create temporary tablespace temp_test tempfile '/u01/app/oracle/oradata/db12c/gls/temp_test.dbf' size 20M ;
Tablespace created.
SQL> select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME CON_ID
--------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf 1
/u01/app/oracle/oradata/db12c/gls/temp_test.dbf 1
The Same Steps in root container for create tablespace and temp tablespace for pluggable database.
Security In Pluggable Database
In This Section we will discuss how to manage Users, roles and privileges.
before Demonstration you need to know what is the difference between two users type :
Common : when you create this kind of users in root it's automatically replicated in all Pluggable database.
Local : this kind of users only created on pluggable database that you are connected to it now. and dose not effect on others pluggable database.
To Create Common Users you need to be connected to root container.
SQL> conn / as sysdba
Connected.
SQL> create user c##hyd identified by hyd123 ;
User created.
SQL> select username, common, con_id from cdb_users where username like 'C##%';
USERNAME COM CON_ID
------------------------------------ --- ----------
C##TEST YES 1
C##OSAMA YES 1
C##TEST YES 3
C##OSAMA YES 3
SQL> grant create session to c##hyd ;
Grant succeeded.
SQL> conn c##hyd/hyd123@test12c:1521/db12c ;
Connected.
Let's connect to pluggable database :
The user i will created it here will not appear in root container.
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create user test identified by test ;
User created.
SQL> grant create session to test ;
Grant succeeded.
SQL> select username, common, con_id from cdb_users where username ='TEST';
USERNAME COM CON_ID
------------------ -------- ----------------
TEST NO 3
SQL> conn test/test@test12c:1521/new ;
Connected.
Same rules and conditions applied on Roles if you created in Root Container it will be replicated to pluggable database, on other hand if you created in Pluggable database it will be local without effecting Container.
Let's connect to Root Container
SQL> conn / as sysdba
Connected.
SQL> create role c##root_role ;
Role created.
SQL> select role, common, con_id from cdb_roles where role='C##ROOT_ROLE';
ROLE COM CON_ID
--------------------- ---------- ----------
C##ROOT_ROLE YES 1
C##ROOT_ROLE YES 3
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create role test2;
Role created.
SQL> select role, common, con_id from cdb_roles where role='TEST2';
ROLE COM CON_ID
---------------- -------- ----------
TEST2 NO 3
SQL> create role hr container=all ;
create role hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
SQL> create user hr identified by hr container=all ;
create user hr identified by hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
---> you cannot create a common role inside a PDB.
Check user privileges :
SQL> select grantee, privilege, common, con_id from cdb_sys_privs where privilege='CREATE SESSION' and grantee='TEST';
GRANTEE PRIVILEGE COM CON_ID
-------------- ------------------------ ---------- -----
TEST CREATE SESSION NO 3
Drop Pluggable Database
SQL > drop pluggable database new including datafiles;
Pluugable database dropped.
Move Datafiles Online 12c
For the first time in Oracle Database you can move objects online without offline or shutdown database which is very useful and helpful for any DBA's
Check the example below :
SQL> select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db12c/system01.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf
Let's move system01.dbf
SQL> alter database move datafile '/u01/app/oracle/oradata/db12c/system01.dbf' to '/u01/system.dbf';
SQL> select File_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/u01/system.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf