Oracle 12C 本地临时表空间
说明
在12.2中引入了一个新的概念“本地临时表空间”，而之前版本中的临时表空间则被称为“共享临时表空间”。
当很多read-only实例访问一个数据库时，本地临时表空间可以提高涉及到排序查询的性能。
共享的临时表空间在共享磁盘上存储临时文件，以便所有数据库实例都可以访问临时表空间。相反，本地临时表空间为每个数据库实例存储单独的、非共享的临时文件。本地临时表空间对于Oracle Real Application Clusters 或 Oracle Flex Clusters很有用。
可以在read-only和read/write数据实例上创建本地临时表空间。
临时表空间优点：
1. Improving I/O performance
2. Avoiding expensive cross-instance temporary space management
3. Improving instance startup performance
本地临时表空间和共享临时表空间对比
|
Shared Temporary Tablespace |
Local Temporary Tablespace |
|
Created with the CREATE TEMPORARY TABLESPACEstatement. |
Created with the CREATE LOCAL TEMPORARY TABLESPACE statement. Note: A local temporary tablespaces is always a bigfile tablespace, but the BIGFILE keyword is not required in the creation statement. |
|
Creates a single temporary tablespace for the database. |
Creates separate temporary tablespaces for every database instance. The FOR LEAF option creates tablespaces only for read-only instances. The FOR ALL option creates tablespaces for all instances, both read-only and read/write. |
|
Supports tablespace groups. |
Does not support tablespace groups. |
|
Stores temp file metadata in the control file. |
Stores temp file metadata common to all instances in the control file, and instance-specific metadata (for example, the bitmaps for allocation, current temp file sizes, and file status) in the SGA. |
具体例子
---创建本地临时表空间，和创建普通临时表空间没什么区别，多了一个关键字“local”和“FOR ALL or FOR LEAF”
SQL> CREATE LOCAL TEMPORARY TABLESPACE FOR ALL cndba_local_temp
TEMPFILE '/u01/app/oracle/oradata/orcl/cndba_local_temp01.dbf' SIZE 5M AUTOEXTEND ON;
Tablespace created.
