临时表空间管理与使用

—数据表空间使用率

SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 

SELECT total.tablespace_name,
       Round(total.MB, 2)           AS Total_MB,
       Round(total.MB - free.MB, 2) AS Used_MB,
       Round(( 1 - free.MB / total.MB ) * 100, 2)
       || '%'                       AS Used_Pct
FROM   (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name;

SELECT a.tablespace_name                        "表空间名",
       total                                    "表空间大小",
       free                                     "表空间剩余大小",
       (total-free)                         "表空间使用大小",
       Round((total-free)/total,4)*100   "使用率   %"
FROM   (SELECT tablespace_name,
               Sum(bytes) free
        FROM   DBA_FREE_SPACE
        GROUP  BY tablespace_name) a,
       (SELECT tablespace_name,
               Sum(bytes) total
        FROM   DBA_DATA_FILES
        GROUP  BY tablespace_name) b
WHERE  a.tablespace_name = b.tablespace_name

—临时表空间
—1.TEMP表空间大小

SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE#                        AS FILE_NUMBER
    ,NAME                           AS NAME
    ,CREATION_TIME                  AS CREATION_TIME
    ,BLOCK_SIZE                     AS BLOCK_SIZE
    ,BYTES/1024/1024/1024           AS "FILE_SIZE(G)"
    ,CREATE_BYTES/1024/1024/1024    AS "INIT_SIZE(G)"
    ,STATUS                         AS STATUS
    ,ENABLED                        AS ENABLED
FROM V$TEMPFILE;

—2.TEMP表空间大小

SET LINESIZE 1200
    COL TABLESPACE_NAME FOR A30
   COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME                 AS TABLESPACE_NAME
        ,FILE_NAME                     AS FILE_NAME
        ,BLOCKS                        AS BLOCKS
        ,STATUS                        AS STATUS
        ,AUTOEXTENSIBLE                AS AUTOEXTENSIBLE
        ,BYTES/1024/1024/1024          AS "FILE_SIZE(G)"
        ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
                          MAXBYTES/1024/1024/1024)
                                       AS "MAX_SIZE(G)"
        ,INCREMENT_BY                  AS "INCREMENT_BY"
        ,USER_BYTES/1024/1024/1024     AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;

—3.TEMP表空间使用率(不准）

SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
       TT.TOTAL - TU.USED                                    AS "FREE(G)",
       TT.TOTAL                                              AS "TOTAL(G)",
       ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
       ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME, 
              SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
       FROM GV_$TEMP_SPACE_HEADER
       GROUP BY TABLESPACE_NAME) TU ,
     (SELECT TABLESPACE_NAME,
              SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
       FROM DBA_TEMP_FILES
       GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

—实际使用率

SELECT D.tablespace_name,
       SPACE "SUM_SPACE(M)",
       blocks "SUM_BLOCKS",
       used_space "USED_SPACE(M)",
       Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
       SPACE - used_space "FREE_SPACE(M)"
  FROM (SELECT tablespace_name,
               Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
               SUM(blocks) BLOCKS
          FROM dba_temp_files
         GROUP BY tablespace_name) D,
       (SELECT tablespace,
               Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM v$sort_usage
         GROUP BY tablespace) F
 WHERE D.tablespace_name = F.tablespace(+);
   AND D.tablespace_name in ('TEMP', 'TEMP1')

—4.TEMP具体使用情况

SELECT A.USERNAME,
       A.TABLESPACE,
       A.SQL_ID,
       A.SEGTYPE,
       B.BYTES_USED / 1024 / 1024 / 1024 || 'G',
       B.BYTES_FREE / 1024 / 1024 / 1024
  FROM V$TEMPSEG_USAGE A
  JOIN V$TEMP_SPACE_HEADER B
    ON A.TABLESPACE = B.TABLESPACE_NAME;

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATA' SIZE 20G; 
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; 

ALTER TABLESPACE TEMP SHRINK TEMPFILE '';
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 20M;