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;