Oracle Database 12c New Features: Tablespace

Showing posts with label Tablespace. Show all posts

Tablespaces in multitenancy

Note:

In a non-CDB, all the tablespaces belong to one database.

In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces.

Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links.

There are new clauses in the CREATE DATABASE command. The USER_DATA TABLESPACE allows you to specify a default tablespace other than USERS when using DBCA to create a database. This tablespace will also be used for XDB options.

The UNDO tablespace is common to all PDBs, that is, you can have more than one but there is only one active UNDO tablespace per CDB.

To view the all tablespaces

SQL> SELECT tablespace_name, DECODE(pdb_name, NULL, 'ROOT',pdb_name) pdb_name
2 FROM cdb_tablespaces t, cdb_pdbs p
3 WHERE t.con_id = p.pdb_id(+)
4 ORDER BY 2,1;

TABLESPACE_NAME PDB_NAME
------------------------------ --------------------
SYSAUX PDB$SEED
SYSTEM PDB$SEED
TEMP PDB$SEED
SYSAUX PDB2
SYSTEM PDB2
TEMP PDB2
SYSAUX ROOT
SYSTEM ROOT
TEMP ROOT
UNDOTBS1 ROOT
UNDOTBS2 ROOT
USERS ROOT

12 rows selected.

The CREATE TABLESPACE command should be familiar. The change in its behavior in a CDB is that the tablespace is created in the container where the command is executed. Separating the data files into different directories by PDB can help determine which files belong to which PDB, though it is not necessary but it is good from administration perspective.

Create tablespace at CDB

SQL> connect system/oracle@cdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLESPACE cdb_tbs
2 DATAFILE '+DATA/ora12c/cdb_tbs01.dbf'
3 SIZE 5M;

Tablespace created.

Create tablespace at PDB

SQL> connect system/oracle@pdb2
Connected.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> CREATE TABLESPACE pdb2_tbs
2 DATAFILE '+DATA/pdb2/pdb2_tbs01.dbf'
3 SIZE 5M;

Tablespace created.

Tablespaces in multitenancy

Note:

In a non-CDB, all the tablespaces belong to one database.

In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces.

Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links.

There are new clauses in the CREATE DATABASE command. The USER_DATA TABLESPACE allows you to specify a default tablespace other than USERS when using DBCA to create a database. This tablespace will also be used for XDB options.

The UNDO tablespace is common to all PDBs, that is, you can have more than one but there is only one active UNDO tablespace per CDB.

To view the all tablespaces

SQL> SELECT tablespace_name, DECODE(pdb_name, NULL, 'ROOT',pdb_name) pdb_name
2 FROM cdb_tablespaces t, cdb_pdbs p
3 WHERE t.con_id = p.pdb_id(+)
4 ORDER BY 2,1;

TABLESPACE_NAME PDB_NAME
------------------------------ --------------------
SYSAUX PDB$SEED
SYSTEM PDB$SEED
TEMP PDB$SEED
SYSAUX PDB2
SYSTEM PDB2
TEMP PDB2
SYSAUX ROOT
SYSTEM ROOT
TEMP ROOT
UNDOTBS1 ROOT
UNDOTBS2 ROOT
USERS ROOT

12 rows selected.

SQL> connect system/oracle@cdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLESPACE cdb_tbs
2 DATAFILE '+DATA/ora12c/cdb_tbs01.dbf'
3 SIZE 5M;

Tablespace created.

Create tablespace at PDB

SQL> connect system/oracle@pdb2
Connected.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> CREATE TABLESPACE pdb2_tbs
2 DATAFILE '+DATA/pdb2/pdb2_tbs01.dbf'
3 SIZE 5M;

Tablespace created.

To View 12c Tablespaces

To view tablespace and it's corresponding container

SQL> col name form a12
SQL> SELECT file#, tbs.name, tbs.ts#, tbs.con_id
2 FROM v$datafile dbf, v$tablespace tbs
3 WHERE dbf.ts#=tbs.ts#
4 AND dbf.con_id=tbs.con_id;
FILE# NAME TS# CON_ID
---------- ------------ ---------- ------
1 SYSTEM 0 1
2 SYSTEM 0    2
3 SYSAUX 1    1
4 SYSAUX 1    2
5 UNDOTBS1 2 1
6 USERS 4    1
7 SYSTEM 0    3
8 SYSAUX 1    3
9 USERS 3    3
9 rows selected.

Extracted from Oracle 12c Document, CON_ID = 0 mean CDB.

About Viewing Information When the Current Container Is the Root

When the current container is the root, a common user can view data dictionary information for the root and for PDBs by querying container data objects. A container data object is a table or view that can contain data pertaining to the following:

One or more containers
The CDB as a whole
One or more containers and the CDB as a whole

Container data objects include V$, GV$, CDB_, and some Automatic Workload Repository DBA_HIST* views. A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects.

In a CDB, for every DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not.

Each container data object contains a CON_ID column that identifies the container for each row returned. Table 43-1 describes the meanings of the values in the CON_ID column.

Table 43-1 CON_ID Column in Container Data Objects

Value in CON_ID Column	Description
0	The data pertains to the entire CDB
1	The data pertains to the root
2	The data pertains to the seed
3 - 254	The data pertains to a PDB Each PDB has its own container ID.