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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,501 other followers

%d bloggers like this: