temp表空间与tempfile

SQL> alter  tablespace  temp read only;
alter  tablespace  temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

SQL> alter  tablespace  temp offline;
alter  tablespace  temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

SQL> drop tablespace temp ;                                             
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

临时表空间不能read only、offline、drop，从临时表空间的功能来看（主要用来排序），如果对临时表空的上述操作能成功，那临时表空间就毫无意义了

但是临时表空间对应的文件可以offline、drop

 

临时表空间的拓展：
来自：http://www.itpub.net/forum.php?mod=viewthread&tid=1481844 

相关视图：
v$dba_temp_files
v$sort_segment
v$tempseg_usage
dba_temp_free_space

上面这几个视图是我总结的在遇到Oracle临时空间问题时能用到的数据字典信息。
先看v$dba_temp_files
这个视图显示了数据库中临时文件的具体信息。要注意的有4列

SQL> desc dba_temp_files;
Name            Type          Nullable Default Comments                                            
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME       VARCHAR2(513) Y                Name of the database temp file                     
FILE_ID         NUMBER        Y                ID of the database temp file                        
TABLESPACE_NAME VARCHAR2(30)                   Name of the tablespace to which the file belongs   
BYTES           NUMBER        Y                Size of the file in bytes                           
BLOCKS          NUMBER        Y                Size of the file in ORACLE blocks                   
STATUS          VARCHAR2(7)   Y                File status: "AVAILABLE"                           
RELATIVE_FNO    NUMBER        Y                Tablespace-relative file number                     
AUTOEXTENSIBLE  VARCHAR2(3)   Y                Autoextensible indicator:  "YES" or "NO"            
MAXBYTES        NUMBER        Y                Maximum size of the file in bytes                  
MAXBLOCKS       NUMBER        Y                Maximum size of the file in ORACLE blocks           
INCREMENT_BY    NUMBER        Y                Default increment for autoextension                 
USER_BYTES      NUMBER        Y                Size of the useful portion of file in bytes         
USER_BLOCKS     NUMBER        Y                Size of the useful portion of file in ORACLE blocks

这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，在我的db中，这一部分大小是128个block，如下图所示，22784-22656=128.
SQL> select file_name, blocks , user_blocks from dba_temp_files;

FILE_NAME                                                                            BLOCKS          USER_BLOCKS
-------------------------------------------------------------------------------- -------------------- -----------
/u01/app/oracle/oradata/sales/temp01.dbf                                    22784                22656
/u01/app/oracle/oradata/sales/temp02.dbf                                    6400                  6272


再看v$sort_segment视图
这个视图从字面翻译虽然叫做排序段，但实际上不仅仅是排序，只要是消耗了临时表空间的操作，比如创建临时表等，都会在这个视图中显示。但如果一个操作没有占用临时表空间（比如开销很小的排序，在内存中完成，或者一个临时表在创建时通过tablespace子句指定到了一个永久表空间中),那么这个视图就不会被更新。
但有一个问题是，我们指定正常的segment是一个segment用来承载一个对象如表或者index，再或者一个对象的分区，如分区表的分区，但我们的sort_segment似乎不是这样的，一个sort_segment可以供多个对象或者说操作使用。
如图，一个sort_segment, 有两个extent被占用，同时有两个user在使用。因为这个测试是我自己进行的，所以我清楚的知道，两个user 一个在创建临时表，另一个在创建一个大表的索引。这根本就是两件不相干的事，但用的是同一个sort_segment.
不过这个问题不重要，因为我们通过这个视图主要知道多少个用户在使用临时表空间，已经使用了多少这就够了。
SQL> select tablespace_name ,TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ，CURRENT_USERS from v$sort_segment;

TABLESPACE_NAME    TOTAL_BLOCKS    USED_EXTENTS    USED_BLOCKS  CURRENT_USERS
-------------------------------  -----------------------      ----------------------     -----------                 ---------------------
TEMP                             22656                         2                               256                        2


再看v$tempseg_usage
这个视图可以详细的显示那些用户在使用临时空间，使用了多少，其session的id，SQL的ID等。但有一个很困惑的问题是其中的SQL_ID列显示的信息不准确。根据我的试验，它显示的应该是目标会话的当前SQL的SQL_ID，而非消耗临时表空间的SQL_ID。比如说你在一个会话中发出了一条SQL消耗了很大的临时空间，然后你又发出了一条不相干的SQL，这时如果我select * from v￥tempseg_usage，查到的SQL_ID对应的SQL是第二条，也就是最新的那条。
另外一条要注意的是，这个视图与 v$sort_usage是一样的，只不过后者改了个名字而已。大概oracle是这么考虑的，如果叫sort_usage会引起歧义，会让人以为这个视图针对的是排序的开销，但实际上不仅仅是排序，只要是对临时表空间的消耗都会体现在这个视图中。