假如,数据库存储使用的是文件系统存储(不是裸设备或ASM),即使表空间的数据文件设置为自动扩展,也可能出现数据量太大,无法扩展的错误。
那么作为管理员,必须能够在日常点检中时常关注存储情况,以在无法扩展之前解决问题。
本文给出了点检存储的步骤。
1、首先在操作系统中查看数据库数据文件相关磁盘的存储使用情况。
1)执行下面SQL语句,以在Oracle数据库查看各个表空间占用磁盘的情况
select file_disc , sum(bytes) /1024/1024/1024 "used space(G)"
from ( select substr( file_name, 0,2 ) as file_disc, bytes from dba_data_files
union all
select substr( file_name, 0,2 ) as file_disc, bytes from dba_temp_files
)
group by file_disc;
其中终端file_disc为文件系统磁盘盘符,used space(G)为所有表空间占用的该磁盘的空间之和。
2)在操作系统中查看1)中所涉及的磁盘的剩余空间。
如果剩余空间与 1)中给出的占用空间相比,很小,就说明磁盘空间已经不够用了,表空间很难再自动扩展了,管理员必须为表空间增加文件,并且这些文件只能增加到其他都用的磁盘上。
如果剩余空间与 1)中给出的占用空间相比,很大,说明磁盘空间还足够表空间自动扩展,但并不表示表空间一定能扩展成功。这时需要下面步骤来做进一步决定。
2、在数据库中执行下面语句,以查看表空间是否能自动扩展。
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)",
MAX_SPACE,
ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) "USED_RATE_MAX_SIZE(%)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
ROUND(SUM(DECODE(SIGN(MAXBYTES - BYTES), 1, MAXBYTES, BYTES)) / 1024 / 1024,
2) MAX_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)",
MAX_SPACE,
ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) "USED_RATE_MAX_SIZE(%)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
ROUND(SUM(DECODE(SIGN(MAXBYTES - BYTES), 1, MAXBYTES, BYTES)) / 1024 / 1024,
2) MAX_SPACE
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
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(+)
这里主要查看两列: tablespace_name 表空间名称 、USED_RATE_MAX_SIZE(%) 已占用空间占表空间最大可扩展大小的比例。
如果这个比例接近100%,那么说明表空间自动扩展的余地已经很小了。这时就必须要管理员干预了。要么增加表空间数据文件自动扩展的最大空间(注意,这个最大空间也是有限制的,受制于文件所在磁盘剩余空间,另外也不能查过 表空间block_size*(2的22次方-1) )。要么手动resize表空间。要么为表空间增加数据文件。
如果这个比例远小于100%,那么认为表空间的自由空间够用不需要自动扩展,或者虽然自由空间不够用了,但还能自动扩展。因而从不需要管理员干预。
另外说明一下,我认为有些文章所讲的,查看USED_RATE(%)就能做出判断,是不准确的。因为表空间的数据文件有可能是在自动扩展模式下,所以即使文件被完全占用了,也有可能成功的自动扩展。
再补充一些手动扩展表空间(数据文件)和修改表空间(数据文件)自动扩展上限的命令:
1、查找出oralce表空间的文件名、路径
select tablespace_name, file_id, file_name from dba_data_files;
2、重新设定表空间大小:
(1)修改表空间大小
ALTER DATABASE DATAFILE 'E:\datafile1.DBF'
RESIZE 3000M;(将表空间扩大到3G)
(2)设置表空间最大大小
ALTER DATABASE DATAFILE ''E:\datafile1.dbf
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;
当然如果数据文件所在的磁盘空间不足,就必须为表空间增加其他磁盘的数据文件了
alter tablespace L25_COM
add datafile 'f:\data001.dbf'
size 10M autoextend on maxsize 20G