Skip navigation.

DBA Blogs

12c : PDB cannot share CDB’s temporary tablespace

Oracle in Action - Tue, 2014-04-29 00:19

RSS content

As per Oracle 12c documentation,  a PDB can

- have its own local temporary tablespace, or

- if it does not have its own  temporary tablespace, it can share the temporary tablespace with the CDB.

To demonstrate a PDB sharing the temporary tablespace of CDB,  the  first step is to create a PDB without a temporary tablespace. By default when a PDB is created from seed, it is created with its local temporary tablespace TEMP and it cannot be dropped as it is the default temporary tablespace of the PDB.

So, the only options I could think of were to

Method – I

  • Create a non-CDB without temporary tablespace and the plug it into a CDB

Method – II

  • Create a non-CDB / PDB with temporary tablespace,
  • Generate its xml file using dbms_PDB
  •  Edit the xml file to remove the entry for temporary tablespace
  •   Plug in the non-CDB into a CDB

I will demonstrate the second method. For this purpose, I created a non-CDB orcl2 using DBCA so that it contained default temporary tablespace temp.

– Open the non-CDB in read only mode

ORCL2> shu immediate;
             startup mount;
             alter database open read only;

– Generate an XML metadata file for the non-CDB

ORCL2>exec dbms_pdb.describe ('/u01/app/oracle/oradata/orcl2/orcl2.xml');

– Edit the xml file to remove the entry for temp tablespace

[oracle@em12 ~]$ vi /u01/app/oracle/oradata/orcl2/orcl2.xml

– Use the xml file to plug in the non-CDB into  CDB2 as PDB_ORCL2

CDB$ROOT@CDB2>create pluggable database pdb_orcl2 using '/u01/app/oracle/oradata/orcl2/orcl2.xml'  nocopy;

– Connect to PDB_ORCL2 and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

CDB$ROOT@CDB2>connect sys/oracle@pdb_orcl2 as sysdba

PDB_ORCL2@CDB2> @?/rdbms/admin/noncdb_to_pdb

PDB_ORCL2@CDB2> alter pluggable database open;

– Check that data from non-CDB is available in the new PDB

PDB_ORCL2@CDB2>select count(empno) from scott.emp;


– Verify that temporary tablespace has not been created in PDB_ORCL2

PDB_ORCL2@CDB2> select tablespace_name from dba_tablespaces;


– Verify that tempfile has not been created in PDB_ORCL2

PDB_ORCL2@CDB2> select name from v$tempfile;

no rows selected

So, I was able to create a PDB without temporary tablespace. Now I wanted to check if PDB_ORCL2 uses the temp tablespace of the CDB.

– First check that default temporary tablespace TEMP exists for the CDB

CDB$ROOT@CDB2> select tablespace_name from dba_tablespaces;



from database_properties
where upper(PROPERTY_NAME) like '%TEMP%';

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

– Set pga_aggregate_target to its lowest allowed value of 10m to force a sort to spill to the temporary tablespace.

 CDB$ROOT@CDB2> alter system set pga_aggregate_target=10m;

– Issue a query in PDB_ORCL2 which will spill to temporary tablespace

PDB_ORCL2@CDB2> select * from dba_objects order by 1,2,3,4,5,6,7;
select * from dba_objects order by 1,2,3,4,5,6,7
ERROR at line 1:
ORA-00959: tablespace 'TEMP' does not exist

I expected the query to use temporary tablespace TEMP of CDB but it failed as it expected temp tablespace to exist locally for PDB as its default temporary tablespace is set to TEMP (as was in non-CDB)

PDB_ORCL2@CDB2> col property_name for a30
PDB_ORCL2@CDB2> col property_value for a15
1  select PROPERTY_NAME, PROPERTY_VALUE from database_properties
2*  where upper(PROPERTY_NAME) like '%TEMP%'

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

 – I tried to modify the default temporary tablespace to cdb$root:temp but that attempt also failed.

PDB_ORCL2@CDB2> alter database default temporary tablespace cdb$root:temp;
alter database default temporary tablespace cdb$root:temp
ERROR at line 1:
ORA-00933: SQL command not properly ended

Hence, I was able to create a PDB without temporary tablespace but could not make it use the temporary tablespace of CDB.


Oracle documentation


Related Links:


Database 12c Index




Comments:  3 comments on this itemYou might be interested in this:  
Copyright © ORACLE IN ACTION [12c : PDB cannot share CDB's temporary tablespace], All Right Reserved. 2014.

The post 12c : PDB cannot share CDB’s temporary tablespace appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Watch: In-Memory Option for Oracle 12c

Pythian Group - Mon, 2014-04-28 08:04

Back in September at Oracle OpenWorld 2013, Larry Ellison announced the Oracle Database In-Memory Option to Oracle 12c. Today, one of Pythian’s Advanced Technology Consultants, Christo Kutrovsky shares his thoughts on the new feature in our latest video. Stay tuned for updates while it’s being tested out in the field.

Categories: DBA Blogs

Log Buffer #368, A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2014-04-28 08:03

The beats of big data and symphonies of small data are creating a dazzling contrast in the realm of databases. Oracle, SQL Server, and MySQL are creating new tunes and all these tonal qualities are being captured by this Log Buffer Edition. Relax, sit back and enjoy.


This blog shows how you can write a SQL query to recommend products (cross-sell) to a customer based on products already placed in his current shopping cart.

R provides a rich set of statistical functions that we may want to use directly from SQL. Many of these results can be readily expressed as structured table data for use with other SQL tables, or for use by SQL-enabled applications, e.g., dashboards or other statistical tools.

Insight in the number, type and severity of errors that happen in a test or production environment is crucial to resolve them, and to make a stable ADF application that is less error-prone.

In order to get meaningful database statistics, the TIMED_STATISTICS parameter must be enabled for the database instance. The performance impact of having TIMED_STATISTICS enabled is minimal compared to instance performance.

As you all know that ADS is basically a push technology which send data from server to client without any user intervention . However client will send request periodically to server ask for update. Which further configure in adf-config.xml.

SQL Server:

SQL Server 2014 and the DBA: Building Bridges.

RS, SharePoint and Forefront UAG Series – Intro.

Introducing the Microsoft Analytics Platform System – the turnkey appliance for big data analytics.

Progressive Insurance data performance grows by factor of four, fueling business growth online experience.

Version 9.04.0013 of the RML Utilities for x86 and x64 has been released to the download center.


Since the dawn of time, MySQL indexes have a limit: they cannot be descending. Yes, ALTER TABLE and CREATE INDEX can contain the ASC and DESC keywords.

It’s now 3 weeks since the MariaDB & MySQL community day in Santa Clara.

FromDual.en: MySQL Environment MyEnv 1.0.3 has been released.

Managing Percona Xtradb Cluster with Puppet.

Every Relation is in First Normal Form, by definition. Every Table may not.

Categories: DBA Blogs

Partner Webcast – Oracle Webcenter: Center of Engagement – Case Study: Information Strategy implementing Webcenter in Educational Institutions

Oracle WebCenter is the user engagement platform for social business. It has the ability to combine the development of rich internet applications, multi-channel portal frameworks and Enterprise...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Nationwide Deploys Database Applications 600% Faster

Pankaj Chandiramani - Mon, 2014-04-28 03:37

Nationwide Deploys Database Applications 600% Faster

Heath Carfrey of Nationwide, a leading global insurance and
financial services organization, discusses how Nationwide saves time and
effort in database provisioning with Oracle Enterprise Manager

Key-points :

  1. Provisioning Databases using Profiles  (aka Gold Images)

  2. Automated Patching

  3.  Config/Compliance tracking

Categories: DBA Blogs

EMCLI setup

Pankaj Chandiramani - Mon, 2014-04-28 02:15

A quick note on how to install EMCLI which is used for various CLI operations from EM . I was looking to test some Database provisioning automation via EMCLI and thus was looking to setup the same . 

To set up EMCLI on the host, follow these steps:
1.    Download the emcliadvancedkit.jar from the OMS using URL https://<omshost>:<omsport>/em/public_lib_download/emcli/kit/emcliadvancedkit.jar
2.    Set your JAVA_HOME environment variable and ensure that it is part of your PATH. You must be running Java 1.6.0_43 or greater. For example:
o    setenv JAVA_HOME /usr/local/packages/j2sdk
o    setenv PATH $JAVA_HOME/bin:$PATH
3.    You can install the EMCLI with scripting option in any directory either on the same machine on which the OMS is running or on any machine on your network (download the emcliadvancedkit.jar to that machine)
java -jar emcliadvancedkit.jar client -install_dir=<emcli client dir>
4.    Run emcli help sync from the EMCLI Home (the directory where you have installed emcli) for instructions on how to use the "sync" verb to configure the client for a particular OMS.
5.    Navigate to the Setup menu then the Command Line Interface. See the Enterprise Manager Command Line Tools Download page for details on setting EMCLI.

Categories: DBA Blogs

PageView Count

Hemant K Chitale - Sat, 2014-04-26 08:41
PageView count of this blog, Top 10 Countries

Country              PageViews
United States      250,986India              116,730United Kingdom      37,784Germany             29,809France              20,570Canada              15,333Russia              13,650Brazil               5,884Australia            4,840Singapore            4,647
Categories: DBA Blogs

The people behind the NoCOUG Journal

Iggy Fernandez - Fri, 2014-04-25 12:53
Now in its 28th year, the NoCOUG Journal is the oldest Oracle user group publication in the world. No other small user group in the world has a printed journal. Most large user groups do not have printed journals either. But little NoCOUG does. I am the editor of the NoCOUG Journal and—I must confess—I get sad when I see […]
Categories: DBA Blogs

Useful Oracle 12c OCP exam blog post

Bobby Durrett's DBA Blog - Fri, 2014-04-25 10:54

I found this blog post about the Oracle 12c OCP exam useful: url

In particular it explained why my Kaplan SelfTest software only covers the new 12c features and not general DBA skills section of the OCP exam.

The Kaplan software I purchased has 50 questions and they are only about new features.  The software showed me the gaps in my 12c new features knowledge and gave me practice taking a multiple choice computerized test and I believe the value of these benefits exceed the $99 cost of the software.  But, the software surprised me when I discovered that it didn’t cover all the areas that will be on the OCP 12c upgrade exam.  The blog post I’ve referenced explains that in the near future Transcender will produce software that includes both sections of the OCP 12c upgrade exam.

- Bobby

Categories: DBA Blogs

Developing a Data Infrastructure Sourcing Strategy

Pythian Group - Fri, 2014-04-25 07:51

Today’s blog post is the second of three in a series dedicated to data infrastructure outsourcing, with excerpts from our latest white paper.

The trends in our first blog post reveal that enterprises are looking for outside help with their data infrastructure not to reduce costs, but to:

  • Improve service quality
  • Increase productivity
  • Access specialized skills
  • Increase innovation

In the full white paper, we examine some of the strengths and weaknesses of the three sourcing options available to help you realize these benefits.


Having data management experts on staff provides organizations with greater control over their work. Additionally, in-house experts have in-depth knowledge about their specific systems and processes. However, relying on in-house resources alone presents multiple challenges for an organization.


There’s no doubt that offshore providers have offered cost savings in the past. However, the fact that many companies are now reshoring their operations indicates that those benefits did not compensate for the downsides of this approach—typically reduced productivity.


The right outsourcing vendor can deliver the benefits of the other two sourcing models without the equivalent downsides. For a deeper analysis of the strengths and weaknesses of the three sourcing options, download the rest of our white paper, Data Infrastructure Outsourcing.

Read the first blog post in this series, The Growing Trend Toward Data Infrastructure Outsourcing.

Categories: DBA Blogs

Oracle Traffic Director - Part 1: Introduction

Enterprises need to support high transaction volumes and run their business applications on a highly available systems while delivering maximum performance. Oracle provides Engineered Systems which...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Information Lifecycle Management

DBA Scripts and Articles - Thu, 2014-04-24 14:22

Presentation of Information Lifecycle Management (ILM) With data volume growing, finding adapted storage solutions to storage costs and performance objectives is a real challenge for IT department in large companies. Information Lifecycle management is about managing the data all along its useful life while offering the best performance and storage cost as low as possible. The [...]

The post Information Lifecycle Management appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Upgrading Certification to 12c

Hemant K Chitale - Thu, 2014-04-24 09:01
Here's an Oracle Webinar on upgrading certification to 12c
Here are notes by Matthew Morris on the upgrade for 9i/10g/11g OCAs : A Lifeline for 9i and 10g OCAs
Categories: DBA Blogs

12c: Connecting to CDB/PDB – Set Container Vs Connect

Oracle in Action - Thu, 2014-04-24 03:29

RSS content

In Oracle 12c, you can connect to a PDB using two methods :

- Switch the container using Alter system set container …

- Use connect command to connect to PDB using network alias

Let’s compare the two methods :

The use of SET CONTAINER avoids the need to create a new connection from scratch.

If there is an existing connection to a PDB / CDB$root, the same connection can be used to connect to desired PDB / CDB$root.

– Connect to CDB

[oracle@em12 ~]$ sqlplus system/oracle@cdb1

CDB$ROOT@CDB1> sho con_name


– Check the PID for the process created on the operating system

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)

– Change the container to PDB1 using Set container

CDB$ROOT@CDB1> alter session set container=pdb1;

sho con_name


– Check that the operating system PID remains the same as earlier connection is reused and a new connection has not been created

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23271 1 0 10:23 ? 00:00:00 oraclecdb1 (LOCAL=NO)

– Switch the container back to cdb$root using connect

CDB$ROOT@CDB1> conn system/oracle@cdb1
sho con_name


– Check that a new operating system PID has been created as a new  connection  has been created

[oracle@em12 ~]$ ps -ef |grep LOCAL |grep -v grep

oracle 23409 1 0 10:29 ? 00:00:00 oraclecdb1 (LOCAL=NO)
glogin.sql is not executed when Alter session set container is used

To demonstrate it, I have added following lines to my glogin.sql to display CDB/PDB name in SQL prompt:

define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'con_name') || '@' || sys_context('userenv', 'db_name')) global_name from dual;
set sqlprompt '&gname> '
set heading on
set termout on

- Let’s connect to PDB1 using “Connect” and verify that glogin.sql is executed and prompt displays CDB/PDB name


SQL> conn sys/oracle@pdb1 as sysdba

- Verify that the prompt displays current container (PDB1) and container database (CDB1)

PDB1@CDB1> sho con_name

PDB1@CDB1> sho parameter db_name
db_name                              string      cdb1

– Now let’s connect to PDB2 using Alter session set container and verify that glogin.sql is not executed and the same prompt as earlier is displayed

PDB1@CDB1>  alter session set container=pdb2;

 Session altered.
PDB1@CDB1> sho con_name


-- Let's connect to PDB2 using connect and verify that glogin.sql is executed as the prompt displays the PDB name PDB2

 PDB1@CDB1> connect sys/oracle@pdb2 as sysdba

Pending transactions are not committed when Alter system set container is used

– Let’s start a transaction in PDB1

PDB1@CDB1> create table pdb1_tab(x number);
Table created.
PDB1@CDB1> insert into pdb1_tab values (1);
1 row created.

– Switch the container to PDB2

PDB1@CDB1> alter session set container=pdb2;

– Try to start another transaction on PDB2 – does not allow as an active transaction exists in the parent container PDB1

PDB1@CDB1> create table pdb2_tab (x number);

 create table pdb2_tab (x number)


 ERROR at line 1:

 ORA-65023: active transaction exists in container PDB1

– In another session check that the transaction was not committed and no rows are visible in table pdb1_tab

CDB$ROOT@CDB1> conn system/oracle@pdb1

 PDB1@CDB1> select * from pdb1_tab;
no rows selected
 Alter session set container cannot be used by local users


– Try to give set container privilege to a local user HR in PDB2 – fails as common privilege cannot be granted to a local user and hence a local user cannot user alter session set container to connect to another PDB

PDB2@CDB1> connect system/oracle@pdb2

 PDB2@CDB1> grant set container to hr container=all;

 grant set container to hr container=all


 ERROR at line 1:

 ORA-65030: one may not grant a Common Privilege to a Local User or Role

I hope this post was useful.

Your comments and suggestions are always welcome.

References :


Related Links:


Database 12c Index




Comments:  0 (Zero), Be the first to leave a reply!You might be interested in this:  
Copyright © ORACLE IN ACTION [12c: Connecting to CDB/PDB - Set Container Vs Connect], All Right Reserved. 2014.

The post 12c: Connecting to CDB/PDB – Set Container Vs Connect appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Estimate Index Size With Explain Plan (I Can’t Explain)

Richard Foote - Wed, 2014-04-23 23:22
I discussed recently an updated MOS note that details the needs vs. the implications of rebuilding indexes. Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime […]
Categories: DBA Blogs

Lead DBA Position Phoenix Arizona with PeopleSoft

Bobby Durrett's DBA Blog - Wed, 2014-04-23 17:13

My company has posted a Lead Oracle DBA position located in Phoenix, Arizona which is where I also live.

You have to apply through our web site using this link:

We would love to get someone who has PeopleSoft skills.

You would be joining a friendly and experienced team of Oracle and SQL Server DBAs who support a wide variety of applications.  I’ve been here eight years and the time has expanded my scope by exposing me to data warehouse and customer facing web applications that I had not previously supported.  It’s a good position for a qualified person.

- Bobby

Categories: DBA Blogs

Data toons: Cirque du DBA

Pythian Group - Wed, 2014-04-23 09:09

It’s not uncommon for database administrators (DBAs) to feel like ring masters at the circus. But what happens when you free up in-house DBAs by outsourcing database management?
Cirque cartoon

This work is licensed under a Creative Commons Attribution-NoDerivs 3.0 Unported License. Based on a work at

Categories: DBA Blogs

Presenting at ODTUG Kscope14 Conference in Seattle June 22-26 2014

Richard Foote - Tue, 2014-04-22 19:21
  Just a short note to say I’ll be presenting at the Oracle Development Tools User Group (ODTUG) Kaleidoscope 14 Conference this year in beautiful Seattle, Washington on June 22-26 2014. I had a fantastic time when I attended this conference a few years ago when it was held in Monterey so I’m really looking forward to […]
Categories: DBA Blogs

Result Cache concept and benefits

DBA Scripts and Articles - Tue, 2014-04-22 15:46

This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data. The Result Cache feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many [...]

The post Result Cache concept and benefits appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Quick documentation for new PeopleSoft DBAs

Bobby Durrett's DBA Blog - Tue, 2014-04-22 11:10

I did a quick survey of the latest PeopleSoft manuals to find a set of links to pass on to a couple of coworkers of mine that are interested in doing PeopleSoft DBA work so I thought I’d include the links in a post.  This might give a new PeopleSoft DBA some highlights without having to read the entire manual set.

This page has a nice picture of how the environments connect:

This is the top level URL for the PeopleTools 8.53 documentation:

Another nice architecture diagram:

Nice overview of application development using app designer:

Yet another architecture diagram:

More in depth view of app server and its processes:

Web server with discussion of servlets and jolt:

Nice overview of datamover commands:

Datamover basics

Nice explanation of Oracle connections from PeopleSoft:

Good to know but not very clear explanation:

Important to know but not very clear:


Starting psadmin

Nice run down of config files:

App server menu:

process scheduler menu:

web server menu – I don’t think I’ve ever used this:

- Bobby


Categories: DBA Blogs