Oracle表空间不足

Oracle临时表空间不足

-- 用户的缺省表空间、临时表空间
select t.username, t.default_tablespace, t.temporary_tablespace 
from dba_users t 
order by t.username;

-- 临时表空间对应临时文件的大小及使用情况
SELECT TABLESPACE_NAME
     , FILE_ID
     , FILE_NAME
     , BLOCKS
     , STATUS
     , AUTOEXTENSIBLE
     , BYTES/1024/1024               AS "FILE_SIZE(M)"
     , DECODE(MAXBYTES, 0, BYTES/1024/1024, MAXBYTES/1024/1024) AS "MAX_SIZE(M)"
     , INCREMENT_BY                  AS "INCREMENT_BY"
     , USER_BYTES/1024/1024          AS "USEFUL_SIZE"
     , BLOCKS - USER_BLOCKS          AS SYSTEM_USED
FROM DBA_TEMP_FILES
ORDER BY 2;

-- Or
SELECT TABLESPACE_NAME,
       FILE_ID,
       BYTES_USED/1024/1024 AS TABLESAPCE_USED,
       BYTES_FREE/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 2;


-- 增加、调整数据文件大小、增量
ALTER TABLESPACE TEMPX
  ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf'
  SIZE 4G 
  AUTOEXTEND ON
  NEXT 200M
  MAXSIZE 16G;
  
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
  AUTOEXTEND ON 
  NEXT 200M 
  MAXSIZE UNLIMITED;

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
  RESIZE 4G;


-- 收缩临时表空间
ALTER TABLESPACE TEMPX SHRINK SPACE KEEP 8G;



-- 更改缺省的临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPX;
ALTER USER tkk_us TEMPORARY TABLESPACE TEMPX;

补充，表空间大小查询

select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_free_space;
select * from dba_segments;
select * from dba_temp_files;
select * from dba_temp_free_space;


--根据dba_data_files和dba_free_space统计表空间大小和表空间剩余大小
SELECT A.TABLESPACE_NAME
       , B.TOTAL / 1024 / 1024 || 'M'
       , (B.TOTAL - A.USE) / 1024 / 1024 || 'M' FREE
FROM (
         SELECT c.tablespace_name, (c.p_use - d.p_free) AS USE
         FROM  (
                  SELECT tablespace_name, SUM(bytes) AS p_use
                  FROM dba_data_files
                  WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
                  GROUP BY tablespace_name
               ) C
             , ( SELECT tablespace_name, SUM(bytes) AS p_free
                 FROM dba_free_space
                 WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
                 GROUP BY tablespace_name
               ) D
          WHERE C.tablespace_name = D.tablespace_name
      ) A
     , (
           WITH TABLESPACE_TOTAL AS (
                SELECT tablespace_name, SUM(MAXBYTES) TOTAL
                FROM DBA_DATA_FILES T
                WHERE   T.AUTOEXTENSIBLE = 'YES'
                    AND T.TABLESPACE_NAME NOT IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
                GROUP BY tablespace_name
                UNION ALL
                SELECT tablespace_name, SUM(bytes) TOTAL
                FROM DBA_DATA_FILES T
                WHERE   T.AUTOEXTENSIBLE = 'NO'
                    AND T.TABLESPACE_NAME NOT IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
                GROUP BY tablespace_name
            )
            SELECT TABLESPACE_NAME, SUM(TOTAL) TOTAL
            FROM TABLESPACE_TOTAL
            GROUP BY TABLESPACE_NAME
      ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;


--根据dba_data_files和dba_segments统计表空间总大小和剩余大小
SELECT A.TABLESPACE_NAME, B.TOTAL / 1024 / 1024 || 'M', (B.TOTAL - A.USE) / 1024 / 1024 || 'M' FREE
FROM   (
           SELECT TABLESPACE_NAME, SUM(bytes) AS USE
           FROM dba_segments
           --WHERE tablespace_name  IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
           GROUP BY TABLESPACE_NAME
       ) A
     , (
           WITH TABLESPACE_TOTAL AS (
                SELECT tablespace_name, SUM(MAXBYTES) TOTAL
                FROM DBA_DATA_FILES T
                WHERE T.AUTOEXTENSIBLE = 'YES'
                   --AND T.TABLESPACE_NAME IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
                GROUP BY tablespace_name
                UNION ALL
                SELECT tablespace_name, SUM(bytes) TOTAL
                FROM DBA_DATA_FILES T
                WHERE T.AUTOEXTENSIBLE = 'NO' 
                   --AND T.TABLESPACE_NAME IN ('SYSTEM', 'SYSAUX', 'USERS', 'UNDOTBS1', 'UNDOTBS2')
                GROUP BY tablespace_name
           )
           
           SELECT TABLESPACE_NAME, SUM(TOTAL) TOTAL
           FROM TABLESPACE_TOTAL
           GROUP BY TABLESPACE_NAME
       ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;