Create a tablespace in #DB12C

I’ve been messing with Oracle Database 12c (12.1.0.1) today. As I’ve been working at setting up a few things to test, I realized I need to create a tablespace or two and I didn’t have a post on how to create tablespaces with Oracle Database 12c.

Before I can do any testing, I have to setup a few tablespaces for the SLOB testing tool. Below is how to simply create tablespaces using Oracle Managed Files (OMF) within Oracle Database 12c.

Traditional/Container Database

This is a top level database. For a traditional style database (non-CDB), created a tablespace is just like in previous versions.

create tablespace SLOB datafile size 1M autoextend on next 1m;

With a Container Database (CDB), first make sure you are in the correct container. This can be done with either SHOW CON_ID or SHOW CON_NAME.

The CDB is always container id of 1. Using the CON_NAME option, the top level container name is called CDB$ROOT.

Now to create a tablespace in the CDB, you need to be logged in as a SYSDBA. Then you can run the same command to create the tablespace as you did for a traditional database.

create tablespace SLOB datafile size 1M autoextend on next 1m;

Pluggable Databases

To create tablespaces in the pluggable databases (PDB), you need to ensure that you are in the correct container you want to create the tablespace for. PDBs have container ids greater than 2. The CON_NAME option is also a good way to ensure that you are in the correct container as well.

Now that you have confirmed that you are in the correct container; now you can create the tablespace.

create tablespace SLOB datafile size 1M autoextend on next 1m;

How to check where the tablespaces are located

Everyone’s first response for checking on where the location of tablespace are located is to look at DBA_TABLESPACES. Within the Database 12c architecture, this view is valid for the container you are in. Oracle has provided a few new views to use with Database 12c. These views are called the CDB views. If you want to see the tablespaces you created above and in what container they are in, the CDB_TABLESPACES view needs to be used.

Set the container back to CDB$ROOT and verify that you are in the correct container.

Now that you are back in the CDB container lets use the CDB_TABLESPACE view to see what containers you created a SLOB tablespace in.

As you can see the SLOB tablespace has been created in the containers 1, 3 and 4. If you want to find the names for the con_ids, these can be found in the CDB_PDBS view. CON_ID for 2, is for the seed PDB which is used for cloning of PDBs.

Oracle Enterprise Manager 12c view

Everything that has been shown has used SQL and SQL*Developer. The same information can be found in Oracle Enterprise Manager 12c (OEM) under the container database. The screen shot below shows the SLOB tablespaces in OEM.