| ORACLE、temp 、临时表空间

ORACLE临时表空间

临时表空间概念

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。

创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。

另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace

临时表空间脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
##查看实例的临时表空间
SELECT PROPERTY_NAME, PROPERTY_VALUE  FROM DATABASE_PROPERTIES  WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
##创建temp表空间
create temporary tablespace temp01 tempfile 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP02.DBF' size 1g autoextend off ;
alter tablespace temp01 add tempfile  'D:\APP\NIUEY\ORADATA\NIUEY\TEMP03.DBF' size 1g AUTOEXTEND OFF ;
##删除表空间文件
ALTER TABLESPACE TEMP01  DROP TEMPFILE 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP03.DBF';
ALTER DATABASE TEMPFILE 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP03.DBF'  DROP INCLUDING DATAFILES;  
##调整表空间数据文件大小
alter database tempfile 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP02.DBF' resize 2G 
##临时表空间脱机/联机
 ALTER DATABASE TEMPFILE   'D:\APP\NIUEY\ORADATA\NIUEY\TEMP02.DBF' OFFLINE;
 ALTER DATABASE TEMPFILE   'D:\APP\NIUEY\ORADATA\NIUEY\TEMP01.DBF' online;
##表空间 自动扩展
ALTER DATABASE TEMPFILE 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP02.DBF' AUTOEXTEND ON NEXT 100M  MAXSIZE UNLIMITED;
##切换数据库默认表空间
alter database default temporary tablespace TEMP01;
##修改表空间数据文件
ALTER DATABASE TEMPFILE  'D:\APP\NIUEY\ORADATA\NIUEY\TEMP01.DBF' offline;
ren   D:\APP\NIUEY\ORADATA\NIUEY\TEMP01.DBF  TEMP.DBF
ALTER DATABASE rename file   'D:\APP\NIUEY\ORADATA\NIUEY\TEMP01.DBF' to 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP.DBF';
ALTER DATABASE TEMPFILE  'D:\APP\NIUEY\ORADATA\NIUEY\TEMP.DBF' online;
##查看临时表空间使用
select * from (
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 = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10

数据文件操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
##开启日志
alter database add  supplemental log data ;
alter database drop  supplemental log data ;
alter database add supplemental log data (primary key,unique,foreign key,all) columns;
alter database drop supplemental log data (primary key,unique,foreign key,all) columns;
##临时表空间
select * from dba_temp_files ;
alter tablespace temp add tempfile 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP2.DBF' size 1G ;
alter tablespace temp drop tempfile 'D:\APP\NIUEY\ORADATA\NIUEY\TEMP2.DBF';
##offline online
alter   database tempfile  3  offline   ;
alter   database tempfile  3  online   ;
##数据表空间
select * from dba_data_files;
alter tablespace users add datafile 'D:\APP\NIUEY\ORADATA\NIUEY\USERS02.DBF' size 1g;
alter tablespace users drop datafile 10 ;
##offline online
alter   database datafile  10  offline drop  ;
alter database  recover datafile 10 ;
alter   database datafile  10  online ;
ESC