临时表的相关运维

概念：

1） **临时段：**在 DM 数据库中，所有的临时段都创建在临时表空间中，这样可以分流磁盘设备的 I/O， 也可以减少由于在 SYSTEM 或其他表空间内频繁创建临时数据段而造成的碎片。
临时段的分配和释放完全由系统自动控制，用户不能手工进行干预。

2） **TEMP 表空间：**临时数据文件，完全由 DM 数据库自动维护。当用户的 SQL 语句需要磁盘空间来完成某个操作时，在执行 SQL过程中DM 数据库会从TEMP表空间分配临时段。当临时段被分配或被释放时，临时表空间文件在磁盘所占大小并不会因此而缩减，用户可以通过系统函数 SF_RESET_TEMP_TS 来进行磁盘空间的清理。

用途：如创建索引、无法在内存中完成的排序操作、SQL 语句中间结果集以及用户创建的临时表等都会使用到 TEMP 表空间。
注：NORMAL、KEEP、FAST 和 RECYCLE四种类型数据缓冲区，RECYCLE 缓冲区仅供临时表空间使用；

3） **临时表：**用来临时保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。仅为全局临时表，不支持压缩，不允许进行备份，不支持USING LONG ROW（超长记录存储）选项，数据独享，默认事务级。在临时表上增删索引会使当前会话上临时 b 树数据丢失，不支持重建索引，不支持使用虚拟列，临时表并不太适合数据量大且频繁查询的情况，不支持使用自增列。

① ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后， 表中所有数据都被删除；
② ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话级临时表数据仅能当前会话查看，会话结束时清空表， 并释放临时 B 树。

4） 临时表相关设置

实操模拟：

1、事务级（默认）

----创建事务级表结构并插入数据

----未提交状态下查询数据

----提交状态下查询数据

----sql如下

create global temporary table temp_table01 (id int,name varchar(20)) on commit delete rows;
insert into temp_table01 values (4,'svz');
insert into temp_table01 values (6,'xvdv');
select * from temp_table01;
commit;
select * from temp_table01;

2、会话级

----创建会话级表结构并插入数据

----未提交状态下查询数据

----提交状态下查询数据

----另开窗口查询数据

----sql如下

create global temporary table temp_table02 (id int,name varchar(20)) on commit preserve rows;
insert into temp_table02 values (4,'fgs');
insert into temp_table02 values (6,'gfncg');
select * from temp_table02;
commit;
select * from temp_table02;

3、模拟在线缩小临时表空间

----查看表空间大小，单位为M

select tablespace_name,FILE_NAME,bytes/1024/1024 from dba_data_files;

----新建事务表table03,向事务表中插入1千万行数据，把临时表空间撑大

create global temporary table temp_table03 (id int,name varchar(20)) on commit delete rows;
begin
 for i in 1..10000000
 loop
 insert into temp_table03 values(i,'abcdefg');
 end loop;
end;
select count(*) from temp_table03;

----查看临时表空间大小

select tablespace_name,FILE_NAME,bytes/1024/1024 from dba_data_files;

----通过函数SP_TRUNC_TS_FILE，在线缩小临时表空间文件大小
①函数说明见下图

②获取ts_id,file_id

select * from  v$datafile;

③清空表数据，在线缩小temp表空间

commit;
select count(*) from temp_table03;
SP_TRUNC_TS_FILE(3,0,64);
select tablespace_name,FILE_NAME,bytes/1024/1024 from dba_data_files;

tip:若无法缩小临时表空间尝试重启库释放数据后再次进行缩小；