oracle 创建表空间-Oracle临时表空间作用

关于临时表空间，之前已经聊了一些了，

《》

借鉴老杨的这篇文章《》，了解一下临时表空间究竟是做什么的。

我们知道，的表空间是数据库存储数据和对象的逻辑容器。

根据创建时语句的不同，表空间可分为三种类型，

(1)常规表空间( / ...)，段。

(2) undo表空间( undo)，undo段。

(3) 临时表空间( )，段。

临时表空间主要是存储数据库的排序操作、临时表、中间排序结果等临时对象，其信息不需要REDO，因此临时表的DML操作往往比普通表产生的REDO少很多。临时表数据变化不产生REDO，UNDO数据变化产生REDO。临时段不仅仅存在于临时表空间中，还可能存在普通表空间。例如通过CTAS( table ... as )创建一张表，新表的数据放在临时段中，临时段在CTAS完成时会被转换为段。

7.3推出的新算法的核心就是SEP(SORT POOL)，SEP负责管理临时段中扩展的结构，存储在共享池内，任何需要使用排序空间的操作，都需要从SEP中分配空闲的扩展，使用完毕后，不需要释放该空间，只需要在SEP 中将该扩展设置为空闲。

当数据库实例启动后，SMON将会删除该实例未释放的临时段，并对临时表空间进行碎片整理。在这个操作完成前，数据库打开的操作不能完成。因此每次数据库重启后，临时段中的垃圾都会被完全清理。当数据库打开后，第一个进行的硬盘排序操作会在相关的临时表空间内创建临时段，这个临时段也是整个实例唯一的临时段(在新的临时段算法下oracle 创建表空间，同一个表空间内，每个实例只有一个临时段)。临时段中扩展的信息会被记录在SEP中。硬盘排序操作会在SEP中查找可用的扩展，在查找前，需要获得SORT POOL闩锁。如果能找到可用的扩展，那么SEP中已被分配的扩展就会被标注为占用状态。如果找不到可用的扩展，那么系统就会试图从表空间中分配新的空间，而如果这个分配工作因为表空间的空闲空间不足而无法完成，那么就会产生一个ORA-1652错误。

当排序操作完成的时候，会再次获取SORT POOL闩锁，并且将使用的扩展标注为空闲，然后释放SORT POOL闩锁。新的临时表空间管理算法不需要频繁地分配和释放临时段，这大大提高了临时段管理的效率。由于这是一种只分配不释放的算法，因此DBA经常会看到自己的临时表空间总是处于或者接近100％使用的状态。其实对于7.3以后的版本而言，临时表空间使用率接近100是十分正常的，DBA可以通过V$和V$这两个视图来检查临时段的使用情况。

在RAC中，由于多个实例会共享一个临时表空间。在RAC环境下，每个实例都拥有独立的SEP，各实例中排序需在自己的SEP中分配空间。如SEP中无法分配到足够的空间，若其他的RAC节点实例还有空闲扩展，则可分配给需要的实例使用。

注意：此时虽然进程不会收到ORA-1652错误信息，但在ALTER LOG中会有一个ORA-1652记录。

当排序操作产生临时数据时，数据库并不是立即将其存储在临时表空间中。通常情况下，会先将这些临时数据存储在内存的PGA程序全局区内。只有当这个程序全局区无法容纳全部数据时，数据库系统才会启用临时表空间中的临时段来保存这些数据。

但是众所周知，操作系统从内存中读取数据要比从硬盘中读取数据块几千倍。为此比较理想的情况是，这个程序全局区足够的大，可以容纳所有的临时数据，此时数据库系统就用不到临时表空间了，从而可以提高数据库的功能。但是这毕竟只是一个理想，因为内存大小等多方面的限制，这个PGA程序区的大小往往是有限制的，所以在进行一些大型的排序操作时，这个临时表空间仍然少不了。如今数据库管理员可以做的就是合理设置这个PGA程序全局区的大小，尽量减少临时表空间使用的几率。例如在实际工作中，数据库管理员可以根据需要来设置初始化参数参数，这个参数主要控制这个PGA程序全局区内排序区的大小。通常情况下，如果这个数据库系统主要用来查询并且需要大量的排序、分组汇总、索引等操作时，那么可以合适调整这个参数，来扩大PGA分区的大小。相反，如果这个系统主要用于升级操作，或者在这个数据库服务器上还部署由其他的应用程序，那么这个PGA分区就不能够占用太多的内存，以防止对其他应用程序产生不利的影响。因此，不能一刀切，需要根据实际情况来调整。在必要的情况下，可以增添系统内存来增添PGA分区的大小，从而降低临时表空间的使用几率，以提高数据库的排序、分组汇总等操作的功能。

如果临时段被频繁使用的话，因为内存和硬盘在功能上的差异，从而会降低数据库的性能。为此，数据库管理员还需要监控临时表空间的使用情况，以判断是否需要采取措施来减少临时表空间的使用来提高数据库的查询功能。

默认情况下临时表空间对各个用户都是共享的，就是说每个连接到数据库的用户都可以使用默认的临时表空间。当然可以给不同的用户指定不同的临时表空间。同时还可以使用表空间组，允许用户使用多个表空间中的临时空间。使用表空间组(而不是单个临时表空间)可以缓解由于一个表空间不足以保存排序结果而导致的问题，特别是在具有多个分区的表上，表空间组允许并行执行服务器在单个并行操作中使用多个临时表空间。具有以下特征，

(1) 至少包含一个表空间，对于组中包含的最大表空间数没有明确的限制。

(2) 共享表空间的名称空间，因此其名称不能与任何表空间相同。

(3) 在为数据库分配默认临时表空间或为用户分配临时表空间时，可以在表空间名称出现的任何位置指定表空间组名称。

(4) 不用显式创建表空间组。相反，他是在将第一个临时表空间分配给组时隐式创建的，当该组包含的最后一个临时表空间从中删除时，该组将被删除。

创建临时表空间temp1，并标记临时表空间组，

SQL> create temporary tablespace temp1      tempfile '/opt/oracle/oradata/BISALCDB/BISALPDB1/temp1_01.dbf'      size 10m autoextend on maxsize 1G tablespace group group1;Tablespace created.
SQL> select * from dba_tablespace_groups;GROUP_NAME                     TABLESPACE_NAME------------------------------ ------------------------------GROUP1                         TEMP1

创建临时表空间temp2，并加入到，此时，临时表空间组，包含了两个临时表空间temp1和temp2，

SQL> create temporary tablespace temp2      tempfile '/opt/oracle/oradata/BISALCDB/BISALPDB1/temp2_01.dbf'      size 10m autoextend on maxsize 1G;Tablespace created.
SQL> alter tablespace temp2 tablespace group group1;Tablespace altered.
SQL> select * from dba_tablespace_groups;GROUP_NAME                     TABLESPACE_NAME------------------------------ ------------------------------GROUP1                         TEMP1GROUP1                         TEMP2

了解了临时表空间基本原理，知道了在进行大数量级排序操作时，数据库内存不够时，中间结果会写入临时表空间，当操作完成后，临时表空间就会自动清空释放。经常使用到临时表空间的操作有： index(创建索引)、group by(分组查询)、order by(排序时)、集合运算(union、minus、)、多表连接查询时，当数据库内存不足时oracle 创建表空间，会用到临时表空间。

再来说一下占用临时表空间的几种常见情况，

(1) order by or group by (disc sort占主要部分)。

(2) 索引的创建和重创建。

(3) 操作。

(4) union & & minus sort-merge joins。

(5) 操作。

常用的临时表空间操作管理SQL，

(1) 临时表空间表空间数据文件查看，

SELECT file_name,       bytes / 1024 / 1024 / 1024,       status,       tablespace_name  FROM dba_temp_files;

(2) 如何查看临时表空间都是被什么SQL占用？

  SELECT vt.inst_id,         vs.sid,         vs.serial#,         vs.machine,         vs.saddr,         vs.program,         vs.module,         vs.logon_time,         vt.tempseg_usage,         vt.segtype    FROM gv$session vs,         (  SELECT inst_id,                   username,                   session_addr,                   segtype,                   ROUND (SUM (blocks) * 8192 / 1024 / 1024 / 1024, 2)                      tempseg_usage              FROM gv$tempseg_usage          GROUP BY inst_id,                   username,                   session_addr,                   segtype          ORDER BY 4 DESC) vt   WHERE vs.inst_id = vt.inst_id AND vs.saddr = vt.session_addrORDER BY tempseg_usage DESC;

(3) 查询会话使用临时表空间，

SELECT SE.USERNAME,       SE.SID,       SE.SERIAL#,       SE.SQL_ADDRESS,       SE.MACHINE,       SE.PROGRAM,       SU.TABLESPACE,       SU.SEGTYPE,       SU.CONTENTS   FROM V$SESSION SE, V$SORT_USAGE SU WHERE SE.SADDR = SU.SESSION_ADDR;

或者，

SELECT B.TABLESPACE,       B.SEGFILE#,       B.SEGBLK#,       B.BLOCKS,       B.BLOCKS * 32 / 1024 / 1024,       A.SID,       A.SERIAL#,       A.USERNAME,       A.OSUSER,       A.STATUS,       C.SQL_TEXT,       B.CONTENTS  FROM V$SESSION A, V$SORT_USAGE B, V$SQL C WHERE A.SADDR = B.SESSION_ADDR   AND A.SQL_ADDRESS = C.ADDRESS(+)   AND status = 'ACTIVE' ORDER BY B.BLOCKS DESC;

(4) 数据库中消耗资源比较大的SQL，

SELECT se.username,       se.sid,       su.extents,       su.blocks * TO_NUMBER (RTRIM (p.VALUE)) AS Space,       tablespace,       segtype,       sql_text  FROM v$sort_usage su,       v$parameter p,       v$session se,       v$sql s WHERE p.name = 'db_block_size'   and su.session_addr = se.saddr   and s.hash_value = su.sqlhash   and s.address = su.sqladdr order by se.username, se.sid;