Oracle表空间操作

Oracle查询表空间使用率

Oracle 数据库的表空间如果使用超过100%，会导致数据库死机

通过 sql 查询当前数据库所有表空间的使用率：

set pagesize 1000 linesize 1000;
col TABLESPACE_NAME for a40;
select tbs_used_info.tablespace_name,
       tbs_used_info.alloc_mb,
       tbs_used_info.used_mb,
       tbs_used_info.max_mb,
       tbs_used_info.free_of_max_mb,
       tbs_used_info.used_of_max || '%' used_of_max_pct
  from (select a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024) alloc_mb,
               round((a.bytes_alloc - nvl(b.bytes_free,
                                          0)) / 1024 / 1024) used_mb,
               round((a.bytes_alloc - nvl(b.bytes_free,
                                          0)) * 100 / a.maxbytes) used_of_max,
               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,
                                                       0)) / 1048576) free_of_max_mb,
               round(a.maxbytes / 1048576) max_mb
          from (select f.tablespace_name,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,
                                  'YES',
                                  f.maxbytes,
                                  'NO',
                                  f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name) a,
               (select f.tablespace_name,
                       sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
 order by tbs_used_info.used_of_max desc;

查询表空间使用的数据文件

SELECT tablespace_name, file_id, file_name, bytes, blocks
FROM dba_data_files
WHERE tablespace_name = 'table_spaceName';

给表空间增加数据文件

ALTER TABLESPACE table_spaceName
ADD DATAFILE 'DatafilePath' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;

temp数据文件处理

# 查询tempfile
SELECT name, bytes/1024/1024 AS "MB" FROM v$tempfile ORDER BY bytes;
# 修改位置
alter database rename file '+DATA/xxx.dbf' to 'xxx';
# 模糊查询
select file_id,file_name,bytes/1024/1024 MB,status,tablespace_name from dba_temp_files where file_name like '%temp%';