環境：oracle10.2.0.1 64bit
問題描述：
測驗環境中有一個表空間名為ANNEX，對應的檔案大小為331.8GB,由于該空間中的資料已經匯出，其中的表也讓開發人員洗掉了。
通過下面的sql陳述句顯示表空間使用率<1%
SELECT a.tablespace_name "表空間名稱", total / (1024 * 1024) "表空間大小(M)", free / (1024 * 1024) "表空間剩余大小(M)", (total - free) / (1024 * 1024 ) "表空間使用大小(M)", total / (1024 * 1024 * 1024) "表空間大小(G)", free / (1024 * 1024 * 1024) "表空間剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name
現在需要將此表空間容量釋放出來，因此，執行如下命令：
ALTER DATABASE DATAFILE '/oradata/hbmisdb/ANNEX.dbf' RESIZE 247921216K
結果提示：Failed to commit: ORA-03297: file contains used data beyond requested RESIZE value
嘗試通過dba_extents和dba_segments查詢此表空間中都有哪些物件，查詢結果如下：
SQL> select segment_name,segment_type,extents,bytes from dba_segments where tablespace_name='ANNEX';
SEGMENT_NAME SEGMENT_TYPE EXTENTS BYTES
------------------------------------- -------------- --------- ----------
SYS_IL0000445585C00006$$ LOBINDEX 17 2097152
SYS_LOB0000445585C00006$$ LOBSEGMENT 210 1751121920
BIN$qRP3thbzAC7gU6wQAQIALg==$0 TABLE 83 100663296
BIN$qRP3thbyAC7gU6wQAQIALg==$0 INDEX 22 7340032
BIN$qRP3thbxAC7gU6wQAQIALg==$0 INDEX 24 9437184
而通過dba_segments查詢的結果則是：
SQL> select segment_name,extent_id,file_id,bytes from dba_extents where tablespace_name='ANNEX';
SEGMENT_NAME EXTENT_ID FILE_ID BYTES
-------------------------------------------------------------------------------- ---------- ---------- ----------
查詢結果為空。
這里想請教的是：
1. 為什么dba_extents查不到內容，而dba_segments能查到一些物件呢？
segment是由多個extent組成的，為什么dba_extents反而查不到任何內容呢？
2. 為什么這個表空間使用率<1%即無法縮小呢？
uj5u.com熱心網友回復：
1. 為什么dba_extents查不到內容，而dba_segments能查到一些物件呢？BIN$ 開頭的段，是已洗掉的表，目前在存盤在回收站，可以用 purge 命令清除它們。
2. 為什么這個表空間使用率<1%即無法縮小呢？
只能收縮到最后一個 extents 所在的位置。
uj5u.com熱心網友回復：
謝謝版主解答，還是有兩點不太明白1. 從dba_segments的查詢結果來看，除了在回收站的索引外，還有lobsegment和lobindex型別的段，但是為什么從dba_extents中查不到任何內容呢？
2. 我想通過dba_extents查詢出最后一個extents的位置，但是dba_extents查詢結果為空，
進一步查詢了已有的段的大小，結果如下：
SQL> select segment_name,segment_type,extents,initial_extent/1024/1024 MB_of_initial_extent,next_extent/1024/1024 MB_of_next_extent,max_extents/1024/1024 MB_of_max_extents,bytes/1024/1024/1024 GB from dba_segments where tablespace_name='ANNEX';
SEGMENT_NAME SEGMENT_TYPE EXTENTS MB_OF_INITIAL_EXTENT MB_OF_NEXT_EXTENT MB_OF_MAX_EXTENTS GB
-------------------------------------------------------------------------------- ------------------ ---------- -------------------- ----------------- ----------------- ----------
SYS_IL0000445585C00006$$ LOBINDEX 17 0.0625 2047.99999713898 0.00195312
SYS_LOB0000445585C00006$$ LOBSEGMENT 210 0.0625 2047.99999713898 1.63085937
BIN$qRP3thbzAC7gU6wQAQIALg==$0 TABLE 83 0.0625 2047.99999713898 0.09375
BIN$qRP3thbyAC7gU6wQAQIALg==$0 INDEX 22 0.0625 2047.99999713898 0.00683593
BIN$qRP3thbxAC7gU6wQAQIALg==$0 INDEX 24 0.0625 2047.99999713898 0.00878906
但是從這個結果看不出最后一個extents的位置，有其他方法嗎？
uj5u.com熱心網友回復：
在回收站里面，清空下回收站，然后再看看uj5u.com熱心網友回復：
1、回收站里有索引，也有表，lob段和索引不可能獨立存在，其屬于某個表，既然把表都洗掉了，那么，這些表及其索引等也就不存在了，所以，dba_extents中查不到；dba_segments里查到的只是回收站里的的物件，查到的空間也是這些物件之前占用的空間，當然，為了保證這些物件可恢復，占用的空間不會被釋放掉；
2、你可以清慷訓收站，當然，如果出于研究目的，也可以恢復這些物件。
uj5u.com熱心網友回復：
查找不同檔案可以進行收縮的空間：col name for a50
col resizecmd for a80
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,ceil(HWM*a.block_size)/1024/1024 ResizeTo,(a.bytes-HWM*a.block_size)/1024/1024 ReleaseMB,
'alter database datafile ''' || a.name || ''' resize '|| ceil(HWM*a.block_size)/1024/1024 || 'M;' ResizeCmd
from v$datafile a,(select file_id,MAX(block_id + blocks - 1) HWM from DBA_EXTENTS group by file_id) b
where a.file#=b.file_id(+) and (a.bytes - HWM*a.block_size) > 0
order by 5;
查找每個資料檔案最末端存盤的物件，通過移動這些物件，達到可以resize資料檔案的目的。
col segmnet_name for a30
col semgent_type for a20
select /*+ rule */ owner,segment_name,segment_type from dba_extents
where (file_id,block_id) in
(select file_id,max(block_id) from dba_extents group by file_id);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8463.html
標籤：基礎和管理
下一篇：求助：org.hibernate.HibernateException: Dialect class not found