temp 权限 temp管理

这篇文章摘自：

tianlesoftware

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。所以Temp表空间可能会越来越大。

排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.

创建临时表空间

SQL> CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '+dgroup1' 2 SIZE 20M REUSE 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; 表空间已创建。 SQL> select t.file_name from dba_temp_files t; FILE_NAME -------------------------------------------------------------------------------- +DGROUP1/oralife/tempfile/temp.265.762803345 +DGROUP1/oralife/tempfile/lmtemp.290.769965543

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The Oracle Database default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the preceding statement.
自动分配不允许应用于临时表空间。

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.
Temp 表空间必须是uniform 的，undo 必须是autoallocate的。默认情况下uniform 是1M。
If you do not specify AUTOALLOCATE or UNIFORM, then the default is UNIFORM for temporary tablespaces and AUTOALLOCATE for all other types of tablespaces.

使用ALTER TABLESPACE添加临时文件,将临时文件offline,online

SQL> ALTER TABLESPACE lmtemp 2 ADD TEMPFILE '+dgroup1' SIZE 18M REUSE; Tablespace altered SQL> select t.file_name from dba_temp_files t; FILE_NAME -------------------------------------------------------------------------------- +DGROUP1/oralife/tempfile/temp.265.762803345 +DGROUP1/oralife/tempfile/lmtemp.290.769965543 +DGROUP1/oralife/tempfile/lmtemp.271.769965767 SQL> ALTER TABLESPACE LMTEMP TEMPFILE ONLINE; Tablespace altered SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2; NAME STATUS -------------------------------------------------------------------------------- ------- +DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE +DGROUP1/oralife/tempfile/lmtemp.290.769965543 ONLINE +DGROUP1/oralife/tempfile/lmtemp.271.769965767 ONLINE SQL> ALTER TABLESPACE LMTEMP TEMPFILE OFFLINE; Tablespace altered SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2; NAME STATUS -------------------------------------------------------------------------------- ------- +DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE +DGROUP1/oralife/tempfile/lmtemp.290.769965543 OFFLINE +DGROUP1/oralife/tempfile/lmtemp.271.769965767 OFFLINE

删除临时表空间并删除对应的OS文件

SQL> drop tablespace temp2 including contents and datafiles; Tablespace dropped

使用ALTER DATABASE更改临时文件
将临时文件offline,online

SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' ONLINE; Database altered SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2; NAME STATUS -------------------------------------------------------------------------------- ------- +DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE +DGROUP1/oralife/tempfile/lmtemp.290.769965543 OFFLINE +DGROUP1/oralife/tempfile/lmtemp.271.769965767 ONLINE SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' OFFLINE; Database altered SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2; NAME STATUS -------------------------------------------------------------------------------- ------- +DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE +DGROUP1/oralife/tempfile/lmtemp.290.769965543 OFFLINE +DGROUP1/oralife/tempfile/lmtemp.271.769965767 OFFLINE

调整表空间大小

SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' RESIZE 18M; Database altered

删除临时文件并删除它的OS文件

SQL> ALTER DATABASE TEMPFILE '+DGROUP1/oralife/tempfile/lmtemp.271.769965767' DROP INCLUDING DATAFILES; Database altered SQL> SELECT t2.NAME FROM v$tempfile t2; NAME -------------------------------------------------------------------------------- +DGROUP1/oralife/tempfile/temp.265.762803345 +DGROUP1/oralife/tempfile/lmtemp.290.769965543

tempfile 数据文件重命名的步骤：

（1）将tempfile offline

（2）在操作系统上重命名tempfile

（3）使用alter database rename file 更新控制文件。

临时表空间满时的处理方法

添加数据文件

如果Temporary tablespace还不大，那么我们可以增加一些数据文件。SQL 语句如下：

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE 'D:/ORADATA/NEWCCS/TEMP02.DBF' SIZE 100M AUTOEXTEND OFF;

一般来说，Temp tablespace 和 Undo Tablespace 是不建议设置为自增长，设置自增长可能会把磁盘给撑满。

修改数据文件大小

可以将原来的数据文件改大一点，如：

SQL>ALTER DATABASE TEMPFILE 'D:/ ORADATA/NEWCCS/TEMP02.DBF' RESIZE 100M;

Temp 表空间过大的处理方法
1.查看目前Temp 表空间的信息

SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DGROUP1/oralife/tempfile/temp.286.769967665 +DGROUP1/oralife/tempfile/temp2.301.769968357 +DGROUP1/oralife/tempfile/temp3.302.769968373 SQL> select username,temporary_tablespace from dba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MGMT_VIEW TEMP SYS TEMP SYSTEM TEMP DBSNMP TEMP SYSMAN TEMP TEST2 TEMP OCP TEMP TEST1 TEMP OUTLN TEMP MDSYS TEMP ORDSYS TEMP CTXSYS TEMP ANONYMOUS TEMP EXFSYS TEMP DMSYS TEMP WMSYS TEMP XDB TEMP ORDPLUGINS TEMP SI_INFORMTN_SCHEMA TEMP OLAPSYS TEMP USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MDDATA TEMP DIP TEMP TSMSYS TEMP 23 rows selected SQL>

关于用户这块是要特别注意的，如果我们将默认的Temp 表空间指向其他的名称，那么这些用户的信息就会失效，所以，我们替换时，要么创建一个临时的Temp 表空间中转一下，这样切换之后，我们的temp表空间名称不变，要么改变名称，同时更新相关用户的default temp 表空间。

2.替换Temp 表空间

这里用中转的方法来测试。

--创建temp2临时表空间 SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 2 '+dgroup1' SIZE 10M; Tablespace created --更改temp2为默认临时表空间 SQL> alter database default temporary tablespace temp2; Database altered --删除原来的临时表空间 SQL> drop tablespace temp including contents and datafiles; Tablespace dropped --重新创建temp临时表空间 SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 2 '+dgroup1' SIZE 10M AUTOEXTEND OFF; Tablespace created SQL> select username,temporary_tablespace from dba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MGMT_VIEW TEMP2 SYS TEMP2 SYSTEM TEMP2 DBSNMP TEMP2 SYSMAN TEMP2 TEST2 TEMP2 OCP TEMP2 TEST1 TEMP2 OUTLN TEMP2 MDSYS TEMP2 ORDSYS TEMP2 CTXSYS TEMP2 ANONYMOUS TEMP2 EXFSYS TEMP2 DMSYS TEMP2 WMSYS TEMP2 XDB TEMP2 ORDPLUGINS TEMP2 SI_INFORMTN_SCHEMA TEMP2 OLAPSYS TEMP2 USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MDDATA TEMP2 DIP TEMP2 TSMSYS TEMP2 23 rows selected --重置缺省临时表空间为新建的temp表空间 SQL> alter database default temporary tablespace temp; Database altered SQL> select username,temporary_tablespace from dba_users; USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MGMT_VIEW TEMP SYS TEMP SYSTEM TEMP DBSNMP TEMP SYSMAN TEMP TEST2 TEMP OCP TEMP TEST1 TEMP OUTLN TEMP MDSYS TEMP ORDSYS TEMP CTXSYS TEMP ANONYMOUS TEMP EXFSYS TEMP DMSYS TEMP WMSYS TEMP XDB TEMP ORDPLUGINS TEMP SI_INFORMTN_SCHEMA TEMP OLAPSYS TEMP USERNAME TEMPORARY_TABLESPACE ------------------------------ ------------------------------ MDDATA TEMP DIP TEMP TSMSYS TEMP 23 rows selected --删除中转临时表空间temp2 SQL> drop tablespace temp2 including contents and datafiles; Tablespace dropped