Oracle釋放高水位線

來源:互聯網
上載者:User

Oracle釋放高水位線

/*****************************************************************
原因:由於原匯出資料庫沒有整理資料表空間其中主要包括兩方面,一是使用者產生太多的DELETE,致使表的高位線(HWM)在很高的位置,所以儘管資料量很小,但是佔據的資料表空間很大,二是索引沒有重建,頻繁的刪除以及更新使得索引越來越大,REBUILD索引是個很必要的事情
*****************************************************************/

--** 優先處理CUX客制化對象

--Step1
--Tablespace Summary
  SELECT A.TABLESPACE_NAME,
        A.TOTAL M_TOTAL,
        NVL (B.USED, 0) M_USED,
        NVL ( (B.USED / A.TOTAL) * 100, 0) PCT_USED,
        A.FILE_NAME
    FROM (  SELECT TABLESPACE_NAME,
                  SUM (BYTES) / (1024 * 1024) TOTAL,
                  WMSYS.WM_CONCAT (FILE_NAME) FILE_NAME
              FROM SYS.DBA_DATA_FILES
          GROUP BY TABLESPACE_NAME) A,
        (SELECT TABLESPACE_NAME, BYTES / (1024 * 1024) USED
            FROM SYS.SM$TS_USED) B
  WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
  --AND A.TABLESPACE_NAME LIKE 'CUX%'
ORDER BY NVL ( (B.USED / A.TOTAL) * 100, 0) DESC;


--Tablespace Objects Detail
  SELECT A.TABLESPACE_NAME,
        A.TOTAL M_TOTAL,
        NVL (B.USED, 0) M_USED,
        NVL ( (B.USED / A.TOTAL) * 100, 0) PCT_USED,
        A.FILE_NAME,
        C.OJBECT_TYPE,
        C.OJBECT_NAME,
        C.M_OBJ_USED,
        NVL ( (C.M_OBJ_USED / A.TOTAL) * 100, 0) PCT_OBJ_USED
    FROM (  SELECT TABLESPACE_NAME,
                  SUM (BYTES) / (1024 * 1024) TOTAL,
                  WMSYS.WM_CONCAT (FILE_NAME) FILE_NAME
              FROM SYS.DBA_DATA_FILES
          GROUP BY TABLESPACE_NAME) A,
        (SELECT TABLESPACE_NAME, BYTES / (1024 * 1024) USED
            FROM SYS.SM$TS_USED) B,
        (SELECT TABLESPACE_NAME,
                SEGMENT_NAME OJBECT_NAME,
                SEGMENT_TYPE OJBECT_TYPE,
                (BYTES / 1024 / 1024) M_OBJ_USED
            FROM DBA_SEGMENTS
          WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX')) C
  WHERE    A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
        AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
ORDER BY NVL ( (B.USED / A.TOTAL) * 100, 0) DESC;


--Step2
-------------------------------------
--釋放高水位線 HWM
--只對有清理過資料的表對象執行(耗時過長)
DECLARE
  L_SQL1        VARCHAR2 (1000);
  L_SQL2        VARCHAR2 (1000);
BEGIN
  FOR R IN (SELECT (OWNER || '.' || SEGMENT_NAME) OBJ
              FROM DBA_SEGMENTS
              WHERE SEGMENT_TYPE = 'TABLE' AND TABLESPACE_NAME LIKE 'CUX%'
              AND SEGMENT_NAME IN('CUX_INV_ISSUE_OA_LOG'))
  LOOP
      L_SQL1 := 'alter table ' || R.OBJ || ' enable row movement';
      L_SQL2 := 'alter table ' || R.OBJ || ' shrink space';
      DBMS_OUTPUT.PUT_LINE (R.OBJ);

      EXECUTE IMMEDIATE L_SQL1;
      EXECUTE IMMEDIATE L_SQL2;
  END LOOP;
END;


-------------------------------
/*
--暫不應用
--Rebuild Index
DECLARE
  L_SQL  VARCHAR2 (1000);
BEGIN
  FOR R IN (SELECT (OWNER||'.'||SEGMENT_NAME) OBJ
              FROM DBA_SEGMENTS
              WHERE SEGMENT_TYPE = 'INDEX' AND TABLESPACE_NAME = 'CUX_INDEX')
  LOOP
      L_SQL := 'alter index ' || R.OBJ || ' rebuild online';
      DBMS_OUTPUT.PUT_LINE (L_SQL);

      EXECUTE IMMEDIATE L_SQL;
  END LOOP;
END;
*/

--刪除表同時刪除資源回收筒 Shift+Delete
--DROP TABLE CUX.CUX_INV_ISSUE_OA_LOG_TEST1 PURGE;
--alter database datafile 'filename' resize size;

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.