ArcSDEforOracle表空间管理临时（TEMP）表空间

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。 重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。 以下操作会占

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

下面就实践一下创建临时表空间组

查明默认的临时表空间信息

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
SDE
ESRI
ESRI2
TEST

已选择10行。

SQL> create temporary tablespace temp2 tempfile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\temp02.dbf' size 10M;

表空间已创建。

SQL> create temporary tablespace temp3 tempfile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\temp03.dbf' size 10M;

表空间已创建。

SQL> create temporary tablespace temp4 tempfile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\temp04.dbf' size 10M;

表空间已创建。

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP03.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP04.DBF

SQL> alter tablespace temp tablespace group temp_group;

表空间已更改。

SQL> alter tablespace temp2 tablespace group temp_group;

表空间已更改。

SQL> alter tablespace temp3 tablespace group temp_group;

表空间已更改。

SQL> alter tablespace temp4 tablespace group temp_group;

表空间已更改。

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_GROUP                     TEMP
TEMP_GROUP                     TEMP2
TEMP_GROUP                     TEMP3
TEMP_GROUP                     TEMP4

SQL> alter database default temporary tablespace temp_group;

数据库已更改。

SQL> select temporary_tablespace from dba_users where username='SDE';

TEMPORARY_TABLESPACE
------------------------------
TEMP_GROUP

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

关于临时表空间的问题：

很多用户会发现在使用临时表空间时，如果操作任务完成之后，系统不会自动清理临时表空间的资源。

可以通过Oracle11g新增的DBA_TEMP_FREE_SPACE视图来查看临时表空间的占用率和空闲率

SQL> select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  32497664         2088960   31457280
TEMP4                                 10485760         2097152    9437184
TEMP3                                 10485760         4194304    7340032
TEMP2                                 10485760         3145728    9437184


SQL> select allocated_space*100/tablespace_size as used from DBA_TEMP_FREE_SPACE;

      USED
----------
6.42803126
        20
        40
        30

如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。

如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。

如果临时表空间的类型为PERMANENT，SMON会在process不再使用临时段之后去做清理。

如果是用PERMANENT tablespace作排序，由smon负责在statement结束之后删除被创建的temporary segments，这样空间可以被其他对象使用。

考虑到性能原因，当一个temporary extent被分配的时候，tablespace会做一个标记，操作结束之后这个extent不会被释放或回收，相应的，这个extent被简单的标志为free，对于后面的sort操作是available的，这样就省去了系统分配和回收temporary extent的负载。建议都是用临时类型。

当然，在Oracle11g版本用户也可以使用ALTER TABLESPACE SHRINK 命令对临时表空间为释放的资源进行Shrink。

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

参考Oracle帮助文档：

Shrinking a Locally Managed Temporary Tablespace

Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse. Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. For this reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.

You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or the SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP clause defines a minimum size for the tablespace or temp file.

Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.

The following example shrinks the locally managed temporary tablespace lmtmp1 while ensuring a minimum size of 20M.

ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;

The following example shrinks the temp file lmtemp02.dbf of the locally managed temporary tablespace lmtmp2. Because the KEEP clause is omitted, the database attempts to shrink the temp file to the minimum possible size.

ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

默认可以不带KEEP参数，如果忽略KEEP 子句，则只要满足其它存储属性，数据库就会尽可能尝试收缩表空间/临时文件（所有当前使用的区的总空间），如果使用KEEP参数建议KEEP大小不能超过所操作临时表空间的最大值。如果该表空间对象参与了临时表空间组也适用于该命令。

注意：临时表空间过大或者过小都会对数据库性能有直接影响，所以建议在Shrink临时表空间都是用KEEP参数。

参考文献：

http://blog.chinaunix.net/uid-21267700-id-3295645.html

http://blog.csdn.net/tianlesoftware/article/details/8225395

----------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------