Oracle表空间大小查询和清理

约定不等于承诺〃 2023-03-13 06:49 15阅读 0赞

\-- 统计表空间  
SELECT TABLESPACE\_NAME 表空间,  
       TO\_CHAR(ROUND(BYTES / 1024, 2), '99990.00') || '' 实有,  
       TO\_CHAR(ROUND(FREE / 1024, 2), '99990.00') || 'G' 现有,  
       TO\_CHAR(ROUND((BYTES - FREE) / 1024, 2), '99990.00') || 'G' 使用,  
       TO\_CHAR(ROUND(10000 \* USED / BYTES) / 100, '99990.00') || '%' 比例  
  FROM (SELECT A.TABLESPACE\_NAME TABLESPACE\_NAME,  
               FLOOR(A.BYTES / (1024 \* 1024)) BYTES,  
               FLOOR(B.FREE / (1024 \* 1024)) FREE,  
               FLOOR((A.BYTES - B.FREE) / (1024 \* 1024)) USED  
          FROM (SELECT TABLESPACE\_NAME TABLESPACE\_NAME, SUM(BYTES) BYTES  
                  FROM DBA\_DATA\_FILES  
                 GROUP BY TABLESPACE\_NAME) A,  
               (SELECT TABLESPACE\_NAME TABLESPACE\_NAME, SUM(BYTES) FREE  
                  FROM DBA\_FREE\_SPACE  
                 GROUP BY TABLESPACE\_NAME) B  
         WHERE A.TABLESPACE\_NAME = B.TABLESPACE\_NAME)  
  --WHERE TABLESPACE\_NAME LIKE 'CDR%'  
 ORDER BY FLOOR(10000 \* USED / BYTES) DESC;   
    
\--3 查看Oracle用户占了哪几个表空间及大小  
select \*  
  from (select owner || '.' || tablespace\_name name, sum(b) g  
          from (select owner,  
                       t.segment\_name,  
                       t.partition\_name,  
                       round(bytes / 1024 / 1024 / 1024, 2) b,  
                       tablespace\_name  
                  from dba\_segments t)  
         where owner not in  
               ('SYS', 'OUTLN', 'SYSTEM', 'TSMSYS', 'DBSNMP', 'WMSYS')  
         group by owner || '.' || tablespace\_name)  
 order by name;

\--4. 查看表、索引占用空间大小  
 select owner,segment\_name,bytes/1024/1024 from dba\_segments where owner='某用户' order by 3 desc;

\--5.清空回收站

purge user\_recyclebin;

purge dba\_recyclebin;

\--6.收缩表

alter table hkta.TSHAREDETAIL enable row movement;   
alter table hkta.TSHAREDETAIL shrink space cascade;  
alter table hkta.TSHAREDETAIL disable row movement;

\--7收缩索引

alter index ITREQUEST\_HIS shrink space;