一、起因
有一套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 NOselect * 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_0001select 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 OFF5.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;檢查數據文件是否還有剩余大于1M的區，主要看 bytes 這列
select file_id,bytes/1024/1024,count(1) from dba_free_space where tablespace_name='CWDATA1' group by file_id,bytes/1024/1024 order by 3 asc;檢查問題表空間里的表的碎片率，可以稍微調整下，這條語句總的來說就是要找使用空間最大里實際大小最小的表，就可以收縮了
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) < 0.3
order by 2 desc;多嘴說兩句
BLOCKS The number of used blocks in the table 表中已使用的塊的數量
NUM_ROWS The number of rows in the table 表中的行數
AVG_ROW_LEN The average row length, including row overhead 平均行長度，包括行開銷(BLOCKS * 8192 / 1024 / 1024) = 塊數量 * 塊大小（bytes） / 1024（kb）/ 1024（mb）= 使用大小
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) = 行數 * 平均行長度 / 1024（kb）/ 1024（mb） / 0.9（塊分10層，一層是塊頭，9層是數據）= 實際大小收縮表空間有三種方式 shrnk 和 expdp 和 move
三種方式使用前都最好先收集一下統計信息
exec dbms_stats.gather_schema_stats(ownname => '用戶名',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數據庫存儲管理與性能優化 這本書，真的是淺顯易懂，幫了很大的忙
