我们知道,我们如果把一些对象删除之后,如何回收表空间中的数据文件:
我们看如下的查询:
- SELECT UPPER(F.TABLESPACE_NAME)"表空间名",
- D.TOT_GROOTTE_MB "表空间大小(M)",
- D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
- TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') ||'%' "使用比",
- F.TOTAL_BYTES "已扩展空闲空间(M)",
- (SELECT free_space_mb+free_allocate_mbFROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name)"总剩余空间",
- F.MAX_BYTES "最大块(M)"
- FROM (SELECT TABLESPACE_NAME,
- ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
- ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
- FROM SYS.DBA_FREE_SPACE
- GROUP BY TABLESPACE_NAME) F,
- (SELECT DD.TABLESPACE_NAME,
- ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
- FROM SYS.DBA_DATA_FILES DD
- GROUP BY DD.TABLESPACE_NAME) D
- WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
- ORDER BY 3desc;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "已扩展空闲空间(M)",
(SELECT free_space_mb+free_allocate_mb FROM dba_tablespace_free a where a.tablespace_name= f.tablespace_name) "总剩余空间",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 3 desc;
查询结果:
其中一个表空间lars01_index分配了3T,但是只用了8%,现在我们想回收这个表空间的数据,如何回收处理?
我们可以按照文件查询:
- select tablespace_name,
- file_name,
- a.file_id,
- bytes / 1024 / 1024 file_size,
- round(a.free_size, 2) free_size,
- autoextensible,
- increment_by next,
- round(maxbytes / 1024 / 1024 / 1024) max_size,
- round(((bytes / 1024 / 1024) - a.free_size) / (bytes / 1024 / 1024) * 100,
- 2) used_rate
- from dba_data_files b,
- (select file_id, sum(bytes) / 1024 / 1024 free_size
- from dba_free_space
- group by file_id) a
- where b.file_id = a.file_id
- and tablespace_name ='LARS01_INDEX'
- order by used_ratedesc;
select tablespace_name,
file_name,
a.file_id,
bytes / 1024 / 1024 file_size,
round(a.free_size, 2) free_size,
autoextensible,
increment_by next,
round(maxbytes / 1024 / 1024 / 1024) max_size,
round(((bytes / 1024 / 1024) - a.free_size) / (bytes / 1024 / 1024) * 100,
2) used_rate
from dba_data_files b,
(select file_id, sum(bytes) / 1024 / 1024 free_size
from dba_free_space
group by file_id) a
where b.file_id = a.file_id
and tablespace_name ='LARS01_INDEX'
order by used_rate desc;
我们针对第一个文件435:
- SQL> selectmax(block_id) from dba_extentswhere file_id=435 and tablespace_name='LARS01_INDEX';
- MAX(BLOCK_ID)
- -------------
- 4177033
- SQL> select 4177033*8/1024from dual;
- 4177033*8/1024
- --------------
- 32633.0703
SQL> select max(block_id) from dba_extents where file_id=435 and tablespace_name='LARS01_INDEX';
MAX(BLOCK_ID)
-------------
4177033
SQL> select 4177033*8/1024 from dual;
4177033*8/1024
--------------
32633.0703
这里最大块是在32G的位置,从这里看到尽快使用率是1%,也就是使用了320M,但是由于这个文件的位置,所以我们要回收的话,是无法直接resize的。
我们查看下文件高水位的分布,按照block_id进行排序:
从这里可以看出有不少对象是在比较高的地方:
把这些对象移动到新的表空间里面:
移除完比较大的block_id之后再次查询:
- SQL> selectmax(block_id)*8/1024 from dba_extentswhere file_id=435 and tablespace_name='LARS01_INDEX';
- MAX(BLOCK_ID)*8/1024
- --------------------
- 537.070313
SQL> select max(block_id)*8/1024 from dba_extents where file_id=435 and tablespace_name='LARS01_INDEX';
MAX(BLOCK_ID)*8/1024
--------------------
537.070313
这里显示: 最大的block_id为537M,也就是这个是可以回收的文件的高水位:
SQL> alter database datafile '+DG_DATA8/oss139/datafile/lars01_index.464.798314649'resize 576M;
数据库已更改。
这个是单个文件的处理回收方法:
如果文件很多,我们可以直接这样查询:
- select a.file#,a.name,a.bytes/1024/1024 Resize,(a.bytes-HWM*a.block_size)/1024/1024 ReleaseMB,
- 'alter database datafile '''||a.name||'''resize '||ceil(HWM*a.block_size/1024/1024+30)||'M;' ResizeCmd ---这里我习惯往前放大30M
- from v$datafile a,
- (select file_id,max(block_id+blocks-1) HWMfrom dba_extents
- group by file_id) b
- where a.file#=b.file_id(+)
- and (a.bytes-HWM*a.block_size)/1024/1024>100; ---空闲多余100M以上才回收
from:http://blog.csdn.net/huangchao_sky/article/details/8283733