ArcSDE for Oracle表空间管理——暂时（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

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