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 ;
增加表空间时请注意操作系统对单个文件(数据文件)大小限制