oracle日常維護使用－－表空間相關

檢視所有以TBS開頭的表空間的使用率

SELECT A.TABLESPACE_NAME,
       ROUND(A.AA / 1024, 1) "總空間(G)",
       TRUNC((A.AA - B.BB) / A.AA * 100, 1) "使用率",
       ROUND(B.BB / 1024, 1) "剩餘空間"
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AA
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 BB
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME LIKE 'TBS%'
 ORDER BY 3 DESC;
           

 檢視所有以TBS開頭并且使用率超過60％的表空間

SELECT *
  FROM (SELECT A.TABLESPACE_NAME,
               ROUND(A.AA / 1024, 1) TOTAL_SPACE,
               TRUNC((A.AA - B.BB) / A.AA * 100, 1) USE_RATE,
               ROUND(B.BB / 1024, 1) LEAVE_SPACE
          FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AA
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 BB
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) B
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
           AND A.TABLESPACE_NAME LIKE 'TBS%'
         ORDER BY 3 DESC)
 WHERE USE_RATE > 60 ;
           

添加表空間的資料檔案

SELECT 'ALTER TABLESPACE ' || TABLESPACE_NAME || ' ADD DATAFILE ''' ||
       FILE_NAME || ''' SIZE 1024M;'
  FROM DBA_DATA_FILES
 WHERE (TABLESPACE_NAME, FILE_ID) IN
       (SELECT TABLESPACE_NAME, MAX(FILE_ID)
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME IN
               (SELECT TABLESPACE_NAME
                  FROM (SELECT A.TABLESPACE_NAME,
                               ROUND(A.AA / 1024, 1) TOTAL_SPACE,
                               TRUNC((A.AA - B.BB) / A.AA * 100, 1) USE_RATE,
                               ROUND(B.BB / 1024, 1) LEAVE_SPACE
                          FROM (SELECT TABLESPACE_NAME,
                                       SUM(BYTES) / 1024 / 1024 AA
                                  FROM DBA_DATA_FILES
                                 GROUP BY TABLESPACE_NAME) A,
                               (SELECT TABLESPACE_NAME,
                                       SUM(BYTES) / 1024 / 1024 BB
                                  FROM DBA_FREE_SPACE
                                 GROUP BY TABLESPACE_NAME) B
                         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
                           AND A.TABLESPACE_NAME LIKE 'TBS%'
                         ORDER BY 3 DESC)
                 WHERE USE_RATE > 70)
         GROUP BY TABLESPACE_NAME);
           

 清理指定分區

SELECT 'alter table ' || SEGMENT_NAME || ' truncate partition ' ||
         PARTITION_NAME || ';',
         A.*
    FROM DBA_SEGMENTS A
   WHERE OWNER = 'UAPP'
     AND SEGMENT_NAME = UPPER('TL_DAILY') 
   --AND (partition_name LIKE 'P01%' OR partition_name LIKE 'P02%' OR partition_name LIKE 'P03%')
   ORDER BY PARTITION_NAME;
           

搬遷指定表分區到新的表空間 

SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME ||
       ' MOVE PARTITION ' || PARTITION_NAME || ' TABLESPACE TBS_NEW  ;'
  FROM DBA_TAB_PARTITIONS
 WHERE TABLE_NAME = 'TL_DAILY';
           

搬遷索引表空間

SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME ||
       ' REBUILD PARTITION ' || PARTITION_NAME || ' TABLESPACE TBS_NEW ;'
  FROM DBA_IND_PARTITIONS a
 WHERE INDEX_NAME  ＝ 'I_TL_DAILY';
           

臨時表空間使用情況

SELECT P.TABLESPACE_NAME,
       ROUND(SUM(P.BYTES_CACHED) / 1024 / 1024) BYTES_CACHED,
       ROUND(SUM(P.BYTES_USED) / 1024 / 1024) BYTES_USED
  FROM V$TEMP_EXTENT_POOL P
 GROUP BY P.TABLESPACE_NAME;