Oracle表空间相关SQL操作

一、临时表空间

• 查看表空间占用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

• 查看临时表空间占用情况

SELECT D.TABLESPACE_NAME "临时表空间名",
       SPACE "表空间大小(M)",
       USED_SPACE "已使用空间(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "使用比(%)",
       NVL(FREE_SPACE, 0) "空闲空间(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_NAME,
               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

• 查看表空间、数据文件

select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files

• 查看库中每个表所占的空间大小，单位G

select Segment_Name,Sum(bytes)/1024/1024/1024 From User_Extents Group By Segment_Name s

二、undo表空间

• 查看undo表空间的名称

SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO';

• 查看已有的undo表空间数据文件存放路径

select FILE_NAME,TABLESPACE_NAME from  dba_data_files where TABLESPACE_NAME = 'UNDOTBS1';

• 查看undo表空间占用率

select tablespace_name,
       round(sum(decode(status, 'ACTIVE', bytes, 'UNEXPIRED', bytes, 0)) * 100 /
             sum(bytes),
             2) || '%' "used %"
  from DBA_UNDO_EXTENTS
group by tablespace_name
order by 1;

• 将undo表空间大小重新加大点

alter tablespace UNDOTBS1 add datafile '/opt/oracle_11g/app/oradata/orcl/undotbs02.dbf' size 1G autoextend on maxsize unlimited;