Oracle临时表空间语句、作用
在Oracle数据库中；一个表空间只属于一个数据库使用；而一个数据库可以拥有多个表空间。属于"一对多"的关系
我们对照着mysql来理解.
1、Oracle有表空间，mysql没有表空间
2、Oracle提供的Sql Plus登录
3、MySQL在一个实例中可以创建多个数据库，但Oracle在一个实例中就只有一个数据库，但是一个oracle一个数据库对应多个实例，用实例sid来区分。
请看我的一篇 https://www.cnblogs.com/aozhejin/p/15859979.html
4、mysql是先建实例，后建库，oracle是先建库，后建实例(看看下面的模型)
mysql--->实例1---》a库---》a1表
a1表
b库--->b1表
b2表
oracle---->数据库a--->a实例--->a表空间----》a1表
a2表
b表空间---->b1表
b2表
b实例--->c表空间----》c1表
c2表
d表空间---->d1表
d2表
下面主要的几个表空间.
1)system表空间 每个Oracle数据库都会有SYSTEM表空间，而且SYSTEM表空间总是要保持在联机模式下，因为其包含了数据库运行所要求的基本信息，
如：数据字典、联机求助机制、所有回退段、临时段、所有的用户数据库实体、其它ORACLE软件产品要求的表等等。 2)SYSAUX表空间 作为SYSTEM表空间的辅助表空间，主要存放一些其他的 metadata 组件，如 OEM,Streams 等会默认存放在 SYSAUX 表空间里。 3)临时表空间 临时表空间是用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，
并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作：
CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN
等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。 4)UNDO表空间 undo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。在rollback，实例恢复(前滚)，
一致性读CR块的构造时会使用到undo信息。由于undo的引入，从而Oracle的select语句实现一致性读时，不需要任何锁。 5)USERS表空间 创建用户必须为其指定表空间，如果没有显性指定默认表空间，则指定为users表空间，此用户所有信息都会放入到users表空间中。 6)业务表空间（自建）
小结： 我们可以认为主要就是四个部分组成 一种是系统表空间、临时表空间、日志表空间、业务表空间，这个应该没有太多疑问。
由于临时表空间是内存中的，所以我们通过pl/sql在查询时候也看不到具体的表。
一、临时表空间作用一：
1、oracle临时表空间主要用来做查询和存放一些缓冲区数据。 2、临时表空间消耗的主要原因是需要对查询的中间结果进行排序。 3、重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
临时表空间对应的文件就是临时文件temp file，在Oracle中可以使用dba_temp_files视图进行查询。
SQL> col file_name for a50;
SQL> select file_name, bytes/1024/1024, AUTOEXTENSIBLE from dba_temp_files;
FILE_NAME BYTES/1024/1024 AUTOEXTEN
--------------- ---------
/u01/oracle/oradata/prd/prd_temp01.dbf 10337 YES
/u01/oracle/oradata/fp/fp_temp01.dbf 50 YES
//这里两个库，分别指定了表空间
通常，在系统创建的时候，Oracle会创建临时表空间Temp作为系统默认临时表空间Temporary Tablespace。而且，通常Temp表空间都是设置为支持自动拓展的。
二、 临时表空间作用二：
索引create或rebuild； Order by 或 group by； Distinct 操作； Union 或 intersect 或 minus； Sort-merge joins； analyze。
表空间从类型上有若干分类的方式，一种是按照“文件file”的大小，区别为small file tablespace和big file tablespace。另一种是按照表空间用途而定的。
此种分类方法可以将表空间划分为持久化表空间（Permanent Tablespace）、Undo表空间和临时表空间（Temporary Tablespace）。
Temporary临时表空间是oracle一种内部空间调控的产物。根据Oracle官方文档中的介绍，Temporary表空间主要是针对session会话自身的操作使用的。
当一个会话通过SQL或者PL/SQL将数据集合获取，进行大面积的sort或者group by操作时，会话会严重的消耗PGA资源。PGA是针对会话自身特有信息的一块内存区域，
用于保存会话自身特有、无法与其他会话共享的信息。如果数据集很大，这样对内存PGA的资源消耗就会很大。Oracle在这个时候，就会采用类似操作系统虚拟内存技术的方法，
从硬盘上借一块空间给PGA置换使用，缓解PGA的不足。
在使用Temporary表空间的时候，是系统自动完成的临时段segment对象创建和销毁。这个过程对用户会话而言是透明的。
三、关于临时表空间的语句：
1、查询TEMP表空间使用情况
SQL> set pagesize 0
SQL> set line 9999
SQL> SELECT temp_used.tablespace_name,total - used AS "Free",
total AS "Total",ROUND (NVL (total - used, 0) * 100 / total, 3) "Free percent"
FROM ( SELECT tablespace_name, SUM (bytes_used) / 1024 / 1024 used
FROM GV$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total FROM dba_temp_files
GROUP BY tablespace_name) temp_total 11 WHERE temp_used.tablespace_name = temp_total.tablespace_name;
TEMP 0 10337 0
FP_TEMP 49 50 98
2、更改系统的默认临时表空间
--查询默认临时表空间
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
--修改默认临时表空间
alter database default temporary tablespace temp1;
3、改变临时表空间大小 alter database tempfile /u01/oracle/oradata/fp/fp_temp01.dbf' resize 100M;
或
alter database tempfile 4 autoextend on next 100M maxsize 1000M;
4、更改某一用户的临时表空间：
alter user scott temporary tablespace temp3;
3、扩展临时表空间： 方法一、增大临时文件大小： SQL> alter database tempfile '/u01/oracle/oradata/fp/fp_temp01.dbf' resize 100m; 方法二、将临时数据文件设为自动扩展： SQL> alter database tempfile '/u01/oracle/oradata/fp/fp_temp01.dbf’ autoextend on next 5m maxsize unlimited; 方法三、向临时表空间中添加数据文件： SQL> alter tablespace temp add tempfile '/u01/oracle/oradata/fp/fp_temp01.dbf’ size 100m; 5、创建临时表空间： create temporary tablespace temp01 tempfile '/u01/oracle/oradata/fp/fp_temp01.dbf' size 10M; 6、更改系统的默认临时表空间： 查询默认临时表空间 select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'; 修改默认临时表空间 alter database default temporary tablespace temp02; select username,temporary_tablespace,default_tablespace from dba_users;
更改某一用户的临时表空间： alter user scott temporary tablespace temp02; 7、删除临时表空间 删除临时表空间的一个数据文件： alter database tempfile 'C:ORADATAORCLTEMP03.DBF' drop; 删除临时表空间(彻底删除)： drop tablespace temp including contents and datafiles cascade constraints; 8、查看临时表空间的使用情况（GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询） GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小 dba_temp_files视图的bytes字段记录的是临时表空间的总大小 SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent" FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total WHERE temp_used.tablespace_name = temp_total.tablespace_name 89、查找消耗资源比较的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 10、查看当前临时表空间使用大小与正在占用临时表空间的sql语句 select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text from v$sort_usage sort, v$session sess, v$sql sql where sort.SESSION_ADDR = sess.SADDR and sql.ADDRESS = sess.SQL_ADDRESS order by blocks desc; 11、临时表空间组介绍 1）创建临时表空间组： create temporary tablespace tempts1 tempfile '/u01/oracle/oradata/fp/fp_temp01.dbf' size 2M tablespace group group1; create temporary tablespace tempts2 tempfile '/u01/oracle/oradata/fp/fp_temp01.dbf' size 2M tablespace group group2; 2）查询临时表空间组：dba_tablespace_groups视图 select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP1 TEMPTS1 GROUP2 TEMPTS2 3）将表空间从一个临时表空间组移动到另外一个临时表空间组： alter tablespace tempts1 tablespace group GROUP2 ; select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP2 TEMPTS1 GROUP2 TEMPTS2 4）把临时表空间组指定给用户 alter user scott temporary tablespace GROUP2; 5）在数据库级设置临时表空间 alter database default temporary tablespace GROUP2; alter database orcl default temporary tablespace GROUP2; 6）删除临时表空间组 (删除组成临时表空间组的所有临时表空间) drop tablespace tempts1 including contents and datafiles; select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ GROUP2 TEMPTS2 drop tablespace tempts2 including contents and datafiles; select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME 12、对临时表空间进行shrink（11g新增的功能） 将temp表空间收缩为20M alter tablespace temp02 shrink space keep 20M; 自动将表空间的临时文件缩小到最小可能的大小 ALTER TABLESPACE temp SHRINK TEMPFILE ’/u01/oracle/oradata/fp/fp_temp01.dbf’;
