DM临时表基础运维

本章介绍达梦数据库临时表基础运维。

本章内容已在如下环境上测试：

①操作系统：CentOS 7.5；

②数据库版本：DM8；

一、概念 

1、 临时段：

在 DM 数据库中，所有的临时段都创建在临时表空间中，这样可以分流磁盘设备的 I/O，也可以减少由于在 SYSTEM 或其他表空间内频繁创建临时数据段而造成的碎片。

临时段的分配和释放完全由系统自动控制，用户不能手工进行干预。

2、TEMP 表空间：

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

用途：

如创建索引、无法在内存中完成的排序操作、SQL 语句中间结果集以及用户创建的临时表等都会使用到 TEMP 表空间。

Tips:：

NORMAL、KEEP、FAST 和 RECYCLE四种类型数据缓冲区，RECYCLE 缓冲区仅供临时表空间使用；

3、临时表：

用来临时保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。仅为全局临时表，不支持压缩，不允许进行备份，不支持USING LONG ROW（超长记录存储）选项，数据独享，默认事务级。

在临时表上增删索引会使当前会话上临时 b 树数据丢失，不支持重建索引，不支持使用虚拟列，临时表并不太适合数据量大且频繁查询的情况，不支持使用自增列。

Tips:

① ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后， 表中所有数据都被删除；

② ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话级临时表数据仅能当前会话查看，会话结束时清空表， 并释放临时 B 树。

4、 临时表相关设置

USE_FTTS	0	动态，会话级	执行过程中产生的临时数据的存放格式。0：用临时表空间的数据页存放；1：用临时文件存放
TEMP_SIZE（重启）	10	静态	默认创建的临时表空间大小，以M为单位。有效值范围（10~1048576）
TEMP_SPACE_LIMIT	0	动态，系统级	临时表空间大小上限，以M为单位。0表示不限制临时表空间大小。有效范围（0~4294967294）。注：TEMP_SPACE_LIMIT一定要大于等于TEMP_SIZE，防止出现临时表空间暴涨导致服务器空间不足数据库宕机。
TEMP_PATH（手动修改重启）		手动	临时表空间路径，默认在实例路径DAMENG下，dm.ini中设置
ENABLE_TMP_TAB_ROLLBACK	1	动态，系统级	临时表操作是否生成回滚记录，0：不生成；1：生成注：置为0时，临时表的DML操作无法回滚

 二、实操模拟  

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、模拟在线缩小临时表空间

     ----查看表空间大小，单位为M3、模拟在线缩小临时表空间；

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，在线缩小临时表空间文件大小 ：

①系统过程说明见下

SP_TRUNC_TS_FILE

定义：

SP_TRUNC_TS_FILE (

ts_id int,

file_id int,

to_size int

)

功能说明：

将临时表空间文件截断到指定的大小，仅能截断文件空闲未使用的部分，如果在指定to_size后存在已使用数据页，则实际截断大小会大于to_size

参数说明：

ts_id：指定截断文件的临时表空间ID

file_id：指定截断文件的文件ID

to_size：指定将文件截断至多大，以M为单位；to_size大小换算成页数后，值必须在4096到2G之间

返回值：

无

举例说明：

将临时表空间文件号为0的文件截断到32M大小，库的页大小为8K

CALL SP_TRUNC_TS_FILE (3, 0, 32);

 ②获取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;

Tips:若无法缩小临时表空间可重启数据库，重启数据库后临时表空间会重建为初始大小，即TEMP_SIZE参数指定的大小。