Oracle 11G Rac集群诡异的表空间不足
一、起因
有一套Oracle 11GR2数据库集群，表空间大概是400G，每次都是表空间使用到47%左右报错，
ORA-12801: error signaled in parallel query server P003, instance : (1)
ORA-01658: unable to create INITIAL extent for segment in tablespace XXXX,
非常奇怪，首先是权限肯定没有问题，
alter user userName quota unlimited on tablespace1 ;
grant dba to userName ;
只能是扩容解决，但这样非常浪费空间，而且没法触发报警，已知开发那边用的是sqlloader工具，还有什么需要关注的吗？
报警信息：
ORA-1653: unable to extend table ****** by 8192 in tablespace ******
ORA-1691: unable to extend lobsegment ****** by 1024 in tablespace ******
ORA-1653: unable to extend table ****** by 128 in tablespace ******
可以看到的是，日志表、lob表、临时表都有涉及其中
二、判断
经总结可能性有以下几种
1、无效索引占用空间，导致空间浪费，建议删除重建失效索引
2、用户权限被收回
3、分区表，建议设置隐藏参数 _partition_large_extents参数为false.
4、检查v$asm_disk视图，单个磁盘free_mb过小
5、回收站空间占用的空间清理不及时
6、表空间碎片严重，可以使用shrnk、move、数据泵等方式清理，建议表空间使用统一的extents
三、验证
1、无效索引
检查无效索引
SELECT owner,
index_name,
table_name,
status
FROM dba_indexes
WHERE owner='用户名' and
status = 'UNUSABLE';
重建无效索引
alter index student_n1 rebuild online parallel 8;
但我的问题表空间没有无效索引的存在
2、用户权限
查看使用这个表空间的用户
select username,account_status,default_tablespace,temporary_tablespace from dba_users where default_tablespace ='表空间';
查看这个用户权限
select * from dba_sys_privs where GRANTEE ='用户名'; GRANTEE PRIVILEGE ADMIN_OPTION
用户名 UNLIMITED TABLESPACE NO
select * from dba_role_privs where GRANTEE ='用户名'; GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
1 用户名 DBA NO YES
2 用户名 CONNECT NO YES
3 用户名 RESOURCE NO YES
用户权限正常
3、分区表
查询用户是否有分区表
select table_owner,table_name,partition_name from dba_tab_partitions where table_owner='用户名';
Oracle 11g有个新特性，在oracle11.2创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而设置的一个参数,默认为true，即分区表的每个extent为8M，和oracle10g相比，会导致同样的数据耗费更多的表空间。
alter system set "_partition_large_extents"=false scope=both sid='*';
如果有的话，可以这样关闭这个参数分配
4、磁盘BUG
检查下v$asm_disk视图，特定情况下会出现单个磁盘free_mb过小的情况，asm没有触发rebalance特性，手动reblance下问题解决
SQL> select OS_MB/1024,TOTAL_MB/1024,FREE_MB/1024,NAME from v$asm_disk where name like 'DATA%';
OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024 NAME
---------- ------------- ------------ ------------------------------
1800 1800 781.585938 DATA_0002
100 100 43.4150391 DATA_0000
100 100 43.4169922 DATA_0001
select name,state,type,free_mb/1024,total_mb/1024,usable_file_mb/1024 from v$asm_diskgroup;
NAME STATE TYPE FREE_MB/1024 TOTAL_MB/1024 USABLE_FILE_MB/1024
------------------------------ ----------- ------ ------------ ------------- -------------------
DATA CONNECTED EXTERN 837.416016 2000 837.416016
FRA CONNECTED EXTERN 162.623047 200 162.623047
OCR MOUNTED NORMAL 14.0957031 15 4.54785156
REDO CONNECTED EXTERN 14.2939453 20 14.2939453
也可以将过小的磁盘剔除
5、回收站垃圾
5.1查看回收站是否开启
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------ ----------- ------------------------------
recyclebin string OFF
5.2清理回收站
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
6、检查涉及表的自动扩展大小
SELECT u.table_name, DBMS_METADATA.GET_DDL('TABLE', u.table_name) table_ddl
FROM user_tables u
WHERE table_name in ('表名')
其中颜色较重的字体的的意思每次申请空间为1M（单位是 bytes）
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "表空间" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "表空间"
也没有类似于分区表很夸张的地步
7、表碎片
检查问题表空间的 FSFI 的值是否小于30%，如果小于30%就需要收集表空间
SELECT a.tablespace_name,
round(sqrt(MAX(a.blocks) / SUM(a.blocks)) * (100 / sqrt(sqrt(COUNT(a.blocks)))),2) "FSFI(碎片率)"
FROM dba_free_space a,
dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
AND b.contents NOT IN ('TEMPORARY',
'UNDO')
GROUP BY a.tablespace_name
ORDER BY 2;
检查问题表空间里的表的碎片率，可以稍微调整下，这条语句总的来说就是要找使用空间最大里实际大小最小的表，就可以收缩了
SELECT TABLE_NAME,
round((BLOCKS * 8192 / 1024 / 1024),2) "使用大小M",
round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9),2) "实际大小M",
round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) / (BLOCKS * 8192 / 1024 / 1024),3) * 100 || '%' "实际使用率%"
FROM USER_TABLES
where blocks > 100
and tablespace_name='表空间'
and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
(BLOCKS * 8192 / 1024 / 1024) '用户名',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 8, granularity => 'ALL',cascade => TRUE);
1、shrnk的优缺点
操作方式：
alter table AA enable row movement;
alter table AA shrink space cascade;
alter table AA disable row movement;
优点：简单方便不断连接，适合行数较少的表，超过一亿数据的表建议不要用这个办法了（曾经有个三亿四千万行的表超过12个小时没有执行完）
缺点：本质是通过增删（insert、delete）方式填充，所以耗费时间较长，归档容易暴增，也会报错 ORA-30036 undo表空间不足，可能会产生行锁
2、expdp的优缺点
优点：简单、快，适合表少、索引小或少但是表数据较多的表或用户（比如说一个表空间有50G到100G左右，快速导出、删除用户、外加导入不超过一个小时）
缺点：中断对外提供使用
3、move的优缺点
没用过，听说是需要迁移来迁移去，先将表迁移到新表空间，再迁移回去，然后再创建索引
四、结果
就是表碎片的原因，但是可以将上述检查进行难易度排序
从简单到难，应该如下
1、用户权限
2、无效索引
3、分区表
4、回收站垃圾
5、检查涉及表的自动扩展大小
6、磁盘BUG
7、表碎片
五、原理
Oracle 表碎片和高水位线是与表空间相关的两个概念，它们之间有一些关系和区别。
表碎片（Table Fragmentation）：
表碎片指的是表在物理存储中的数据分布不连续或不均匀的情况。
当表进行大量的数据删除、更新或插入操作时，可能会导致表的空间使用不连续，即表的数据行被散落在表空间的不同区域。
这种不连续的数据分布会增加查询的成本，因为数据库需要在不同的磁盘区域进行IO操作以获取所有的数据行。
高水位线（High Water Mark）：
高水位线是表空间中最后一个有效数据块的逻辑指针。
在表中插入新数据时，高水位线将向上移动，表示可用空间已经被占用。
当数据被删除时，高水位线不会自动下降，而是保持在最高使用过的位置，这样确保了新插入数据的高效存储。
关系和区别：
表碎片和高水位线都与表的物理存储和空间使用有关。
表碎片指的是表数据在物理存储中的不连续或不均匀分布，而高水位线则是表空间中最后一个有效数据块的位置。
表碎片可能会导致查询的性能下降，而高水位线则表示可用空间被占用，新数据的插入可能需要在表空间中寻找更多的可用空间。
解决表碎片可以通过重新组织表或者进行表分区等方法来优化存储和查询性能，而高水位线的管理通常由Oracle数据库自动处理。
总结：表碎片是指表数据在物理存储中的不连续或不均匀分布，而高水位线是表空间中最后一个有效数据块的位置。表碎片可能导致查询性能下降，而高水位线表示可用空间被占用，新数据的插入可能需要在表空间中寻找更多的可用空间。
六、感谢
感谢鹏哥的帮助，杨卓杨老师的回答，还有 Oracle数据库存储管理与性能优化 这本书，真的是浅显易懂，帮了很大的忙
