Oracle 数据库分析SQL及使用空间优化

查看表空间使用率

--查看表空间使用率
SELECT * FROM ( 
SELECT D.TABLESPACE_NAME "表空间名", 
        SPACE || 'M' "表空间大小", 
        BLOCKS "SUM_BLOCKS", 
        SPACE - NVL (FREE_SPACE, 0) || 'M' "已使用空间", 
        ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "使用率", 
        FREE_SPACE || 'M' "空闲空间" 
   FROM (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                  SUM (BLOCKS) BLOCKS 
             FROM DBA_DATA_FILES 
         GROUP BY TABLESPACE_NAME) D, 
        (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
             FROM DBA_FREE_SPACE 
         GROUP BY TABLESPACE_NAME) F 
  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
 UNION ALL                                                           
 SELECT D.TABLESPACE_NAME "表空间名", 
        SPACE || 'M' "表空间大小", 
        BLOCKS SUM_BLOCKS, 
        USED_SPACE || 'M' "已使用空间", 
        ROUND( NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "使用率", 
        NVL(FREE_SPACE, 0) || 'M' "空闲空间" 
   FROM (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                  SUM (BLOCKS) BLOCKS 
             FROM DBA_TEMP_FILES 
         GROUP BY TABLESPACE_NAME) D, 
        (  SELECT TABLESPACE_NAME, 
                  ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                  ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
             FROM V$TEMP_SPACE_HEADER 
         GROUP BY TABLESPACE_NAME) F 
   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
   ORDER BY 1)  
 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP'); 

检查表空间最大的段

--检查表空间最大的段
SELECT *
FROM (SELECT bytes, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSAUX' --表空间
ORDER BY bytes DESC)
WHERE ROWNUM < 10;

收缩/释放表空间

--释放表空间
select a.file#, a.name, a.bytes/1024/1024 "Current(MB)",
  ceil(HWM * a.block_size)/1024/1024 "ResizeTo(MB)",
  (a.bytes - HWM * a.block_size)/1024/1024 "Release(MB)",
  'alter database datafile '''||a.name||''' resize '|| ceil(HWM * a.block_size/1024/1024) || 'M;' "Resize CMD"
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 5;

 SYSTEM  SYSAUX 等表空间不是自动管理的表空间(ASSM), 不支持收缩表, 支持resize表空间 

--收缩表空间
ALTER DATABASE DATAFILE 'D:\ora_tablespace\XXXX.dbf' RESIZE 1024M;

收缩表/表空间

--语法总结：

ALTER TABLE <table_name> ENABLE ROW MOVEMENT   -->前提条件

ALTER TABLE <table_name> SHRINK SPACE [ <NULL> | COMPACT | CASCADE ];

ALTER TABLE <table_name> SHRINK SPACE COMPCAT; -->缩小表和索引，不移动高水位线，不释放空间

ALTER TABLE <table_name> SHRINK SPACE;         -->收缩表，降低高水位线;

ALTER TABLE <table_name> SHRINK SPACE CASCADE; -->收缩表，降低高水位线，并且相关索引也要收缩一下

ALTER TABLE <table_name> MODIFY LOB (lob_column) (SHRINK SPACE); -->收缩LOB段

ALTER INDEX <indx_name> SHRINK SPACE;              -->索引段的收缩，同表段

--收缩普通表，批量脚本

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space cascade;' from user_tables;

select'alter index '||index_name||' shrink space;' from user_indexes;

查看对象/Segment占用空间

--查看对象/Segment占用空间
SELECT * from (
  SELECT segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name 
  FROM dba_segments 
  WHERE tablespace_name in ('SYSTEM','SYSAUX')
  GROUP BY segment_name,tablespace_name
  ORDER BY 2 desc)
WHERE rownum <=20;

SELECT occupant_name "Item",space_usage_kbytes/1024 "Space Used (MB)",schema_name "Schema",move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc;

SELECT segment_name,sum(bytes)/1024/1024 MB 
FROM user_segments 
--WHERE segment_name=UPPER('aud$') 
GROUP BY segment_name
ORDER BY 2 desc;

SELECT owner,segment_name,segment_type,sum(bytes)/1024/1024 "size(M)"
FROM dba_extents
WHERE tablespace_name = 'SYSAUX'
GROUP BY owner,segment_name,segment_type
ORDER BY 4 desc;