Handing Tablepsace / Datafiles
in PDB.
--------------------------------------
Creating tablespace in PDB.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id, NAME
, OPEN_MODE from V$pdbs;
CON_ID NAME OPEN_MODE
----------
------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB_PURCHASE READ WRITE
4 PDB_HR READ WRITE
SQL> alter session set
container = pdb_hr;
Session altered.
SQL> create tablespace
hr_tblspc datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' size 50M;
Tablespace created.
SQL> conn / as sysdba
Connected.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select name , status
from V$datafile where con_id = 4;
NAME
STATUS
------------------------------------------------------------
-------
/opt/oracle/oradata/pdb_hr/system01.dbf SYSTEM
/opt/oracle/oradata/pdb_hr/sysaux01.dbf ONLINE
/opt/oracle/oradata/pdb_hr/users01.dbf ONLINE
/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf ONLINE
SQL> alter pluggable
database pdb_hr datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' offline;
alter pluggable database
pdb_hr datafile'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' offline
*
ERROR at line 1:
ORA-65046: operation not
allowed from outside a pluggable database
SQL> alter pluggable
database pdb_hr datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' resize
100M;
alter pluggable database
pdb_hr datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' resize 100M
*
ERROR at line 1:
ORA-65046: operation not
allowed from outside a pluggable database
Need to login to the PDB
container to manage the tablespace/datafile of a particular PDB.
SQL> alter session set
container = pdb_hr;
Session altered.
SQL> alter pluggable
database pdb_hr datafile'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' offline;
Pluggable database altered.
SQL> select name , status
from V$datafile where con_id = 4;
NAME
STATUS
------------------------------------------------------------
-------
/opt/oracle/oradata/pdb_hr/system01.dbf SYSTEM
/opt/oracle/oradata/pdb_hr/sysaux01.dbf ONLINE
/opt/oracle/oradata/pdb_hr/users01.dbf ONLINE
/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf RECOVER
4 rows selected.
SQL> alter pluggable
database pdb_hr datafile'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' online;
alter pluggable database
pdb_hr datafile'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' online
*
ERROR at line 1:
ORA-01113: file 32 needs
media recovery
ORA-01110: data file 32:
'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf'
SQL> alter pluggable
database pdb_hr recover datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf';
Pluggable database altered.
SQL> alter pluggable
database pdb_hr datafile'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' online;
Pluggable database altered.
When connected to a
particular PDB we can use alter database or alter pluggable database, both are
valid options.
SQL> alter pluggable
database pdb_hr datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' resize
55M;
Pluggable database altered.
or
SQL> alter database
datafile '/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf' resize 55M;
Database altered.
SQL> alter tablespace
hr_tblspc offline immediate;
Tablespace altered.
SQL> alter tablespace
hr_tblspc online;
alter tablespace hr_tblspc
online
*
ERROR at line 1:
ORA-01113: file 32 needs
media recovery
ORA-01110: data file 32:
'/opt/oracle/oradata/pdb_hr/hr_tblspc01.dbf'
SQL> recover tablespace
hr_tblspc;
Media recovery complete.
SQL> alter tablespace
hr_tblspc online;
Tablespace altered.
Chaning Default temporary
tablespace
SQL> select
PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like
'%DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------------
----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE
SQL> create temporary
tablespace temp2 tempfile '/opt/oracle/oradata/pdb_hr/temp201.dbf' size 50M;
Tablespace created.
SQL> alter database
default temporary tablespace temp2;
Database altered.
SQL> select
PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like
'%DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------------
----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE
SQL> alter database
default tablespace hr_tblspc;
Database altered.
SQL> select
PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like
'%DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------------
----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2
DEFAULT_PERMANENT_TABLESPACE HR_TBLSPC
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE
Set the storage limit of
PDB.
SQL> show con_name;
CON_NAME
------------------------------
PDB_HR
SQL> select
sum(bytes)/1024/1024 from dba_Data_files;
SUM(BYTES)/1024/1024
--------------------
1815
SQL> alter pluggable
database PDB_HR storage (maxsize 2g);
Pluggable database altered.
SQL> create tablespace
above_storage datafile '/opt/oracle/oradata/pdb_hr/above_storge_limit.dbf' size
2g;
create tablespace
above_storage datafile '/opt/oracle/oradata/pdb_hr/above_storge_limit.dbf' size
2g
*
ERROR at line 1:
ORA-65114: space usage in
container is too high
SQL> alter pluggable
database storage (maxsize unlimited);
Pluggable database altered.
-- Chaning the Global name
of the PDB.
SQL> select name,
open_mode from V$pdbs;
NAME OPEN_MODE
------------------------------
----------
PDB$SEED READ ONLY
PDB_PURCHASE MOUNTED
PDB_HR MOUNTED
SQL> alter pluggable
database PDB_HR open restricted;
Pluggable database altered.
SQL> select name,
open_mode, restricted from V$pdbs;
NAME OPEN_MODE RES
------------------------------
---------- ---
PDB$SEED READ ONLY NO
PDB_PURCHASE MOUNTED
PDB_HR READ WRITE YES
SQL> alter session set
container=pdb_hr;
Session altered.
SQL> alter pluggable
database PDB_HR rename global_name to PDB_HRALL;
Pluggable database altered.
SQL> select name,
open_mode, restricted from V$pdbs;
NAME OPEN_MODE RES
------------------------------
---------- ---
PDB_HRALL READ WRITE YES
SQL> select name from
dba_services;
NAME
----------------------------------------------------------------
pdb_hrall
-- Change the service name
accordingly in tnsnames.ora
SQL> alter pluggable
database pdb_hrall rename global_name to pdb_hr;
Pluggable database altered.
SQL> select name from
dba_services;
NAME
----------------------------------------------------------------
pdb_hr
No comments:
Post a Comment