Oracle表空间分析统计与释放

一、统计表空间使用率

1、当前用户表空间（user）

--查看当前用户的表空间：
select a.tablespace_name,
       total "total(M)",
       free "free(M)",
       total - free "used(M)",
       trunc((total - free) / total, 4) * 100 || '%' percentused,
       trunc(free / total, 4) * 100 || '%' percentfree
  from (select tablespace_name, sum(bytes) / 1024 / 1024 total
          from user_segments
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 free
          from user_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name;

2、dba用户表空间-单位M

--------------单位MB
SELECT UPPER(F.TABLESPACE_NAME) "SPACE_NAME",
       D.TOT_GROOTTE_MB "SPACE_SIZE(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "SPACE_USE(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "USE_PER",
       F.TOTAL_BYTES "SPACE_FREE(M)",
       F.MAX_BYTES "MAX_BLOCK(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;

3、dba用户表空间-单位G

--------------单位GB
SELECT UPPER(F.TABLESPACE_NAME) "SPACE_NAME",
       D.TOT_GROOTTE_MB "SPACE_SIZE(G)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "SPACE_USE(G)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') || '%' "USE_PER",
       F.TOTAL_BYTES "SPACE_FREE(G)",
       F.MAX_BYTES "MAX_BLOCK(G)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;

4、查看表空间中各对象或模块的大小

------查看一个表所占的空间大小
SELECT bytes/1024/1024/1024 TABLE_SIZE ,a.TABLE_NAME,a.COLUMN_NAME,u.* FROM USER_SEGMENTS U ,user_ind_columns a
WHERE U.tablespace_name='BASE_BJCNCIOM' and u.segment_name=a.INDEX_NAME(+) ORDER BY TABLE_SIZE DESC ; 

5、查看模块下的对象或字段

select * from user_lobs a where a.SEGMENT_NAME='SYS_LOB0000111909C00008$$'

二、清空clob字段

1、更新clob字段

CREATE OR REPLACE PROCEDURE P_DEAL_CLOB_RECV IS
/*
create table tmp_wzh_recv_seq as
select a.recv_queue_seq from t_od_recv_queue_his a where a.recv_date<=to_char('2016-1-1','yyyy-mm-dd');
alter table tmp_wzh_recv_seq add(flag number(2) default 0);
Select a.flag,Count(1) From tmp_wzh_recv_seq a group by a.flag;
*/
  V_RECV_QUEUE_SEQ T_OD_RECV_QUEUE_HIS.RECV_QUEUE_SEQ%TYPE;
  CURSOR CUR_SEQ IS
    SELECT A.RECV_QUEUE_SEQ FROM TMP_WZH_RECV_SEQ A WHERE A.FLAG = 0;
BEGIN
  OPEN CUR_SEQ;
  LOOP
    FETCH CUR_SEQ
      INTO V_RECV_QUEUE_SEQ;
    EXIT WHEN CUR_SEQ%NOTFOUND;
    UPDATE T_OD_RECV_QUEUE_HIS A
       SET A.SRC_DATA = ''
     WHERE A.RECV_QUEUE_SEQ = V_RECV_QUEUE_SEQ;
    COMMIT;
    UPDATE TMP_WZH_RECV_SEQ A
       SET A.FLAG = 1
     WHERE A.RECV_QUEUE_SEQ = V_RECV_QUEUE_SEQ;
    COMMIT;
  END LOOP;
  CLOSE CUR_SEQ;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END P_DEAL_CLOB_RECV;

2、释放回收站

--清空当前用户的回收站
  purge recyclebin

3、表分析

exec dbms_stats.gather_table_stats(OWNNAME=> 'BJCNCIOM', TABNAME=> 't_od_recv_queue_his', ESTIMATE_PERCENT=>30, CASCADE=>true, DEGREE=>16,GRANULARITY=>'all');

三、压缩释放空间

1、压缩

alter table T_APP_SI_COMPONENT_16 move tablespace BASE_BJCNCIOM compress;
alter table T_APP_SI_COMPONENT_17 move tablespace BASE_BJCNCIOM compress;

2、重建索引（压缩后索引失效，需要重建）

create index IDX_APP_SI_COMPONENT_16_Y1 on T_APP_SI_COMPONENT_16 (ORDER_ID)
  tablespace INDX_BJCNCIOM;

alter index IDX_APP_SI_COMPONENT_16_Y1  rebuild;  
alter index IDX_APP_SI_COMPONENT_16_Y1  rebuild online;  

alter index IDX_APP_SI_COMPONENT_17_Y1  rebuild;  
alter index IDX_APP_SI_COMPONENT_17_Y1  rebuild online;

3、统计压缩后大小

SELECT ROUND(BYTES / 1024 / 1024 / 1024, 2) "TABLE_SIZE(G)",
       A.TABLE_NAME,
       A.COLUMN_NAME,
       U.*
  FROM USER_SEGMENTS U, USER_IND_COLUMNS A
 WHERE U.TABLESPACE_NAME = 'BASE_BJCNCIOM'
   AND U.SEGMENT_NAME = A.INDEX_NAME(+)
   AND U.SEGMENT_NAME LIKE 'T_APP_SI_COMPONENT%'
 ORDER BY 1 DESC;