排错：insert失败没有数据但空间持续消耗_表空间管理

1、背景

近期排查了一个数据库job执行异常，表现为：

1. insert操作失败，目标表里没有数据（与一般经验一致）；
2. 但目标表占用的空间持续增长，且不正常的增速（没数据，空间还快速消耗，与一般经验不符）。

通过排查原因是：

1. 数据处理程序中，数据插入目标表后，在最后阶段清空临时表；
2. 某次执行，因表空间不足，插入目标表操作失败，程序退出，后续清空临时表不执行；
   3 .临时表沉淀了多批次的数据，关联产生笛卡尔积，导致后续插入目标表的数据量越来越大。
3. 表空间消耗越来越大，很快因空间不足再次失败。因事务一致性，其他会话确实查不到数据。但空间已分配，且不因事务失败而回退。

2、验证

1.insert失败，表空间是否被占用？
2.insert失败，已分配的空间能否再次使用？
3.如何回收insert失败已分配的空间？

2.1 新建表空间，大小=100m;

create tablespace ts_test0827 
       datafile '/u01/app/oracle/oradata11g/test2/ts_test0828.dbf' size 100m;

2.2 新建表，看segment大小

create table hello1 (
  BEGIN_ID	 	VARCHAR2(100)	,		
  END_ID	 	VARCHAR2(100)	,			
  DATETIME	 	DATE	,			
  SYN_TYPE	 	VARCHAR2(50)	
)
tablespace ts_test0827;	

2.3 查看表的segment占用空间

select t.segment_name,
       t.segment_type,
       t.BYTES / 1024 / 1024 size_m,
       t.blocks
  from user_segments t
 where t.segment_name = 'HELLO1';

1.无数据：表刚创建后，是无segment对象；

2.4 插入1条记录，再看segment占用空间

插入1条记录后，大小0.0625M；8个blocks;

2.5 插入3GB的数据量

不出意外的insert因空间不足失败。
查看segment占用的空间：
直接插入3G的数据，大小为96M，12288个blocks；实际还是只1条记录；

  表空间名	表空间大小(m)	已使用空间(m)	使用比	空闲空间(m)	最大块(m)
  TS_TEST0827	100	97	97	3	3

证明：在提交前，数据已从内存到文件，占用了表空间。

2.6 表内的空间还能否使用

插入2万条记录，2万条记录的空间>3M的，确保真的使用了之前占用的空间。

insert into hello1
  select * from aaa_monitor 
  where rownum<20000;

2.7 回收空间

1)收集统计信息

  begin
	dbms_stats.gather_table_stats(ownname => user, tabname => 'HELLO1');
  end;

收集统计信息，空间没有回收；

SEGMENT_NAME	SEGMENT_TYPE	SIZE_M	BLOCKS
HELLO1	TABLE	96	12288

2)shrink

  alter table hello1 enable row movement;
  alter table hello1 shrink space cascade;
  SEGMENT_NAME	SEGMENT_TYPE	SIZE_M	BLOCKS
  HELLO1	TABLE	2.1875	280

3.总结：

1.程序设计

清空临时表，最好放在程序开始，防止后期被其他异常干扰导致失败；或者模块化，在头尾都清理2次。

2.Oracle空间分配：

1. 表刚创建，只有逻辑对象，没有物理的segment；只有insert第一条记录后，才生成segment；分配extends，连续的8个block。
2. insert操作：根据数据库原理，事务即使没有提交，因为data buffer cache管理机制，也有可能被写入到dbfile，分配空间，提高hwm。
3. 失败事务被分配的空间，是可以被其他事务使用的。
4. 统计信息不能回收被分配的数据；shrink可以，shrink是重新排布了行，降低了hwm。
5. 回收空间的意义是从一个表收回到表空间，可以被其他表使用。