oracle12c扩充临时表空间,Oracle 12C R2-新特性-本地临时表空间

1说明

在12.2中引入了一个新的概念“本地临时表空间”，而之前版本中的临时表空间则被称为“共享临时表空间”。

https://www.cndba.cn/Expect-le/article/2540

当很多read-only实例访问一个数据库时，本地临时表空间可以提高涉及到排序查询的性能。

共享的临时表空间在共享磁盘上存储临时文件，以便所有数据库实例都可以访问临时表空间。相反，本地临时表空间为每个数据库实例存储单独的、非共享的临时文件。本地临时表空间对于Oracle Real Application Clusters 或Oracle Flex Clusters很有用。

可以在read-only和read/write数据实例上创建本地临时表空间。

临时表空间优点：

1.Improving I/O performanceby using local rather than shared disk storagehttps://www.cndba.cn/Expect-le/article/2540

2.Avoiding expensive cross-instance temporaryspace management

3.Improving instance startup performanceby eliminating on-disk space metadata management

2本地临时表空间和共享临时表空间对比

Shared Temporary Tablespace

https://www.cndba.cn/Expect-le/article/2540https://www.cndba.cn/Expect-le/article/2540

Local Temporary Tablespace

https://www.cndba.cn/Expect-le/article/2540

Created with theCREATE TEMPORARY TABLESPACEstatement.

https://www.cndba.cn/Expect-le/article/2540

Created with the CREATE LOCAL TEMPORARY TABLESPACE statement.

Note:A local temporary tablespaces is always abut 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. TheFOR LEAFoption creates tablespaces only for read-only instances. TheFOR ALLoption creates tablespaces for all instances, both read-only and read/write.

https://www.cndba.cn/Expect-le/article/2540

Supports tablespace groups.https://www.cndba.cn/Expect-le/article/2540

Does not support tablespace groups.https://www.cndba.cn/Expect-le/article/2540

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.

3具体例子

---创建本地临时表空间，和创建普通临时表空间没什么区别，多了一个关键字“local”和“FOR ALL or FOR LEAF”

https://www.cndba.cn/Expect-le/article/2540

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.

版权声明：本文为博主原创文章，未经博主允许不得转载。

本地临时表空间