Oracle 表空间相关操作 ORA-01654 解决

今天领导突然找到我说服务出问题了，上服务器一看，发现报表空间不足异常

由于之前没有过扩容 Oracle 表空间的经验，所以边 Google 边操作，解决了问题。过程记录一下方便日后查阅。

表空间容量指标查询

首先查询一下各个表空间的使用情况。

SELECT
	TABLESPACE_NAME "表空间",
	To_char( Round( BYTES / 1024, 2 ), '99990.00' ) || '' "总量",
	To_char( Round( FREE / 1024, 2 ), '99990.00' ) || 'G' "剩余",
	To_char( Round(( BYTES - FREE ) / 1024, 2 ), '99990.00' ) || 'G' "使用",
	To_char( Round( 10000 * USED / BYTES ) / 100, '99990.00' ) || '%' "比例" 
FROM
	(
	SELECT
		A.TABLESPACE_NAME TABLESPACE_NAME,
		Floor(
		A.BYTES / ( 1024 * 1024 )) BYTES,
		Floor(
		B.FREE / ( 1024 * 1024 )) FREE,
		Floor((
				A.BYTES - B.FREE 
			) / ( 1024 * 1024 )) USED 
	FROM
		( SELECT TABLESPACE_NAME TABLESPACE_NAME, Sum( BYTES ) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) A,
		( SELECT TABLESPACE_NAME TABLESPACE_NAME, Sum( BYTES ) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) B 
	WHERE
		A.TABLESPACE_NAME = B.TABLESPACE_NAME 
	) 
-- WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER BY
	Floor( 10000 * USED / BYTES ) DESC;

可以看到 SMARTCITY 的占用达到了 20G，截图为扩容后的，扩容前为 20G，所以之前会报表空间不足。

查询表空间对应的数据文件相关信息

select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name

可以看到文件 ID 为 9 的表空间已经使用完了，复制对应的物理文件名备用。

开始扩容

Tips: 单个数据文件最大为 32G

方法 1：改变已存在数据文件的大小

-- 扩展到32G
ALTER TABLESPACE SMARTCITY ADD DATAFILE '/home/oracle/oradata/smartcity.dbf' SIZE 32768M;

方法 2：允许现在的数据文件自动增长

--允许表空间自动扩容
ALTER DATABASE DATAFILE '/home/oracle/oradata/smartcity.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 20480M; 

方法 3：给表空间增加数据文件

-- 增加数据文件，初始大小为7G，可自动增长
ALTER TABLESPACE SMARTCITY ADD DATAFILE
'/home/oracle/oradata/smartcity_1.dbf' 
size 7167M autoextend on ;

以上三种方法均可，从之前的截图可以看出，我采用的是第三种方式。