12C关于CDB、PDB 临时temp表空间的总结

官方文档

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/logical-storage-structures.html#GUID-8EB47FD3-2C96-4130-98B7-36E0B1DA0486

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-tablespaces.html#GUID-9B6544CD-BFF3-4EA8-B06B-0CBC5A91EA45

1、每个容器都有属于的自己的temp表空间，PDB不共享CDB的temp表空间，CDB有自己的temp表空间，每个PDB也有自己的tmep表空间

2、当前容器下创建的tempfile只属于当前容器的temp表空间， 也就是说CDB下创建的tempfile只属于CDB，某个PDB创建的tempfile只属于这个PDB自己

3、 当前容器 可以删除CDB和任意PDB的tempfile，也就是说CDB可以删除任意PDB的tempfile，PDB也可以删除CDB和任意PDB的tempfile

4、我们平时所说的temp表空间不做特殊说明就是指共享临时表空间，12.2开始引入了"本地临时表空间"的概念，“本地临时表空间"必须是BIGFILE表空间并且不支持tablespace groups表空间组的形式，几乎很少情况下会用到"本地临时表空间”,一般没什么大用，主要用于Oracle Real Application ClustersOr和acle Flex Clusters

5、CDB和任意PDB都可以创建本地临时表空间，创建本地临时表空间和创建普通临时表空间语法上没什么区别，就是TEMPORARY TABLESPACE前面多一个"local"，后面多一个"FOR ALL"或"FOR LEAF"

6、临时文件不能被备份并且没有任何redo信息产生，因此RMAN不会还原或恢复临时文件

SQL> show con_name

CON_NAME

---

CDB$ROOT

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---

     2 PDB$SEED                       READ ONLY  NO

     4 POCP2                          READ WRITE NO

     5 POCP999                        READ WRITE NO

SQL> select FILE_NAME,CON_ID from cdb_temp_files;

FILE_NAME CON_ID

---

/u02/data/test/temp_root1.dbf 1

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_2.dbf 4

/u02/data/test/temp_pocp999.dbf 5

/u02/data/test/temp_pocp999_02.dbf 5

SQL> alter tablespace temp add tempfile ‘/u02/data/test/temp_root1_2.dbf’ size 10M;

SQL> select FILE_NAME,CON_ID from cdb_temp_files; --当前CDB新增加的tempfile只能加入当前CDB

FILE_NAME CON_ID

---

/u02/data/test/temp_root1.dbf 1

/u02/data/test/temp_root1_2.dbf 1

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_2.dbf 4

/u02/data/test/temp_pocp999.dbf 5

/u02/data/test/temp_pocp999_02.dbf 5

SQL> alter session set container=POCP2;

SQL> alter tablespace temp add tempfile ‘/u02/data/test/temp_pocp2_3.dbf’ size 10M;

SQL> select FILE_NAME,CON_ID from cdb_temp_files;–当前PDB新增加的tempfile只能加入当前PDB

FILE_NAME CON_ID

---

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_2.dbf 4

/u02/data/test/temp_pocp2_3.dbf 4

SQL> alter session set container=CDB$ROOT;

SQL> select FILE_NAME,CON_ID from cdb_temp_files;

FILE_NAME CON_ID

---

/u02/data/test/temp_root1.dbf 1

/u02/data/test/temp_root1_2.dbf 1

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_2.dbf 4

/u02/data/test/temp_pocp2_3.dbf 4

/u02/data/test/temp_pocp999.dbf 5

/u02/data/test/temp_pocp999_02.dbf 5

SQL> alter tablespace temp drop tempfile ‘/u02/data/test/temp_pocp2_2.dbf’;–CDB可以删除PDB的tempfile

SQL> select FILE_NAME,CON_ID from cdb_temp_files;

FILE_NAME CON_ID

---

/u02/data/test/temp_root1.dbf 1

/u02/data/test/temp_root1_2.dbf 1

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_3.dbf 4

/u02/data/test/temp_pocp999.dbf 5

/u02/data/test/temp_pocp999_02.dbf 5

SQL> alter session set container=POCP2;

SQL> alter tablespace temp drop tempfile ‘/u02/data/test/temp_root1_2.dbf’; --PDB可以删除CDB的tempfile

SQL> select FILE_NAME,CON_ID from cdb_temp_files;

FILE_NAME CON_ID

---

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_3.dbf 4

SQL> alter session set container=CDB$ROOT;

SQL> select FILE_NAME,CON_ID from cdb_temp_files;

FILE_NAME CON_ID

---

/u02/data/test/temp_root1.dbf 1

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_3.dbf 4

/u02/data/test/temp_pocp999.dbf 5

/u02/data/test/temp_pocp999_02.dbf 5

SQL> alter session set container=pocp2;

SQL> alter tablespace temp drop tempfile ‘/u02/data/test/temp_pocp999_02.dbf’;

–PDB可以删除其他PDB的tempfile

SQL> alter session set container=CDB$ROOT;

SQL> select FILE_NAME,CON_ID from cdb_temp_files;

FILE_NAME CON_ID

---

/u02/data/test/temp_root1.dbf 1

/u02/data/test/temp_pocp2.dbf 4

/u02/data/test/temp_pocp2_3.dbf 4

/u02/data/test/temp_pocp999.dbf 5

SQL> show con_name

CON_NAME

---

CDB$ROOT

SQL> CREATE LOCAL TEMPORARY TABLESPACE for leaf temp_local tempfile ‘/u02/data/test/temp_local_root1.dbf’ size 10M;

CREATE LOCAL TEMPORARY TABLESPACE for leaf temp_local tempfile ‘/u02/data/test/temp_local_root1.dbf’ size 10M

                                  *

ERROR at line 1:

ORA-32778: DDL operations are disabled on local temporary tablespaces FOR LEAF.

SQL> CREATE LOCAL TEMPORARY TABLESPACE for all temp_local tempfile ‘/u02/data/test/temp_local_root1.dbf’ size 10M;

Tablespace created.

SQL> select tablespace_name,extent_management,bigfile,shared,con_id from cdb_tablespaces where contents=‘TEMPORARY’;

TABLESPACE_NAME EXTENT_MAN BIG SHARED CON_ID

---

TEMP LOCAL NO SHARED 1

TEMP_LOCAL LOCAL YES LOCAL_ON_ALL 1

TEMP LOCAL NO SHARED 4

TEMP LOCAL NO SHARED 5

SQL> select file_name,tablespace_name,shared,inst_id,con_id from cdb_temp_files;

FILE_NAME TABLESPACE_NAME SHARED INST_ID CON_ID

---

/u02/data/test/temp_local_root1.dbf_1 TEMP_LOCAL LOCAL_ON_ALL 1 1

/u02/data/test/temp_root1.dbf TEMP SHARED 1

/u02/data/test/temp_pocp2.dbf TEMP SHARED 4

/u02/data/test/temp_pocp2_3.dbf TEMP SHARED 4

/u02/data/test/temp_pocp999.dbf TEMP SHARED 5

SQL> alter session set container=pocp2;

SQL> CREATE LOCAL TEMPORARY TABLESPACE for all temp_local_pocp2 tempfile ‘/u02/data/test/temp_local_pocp2_1.dbf’ size 10M;

Tablespace created.

SQL> select file_name,tablespace_name,shared,inst_id,con_id from cdb_temp_files;

FILE_NAME TABLESPACE_NAME SHARED INST_ID CON_ID

---

/u02/data/test/temp_local_pocp2_1.dbf_1 TEMP_LOCAL_POCP2 LOCAL_ON_ALL 1 4

/u02/data/test/temp_pocp2.dbf TEMP SHARED 4

/u02/data/test/temp_pocp2_3.dbf TEMP SHARED 4

Starting with Oracle Database 12c Release 2 (12.2), local temporary tablespaces are available. A local temporary tablespace stores separate, non-shared temp files for every database instance. A local temporary tablespace is used only for spilling temporary results of SQL statements, such as queries that involve sorts, hash aggregations, and joins. These results are only accessible within an instance. In contrast, a shared temporary tablespace resides on a shared disk and is available to all instances. To create a local temporary tablespace, use a CREATE LOCAL TEMPORARY TABLESPACE statement. Shared temporary tablespaces were available in prior releases of Oracle Database and were called “temporary tablespaces.” In this Oracle Database Administrator’s Guide, the term “temporary tablespace” refers to a shared temporary tablespace unless specified otherwise.

Note:Local temporary tablespaces are new in Oracle Database 12c Release 2 (12.2). In previous releases, shared temporary tablespaces were simply called temporary tablespaces. Starting in this release, the term temporary tablespace refers to a shared temporary tablespace unless specified otherwise.