DBA Hub

📋Steps in this guide1/5

Multitenant : Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)

This article demonstrates how to manage tablespaces in a container database (CDB) and pluggable database (PDB) in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Manage Tablespaces in a CDB

Management of tablespaces in a container database (CDB) is no different to that of a non-CDB database. Provided you are logged in as a privileged user and pointing to the root container, the usual commands are all available.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CONN / AS SYSDBA

SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> 

CREATE TABLESPACE dummy
  DATAFILE '/u01/app/oracle/oradata/cdb1/dummy01.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;
  
Tablespace created.

SQL>

ALTER TABLESPACE dummy ADD
  DATAFILE '/u01/app/oracle/oradata/cdb1/dummy02.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;
 
Tablespace altered.

SQL> 

DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>
2

Manage Tablespaces in a PDB

The same tablespace management commands are available from a pluggable database (PDB), provided you are pointing to the correct container. You can connect using a common user then switch to the correct container. Alternatively, connect directly to the PDB as a local user with sufficient privilege. Once pointed to the correct container, tablespaces can be managed using the same commands you have always used. Make sure you put the datafiles in a suitable location for the PDB.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SESSION SET CONTAINER = pdb1;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL>

SQL> CONN pdb_admin@pdb1
Enter password: 
Connected.
SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL>

CREATE TABLESPACE dummy
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy01.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;
  
Tablespace created.

SQL>

ALTER TABLESPACE dummy ADD
  DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/dummy02.dbf' SIZE 1M
  AUTOEXTEND ON NEXT 1M;
 
Tablespace altered.

SQL> 

DROP TABLESPACE dummy INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>
3

Undo Tablespaces

Management of the undo tablespace in a CDB is unchanged from that of a non-CDB database. In contrast, a PDB can not have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible. But we can see the datafile associated with the CDB undo tablespace.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CONN pdb_admin@pdb1

SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS

SQL>

SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

SQL>

SELECT name FROM v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/temp01.dbf

SQL>
4

Temporary Tablespaces

Management of the temporary tablespace in a CDB is unchanged from that of a non-CDB database. A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CONN pdb_admin@pdb1

CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/app/oracle/oradata/cdb1/pdb1/temp02.dbf' SIZE 5M
  AUTOEXTEND ON NEXT 1M;
  
Tablespace created.

SQL>

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL>
5

Default Tablespaces

Setting the default tablespace and default temporary tablespace for a CDB is unchanged compared to a non-CDB database. There are a two ways to set the default tablespace and default temporary tablespace for a PDB. The command is the recommended way. For backwards compatibility, it is also possible to use the command. With both methods, you should be pointing to the appropriate container for the command to work. For more information see: - Multitenant : All Articles - Multitenant : Manage Tablespaces in a CDB and a PDB - Introduction to the Multitenant Architecture - Overview of the Multitenant Architecture - Managing a Multitenant Environment - CREATE TABLESPACE - ALTER TABLESPACE - DROP TABLESPACE - ALTER PLUGGABLE DATABASE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
CONN pdb_admin@pdb1
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;

CONN pdb_admin@pdb1
ALTER DATABASE DEFAULT TABLESPACE users;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!