【Oracle SQL】表空间使用率

---

1. 查询表空间使用率

select t.tablespace_name,
       trunc((d.tbs_size - nvl(s.free_space, 0)) / 1024 / 1024) used_M,
       trunc(d.tbs_size / 1024 / 1024) L_space,
       trunc(d.tbs_maxsize / 1024 / 1024) P_space,
       trunc(nvl(s.free_space, 0) / 1024 / 1024) L_free,
       trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0)) / 1024 / 1024) P_free,
       decode(d.tbs_maxsize,
              0,
              0,
              trunc((d.tbs_size - nvl(s.free_space, 0)) * 100 /
                    d.tbs_maxsize)) P_usage
  from (select SUM(bytes) tbs_size,
               SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
               tablespace_name tablespace
          from (select nvl(bytes, 0) bytes,
                       nvl(maxbytes, 0) maxbytes,
                       tablespace_name
                  from dba_data_files
                union all
                select nvl(bytes, 0) bytes,
                       nvl(maxbytes, 0) maxbytes,
                       tablespace_name
                  from dba_temp_files)
         group by tablespace_name) d,
       (select sum(bytes) free_space, tablespace
          from (select bytes, tablespace_name tablespace
                  from dba_free_space
                union all
                select free_space bytes, tablespace_name tablespace
                  from dba_temp_free_space)
         group by tablespace) s,
       dba_tablespaces t
 where t.tablespace_name = d.tablespace(+)
   and t.tablespace_name = s.tablespace(+)
 order by 7 desc;

---

2. 查询表空间数据文件路径

--查询表空间对应的数据文件
select * from dba_data_files a where a.tablespace_name = 'TBS_USER';
SELECT * FROM dba_tablespaces where tablespace_name = 'TBS_USER';
select * from dba_temp_files a where a.tablespace_name = 'TBS_USER';

--查询表空间对应的数据文件和创建时间
select b.creation_time, a.* from dba_data_files a, v$datafile b
where a.file_id = b.file#
and a.tablespace_name = 'TBS_USER'

--查询数据库服务器磁盘空间大小（RAC）
select group_number, name, state, type, total_mb, free_mb, ROUND((total_mb - free_mb)/total_mb*100) used_rate
from v$asm_diskgroup;

--或服务器查看
单个实例查看磁盘空间：ssh服务器 =》 命令:df -h =》 找到数据文件对应的目录，查看磁盘大小是否充足 
RAC查看磁盘空间：ssh登录服务器grid用户 =》 命令：asmcmd =》 命令：lsdg =》 找到数据文件对应的目录，查看磁盘大小是否充足 

3. 表空间扩容

3.1 普通表空间扩容

--扩容数据文件的大小上限，若未设置数据文件上限，优先此方式
--上限设置为32G，最大32G
alter database datafile '/home/oracle/datafile01.dbf' autoextend on next 10m maxsize 32G;

--上限设置为无限制，最大32G
alter database datafile '/home/oracle/datafile01.dbf' autoextend on next 10m maxsize unlimited;

--增加数据文件的数量，数据文件已使用接近上限32g
alter tablespace TBS_SXRPT add datafile '/home/oracle/datafile01.dbf' size 100m autoextend on next 10m maxsize unlimited;

3.2 大表空间扩容

alter database datafile '/home/oracle/datafile01.dbf' autoextend on next 10m maxsize 4T; 

3.3 临时表空间扩容

alter tablespace TEMP_TEST add TEMPFILE '/home/oracle/datafile01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;

3.4 创建表空间

--永久表空间/UNDO表空间
create TABLESPACE TEMP1 DATAFILE '/home/oracle/undo01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;

--临时表空间
create TEMPORARY TABLESPACE TEMP1 TEMPFILE '/home/oracle/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;

--大表空间
create bigfile tablespace btbs_sxrpt datafile '/home/oracle/datafile01.dbf' size 100M REUSE autoextend on next 10M maxsize 580G;