表空间回缩

https://blog.csdn.net/tswisdom/article/details/42460577?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-1-42460577-blog-79145988.pc_relevant_vip_default&spm=1001.2101.3001.4242.1&utm_relevant_index=4 今天对数据库的表空间进行了回缩 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