今天对数据库的表空间进行了回缩
TEMP和SYSTEM的表空间
select /*+ ordered use_hash(a,b,c) */ a.file_id,a.file_name,a.filesize, b.freesize, (a.filesize-b.freesize) usedsize,
c.hwmsize, c.hwmsize - (a.filesize-b.freesize) unsedsize_belowhwm, a.filesize - c.hwmsize canshrinksize
from ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a,
( select file_id,round(sum(dfs.bytes)/1024/1024) freesize from dba_free_space dfs group by file_id ) b,
( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.file_id = b.file_id and a.file_id = c.file_id order by unsedsize_belowhwm desc
alter database datafile '/oracle/oradata/Mes/SYSTEM01.DBF' resize 17000M;
alter tablespace TEMP01 coalesce
Alter tablespace TEMP coalesce;
select name from v$tempfile;
D:\ORACLE\ORADATA\MES\TEMP01.DBF
select username,temporary_tablespace from dba_users;
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 * block_size) > 0
order by file#;
alter tablespace temp shrink tempfile '/ORACLE/ORADATA/MES/TEMP01.DBF' keep 17000m;
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,
2) "Free MB",
round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",
round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
2) as"Used_Rate(%)"
from SYS.V_$TEMP_SPACE_HEADER f,
DBA_TEMP_FILES d,
SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;
select tablespace_name,
file_name,
bytes / 1024 / 1024 file_size,
autoextensible
from dba_temp_files;
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25498/viewspace-2909126/ ，如需转载，请注明出处，否则将追究法律责任。
