Oracle database 表空间不足 ORA-01653

原创已于 2022-11-10 16:20:12 修改 · 526 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #dba

于 2022-11-10 09:45:43 首次发布

Oracle database 专栏收录该内容

36 篇文章

订阅专栏

本文详细解读Oracle表空间不足错误ORA-01653，提供表空间使用检查、单空间分析、数据文件管理方法，以及Linux/Windows下增加数据文件和表空间实例。涵盖关键操作和注意事项。

Oracle表空间不足 ORA-01653

查看表空间使用情况

查看所有表空间

SELECT a.tablespace_name "tablespace_name", 
100-ROUND((NVL(b.bytes_free,0)/a.bytes_alloc)*100,2) "use(%)",
ROUND(a.bytes_alloc/1024/1024/1024,2) "all(G)",
ROUND(NVL(b.bytes_free,0)/1024/1024/1024,2) "free(G)",
ROUND((a.bytes_alloc-NVL(b.bytes_free,0))/1024/1024/1024,2) "use(G)", 
TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') "now"   
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;

查看单个表空间

SELECT a.tablespace_name "tablespace_name", 
100-ROUND((NVL(b.bytes_free,0)/a.bytes_alloc)*100,2) "use(%)",
ROUND(a.bytes_alloc/1024/1024/1024,2) "all(G)",
ROUND(NVL(b.bytes_free,0)/1024/1024/1024,2) "free(G)",
ROUND((a.bytes_alloc-NVL(b.bytes_free,0))/1024/1024/1024,2) "use(G)", 
TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') "now"   
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
and a.tablespace_name ='LMIS' ;

查看数据文件使用情况

select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from dba_data_files group by file_name,file_id,tablespace_name,autoextensible order by file_id;

select tablespace_name,autoextensible,sum(bytes)/1024/1024 total_mb from dba_data_files group by tablespace_name,autoextensible;

select a.tablespace_name,a.FILE_NAME,bytes/1024/1024/1024||'G' "size",a.AUTOEXTENSIBLE,a.MAXBYTES,a.INCREMENT_BY  
from  dba_data_files a order by a.FILE_NAME;

select a.tablespace_name,a.FILE_NAME,bytes/1024/1024/1024||'G' "size",a.AUTOEXTENSIBLE,a.MAXBYTES,a.INCREMENT_BY  
from  dba_data_files a  where a.tablespace_name='LMIS';

select a.tablespace_name,a.FILE_NAME,bytes/1024/1024/1024||'G' "size",a.AUTOEXTENSIBLE,a.MAXBYTES,a.INCREMENT_BY  from  dba_data_files a  where a.tablespace_name='UNDOTBS1';

增加数据文件允许自增长

linux 操作系统

#linux
ALTER TABLESPACE DMS ADD DATAFILE
'/u01/app/oracle/orcl/dms23.dbf' SIZE 10G
AUTOEXTEND on next 1G MAXSIZE 30G;

Windows操作系统

#windows
ALTER TABLESPACE LMIS ADD DATAFILE
'D:\ORA_DATA\LMIS\LMIS95.DBF' SIZE 2G ;

增加表空间时请注意操作系统对单个文件（数据文件）大小限制