Manage Temporary Tablespace in Container Database (CDB) and Pluggable Database (PDB)

Both CDB and PDB has its own dedicated TEMP tablespace.

In a 12cR1, both CDB and PDB have own dedicated TEMP tablespace by default

SQL> show con_id con_name

CON_ID
---------
1

CON_NAME
---------
CDB$ROOT

SQL> select CON_ID,NAME from v$tablespace where name='TEMP';

CON_ID      NAME
---------- -------------
 1         TEMP
 2         TEMP
 3         TEMP

Default Temporary Tablespace in PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP OLTP_TEMP;

Tablespace altered.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE OLTP_TEMP;

Pluggable database altered.

Check the default Temporary Tablespace for PDB:

SQL> show con_id con_name

CON_ID
---------
3

CON_NAME
---------
PDB1

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME           PROPERTY_VALUE  DESCRIPTION
----------------------- --------------- --------------------------
DEFAULT_TEMP_TABLESPACE OLTP_TEMP       Name of default temporary 
                                        tablespace

Check the default Temporary Tablespace for CDB as below, we can see CDB is still using its default temporary tablespace TEMP.

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT

SQL> select * 
       from database_properties 
      where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME             PROPERTY_VALUE  DESCRIPTION
------------------------- --------------- -------------------------
DEFAULT_TEMP_TABLESPACE   TEMP            Name of default temporary
                                          tablespace

How to Fix Incomplete RDBMS DST Upgrading in 12c

It is a good practice to raise a service request for issues of DST upgrading in production environment.

There is a database upgraded from 11.2.0.4 to 12.1.0.2 by using “Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)”. For some reason, the DST upgrading was not complete successfully after the upgrading checking:

Current DST state is still “Upgrade”.

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME LIKE 'DST_%'
    ORDER BY PROPERTY_NAME;

PROPERTY_NAME			    VALUE
----------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION		    18
DST_SECONDARY_TT_VERSION	    14
DST_UPGRADE_STATE		    UPGRADE

Try to end the upgrading, still got issues.

SQL>shutdown immediate
SQL>startup

SQL>alter session set "_with_subquery"=materialize;
SQL>alter session set "_simple_view_merging"=TRUE;


SQL>VAR fail number
SQL>BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
Number of failures: 2
BEGIN
*
ERROR at line 1:
ORA-56929: Ending an upgrade window failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1289
ORA-06512: at line 

SQL>SELECT * FROM sys.dst$error_table;

no rows selected

SQL>SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS 
      FROM ALL_TSTZ_TABLES 
     where UPGRADE_IN_PROGRESS='YES';

OWNER            TABLE_NAME                    UPG
---------------- ----------------------------  ----  
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_L YES
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES

Start database normally and run DBMS_DST.UPGRADE_DATABASE.

SQL>set serveroutput on
SQL>VAR numfail number
SQL>BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.



SQL>SELECT * FROM sys.dst$error_table;

no rows selected

End the upgrading.

SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

Check the successful upgrading.

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
      FROM DATABASE_PROPERTIES
     WHERE PROPERTY_NAME LIKE 'DST_%'
  ORDER BY PROPERTY_NAME;  

PROPERTY_NAME             VALUE
------------------------- -------
DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE          NONE


SQL>SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 	     18 	 0

Reference:

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)