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; COUNT(EMPNO) ------------ 14
– Verify that temporary tablespace has not been created in PDB_ORCL2
PDB_ORCL2@CDB2> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX USERS EXAMPLE
– 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; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS CDB$ROOT@CDB2> select PROPERTY_NAME, PROPERTY_VALUE from database_properties where upper(PROPERTY_NAME) like '%TEMP%'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ --------------- DEFAULT_TEMP_TABLESPACE 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 PDB_ORCL2@CDB2> l 1 select PROPERTY_NAME, PROPERTY_VALUE from database_properties 2* where upper(PROPERTY_NAME) like '%TEMP%' PDB_ORCL2@CDB2> / PROPERTY_NAME PROPERTY_VALUE ------------------------------ --------------- DEFAULT_TEMP_TABLESPACE 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.
5 thoughts on “12c : PDB cannot share CDB’s temporary tablespace”
What does Oracle Support says about that ?
Haven’t submitted SR.
Well done. Thanks for sharing!
Hi Anuj Mam,
Yes even I came to know this today when I was trying to make CDB temporary tablespace as one of PDB Default temporary tablespace.
Thanks for Post
I think, this is the problem here:
PDB to Use Global CDB (ROOT) Temporary Tablespace Functionality is Missing – https://support.oracle.com/epmos/faces/DocumentDisplay?id=2004595.1