EM12c comes to the rescue when you need a Tablespace for the new Application


I like to combine all Oracle Database 12c Tools: SQL*Plus, SQL Developer, and Oracle Enterprise manager Database Express 12 – EM12c. While I am working on a project, this allows me to switch fast from one tool to the other especially when I need to run any DDL statements, then go back to SQL Developer and create and test some PL/SQL code. Usually, EM12c is extremely fast for the tablespace creation or its management, especially when the new project starts.

task#9 In this task I will demonstrate the fastest way to create a tablespace when you  needed it the most.

step# Connect to the pluggable database PDBORCL through EM12c using address “localhost:5502/em”. Ports 5500 and 5502 are by default reserved and assigned to the CDB$ROOT and PDBORCL databases accordingly on my Oracle Database 12c.

EM12c Connect To PDBORCL

EM12c Connect To PDBORCL

step#2 Open the Tablespace control window.

The Pluggable Database Tablespaces

The Pluggable Database Tablespaces

At the moment, we have 6 tablespaces: SYSTEM, SYSAUX, USERS, TEMP, EXAMPLE, and COMPANYAB.

step#3 Click on the Action Button and choose the Create option.

The Action Button

The Action Button

There are other options too, we can Drop any of the existing tablespaces, change the Status of the tablespace, Add a new datafile to the existing tablespace, and set a tablespace as Default one.

step#4 In the open Create tablespace window, enter the name of the new tablespace, in this case “COMPANYCD”, then click “>” button and follow the procedure.

Create a New Tablespace

Create a New Tablespace

step#5 See what SQL “CREATE TABLESPACE” statement will be executed, and make sure that all parameters are correct, such as the name of the datafile, size, logging, and compression. And if it is all as it should be, press OK button to execute that DDL statement.

The SQL CREATE TABLESPACE statement

The SQL CREATE TABLESPACE statement

step#6 The new tablespace has been created successfully.

The Tablespace Successful Creation

The Tablespace Successful Creation

step#7 Finally, The list of tablespaces  has a new member, the tablespace “COMPANYCD”, and we are ready to start a new app.

The List Of the Current Tablespaces

The List Of the Current Tablespaces

step#8 Check with SQL*Plus that the tablespace datafile “COMPANYCD.DBF” is in the right directory on your computer

c:\TEMP>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 9 00:42:10 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > select dbms_xdb.getHttpPort() from dual;

DBMS_XDB.GETHTTPPORT()

----------------------

0

SYS@orcl > select dbms_xdb_config.getHttpsPort() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()

------------------------------

5500

SYS@orcl > host

Microsoft Windows [Version 6.3.9600]

(c) 2013 Microsoft Corporation. All rights reserved.

c:\TEMP>dir C:\app\orauser\oradata\orcl\pdborcl

Volume in drive C has no label.

Volume Serial Number is C2DD-F7B2

Directory of C:\app\orauser\oradata\orcl\pdborcl

09/09/2015 01:00  .

09/09/2015 01:00  ..

06/09/2015 22:41 104,865,792 COMPANYAB.DBF

09/09/2015 01:00 104,865,792 COMPANYCD.DBF

06/09/2015 22:41 1,456,218,112 EXAMPLE01.DBF

06/09/2015 22:32 20,979,712 PDBORCL_TEMP012014-12-09_02-14-58-AM.DBF

07/09/2015 18:54 22,290,432 SAMPLE_SCHEMA_USERS01.DBF

06/09/2015 22:41 734,011,392 SYSAUX01.DBF

06/09/2015 22:41 304,095,232 SYSTEM01.DBF

7 File(s) 2,747,326,464 bytes

2 Dir(s) 562,110,873,600 bytes free

c:\TEMP>exit

SYS@orcl >

Summary

The Oracle Corporation recommends that the separate tablespaces should be created for any customer new applications in order to guarantee isolation, security, and easy management of the application and its tablespaces.

In this task I have shown how fast and easy is to create a tablespace on the fly using EM12c when you are desperate to start a new project.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s