oracle如何回收未使用的数据文件,几条sql帮你搞定

检查表空间使用率：

WITH A AS (SELECT d.tablespace_name “Name”, d.status “Status”,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), ‘99,999,990.90’) “Size (M)”,
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
‘99999999.99’
) USE,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
‘990.00’
) “Used %”
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE ‘LOCAL’ AND d.CONTENTS LIKE ‘TEMPORARY’)
UNION ALL
SELECT d.tablespace_name “Name”, d.status “Status”,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), ‘99,999,990.90’) “Size (M)”,
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, ‘99999999.99’) USE,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), ‘990.00’) “Used %”
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE ‘LOCAL’
AND d.CONTENTS LIKE ‘TEMPORARY’)
SELECT * FROM A ORDER BY 5;

筛选出你所想要的表空间：（我要总共大于50g，空闲大于40G）

set pagesize 2000
SELECT A.TABLESPACE_NAME,TOTAL,FREE,round((total - free) / total, 4) * 100 as percent
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 AS FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.FREE >= 50
AND B.TOTAL >= 40
order by percent desc;

拼接sql得到resize语句：

set linesize 2000
set pagesize 2000
select 'alter ‘||‘database ‘||‘datafile ‘’’||file_name||’’’ resize '||CEIL(Small_HWM_G*1.2)||‘G;’
from (SELECT a.tablespace_name,
file_name,
b.file_id,
c.VALUE / 1024 “Blk. size(Kb)”,
CEIL((NVL(hwm, 1) * c.VALUE) / 1024 / 1024 / 1024) Small_HWM_G,
CEIL(blocks * c.VALUE / 1024 / 1024 / 1024) Current_G,
CEIL(blocks * c.VALUE / 1024 / 1024 / 1024) -
CEIL((NVL(hwm, 1) * c.VALUE) / 1024 / 1024 / 1024) Free_G
FROM dba_data_files a,
(SELECT file_id, MAX(block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b,
(SELECT VALUE FROM v$parameter WHERE NAME = ‘db_block_size’) c
WHERE a.file_id = b.file_id(+)
and a.status != ‘INVALID’
and a.TABLESPACE_NAME in (‘A’,‘B’,‘C’));

执行得到的sql,注意表空间大小；

再赠送一个一键化扩表空间的sql:

#按阈值筛选出需要扩容的表空间，对当前数据文件判断，空间够resize datafile，空间不够add datafile
with free as
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 gb
from dba_free_space
group by tablespace_name),
total as
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 gb
from dba_data_files
group by tablespace_name),
t1 as
(select tablespace_name
from (select total.tablespace_name,
round((total.gb - free.gb) / total.gb * 100, 2) as used_pct
from free, total
where free.tablespace_name = total.tablespace_name)
where used_pct > 90
order by used_pct desc),
t2 as
(select a.tablespace_name,
a.AUTOEXTENSIBLE,
file_name,
max(file_name) over(partition by a.tablespace_name) as max_file,
file_id,
sum(bytes / 1024 / 1024) over(partition by a.tablespace_name) as total_mb,
bytes / 1024 / 1024 as mb,
32767 - bytes / 1024 / 1024 as extensible_mb,
ceil((sum(bytes / 1024 / 1024) over(partition by a.tablespace_name)) * 0.2) as pct10_mb
from dba_data_files a, dba_tablespaces b
where 1 = 1
and a.TABLESPACE_NAME = b.TABLESPACE_NAME
and a.tablespace_name in (select * from t1)
and bigfile = ‘NO’),
t3 as
(select t2.,
sum(extensible_mb) over(partition by tablespace_name) as total_ext_mb
from t2 t2
where 1 = 1
and mb <> 32767),
t4 as --key_tab
(select t3.,
case
when total_ext_mb - pct10_mb > 0 then
(case
when mb + pct10_mb > 32767 then
32767
else
mb + pct10_mb
end)
else
pct10_mb
end as d_size,
case
when total_ext_mb - pct10_mb > 0 then
‘resize’
else
‘add’
end as logic_1,
regexp_substr(regexp_substr(max_file, '([0-9]+)([a-zA-Z]).dbf${}^{\prime }){,}^{\prime }[0-9]{+}^{\prime })+1|{|}^{\prime }.db{f}^{\prime }asc1fromt3t3),t5as(selectcasewhenlogi{c}_1{=}^{\prime }resiz{e}^{\prime }the{n}^{\prime }alterdatabasedatafil{e}^{\prime }||fil{e}_{i}d|{|}^{\prime }resiz{e}^{\prime }||d_{s}ize|{|}^{\prime }m{;}^{\prime }els{e}^{\prime }altertablespac{e}^{\prime }||tablespac{e}_{n}ame|{|}^{\prime }adddatafil{e}^{\prime \prime \prime }||regex{p}_{r}eplace(ma{x}_{f}ile{,}^{\prime }[0-9]+.dbf$’, c1, 1, 1) || ‘’’ size ’ ||
case when pct10_mb>32767 then 32767 else pct10_mb end || ‘m;’
end as action,
t4.
from t4 t4)
select distinct action from t5;

writen by caozhenyu