sqlのoracle表空间扩容

sqlのoracle表空间扩容

业务场景：

记录一下，2024年7月24，发生了一场不大不小的生产事故，导致一个地级市整个城市的某个业务系统停服一上午。也吸取一下深刻的教训。简单来讲，导致该事故最直接的原因就是这个业务系统现场人员为按时巡检导致，该系统数据库表空间满了没人知道，直到客户反馈业务传输有问题了，才开始排查，排查发现录入的信息数据库内无法查找，抽取日志查看发现表空间无法自动扩容。
所以运维工作中，日常巡检的必要性还是毋庸置疑的。

扩容用有dba权限的账号，通过plsql客户端直接操作的。是使用增加数据文件的方式进行扩容。建议表空间超过90%就直接扩容。

–查询表空间使用情况
SELECT a.tablespace_name "表空间名", round(total / (1024 * 1024 * 1024), 2) "表空间大小(G)", round(free / (1024 * 1024 * 1024), 2) "表空间剩余大小(G)", round((total - free) / (1024 * 1024 * 1024), 2) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name

–临时表空间使用率
select c.tablespace_name "临时表空间名", round(c.bytes / 1024 / 1024 / 1024, 2) "临时表空间大小(G)", round((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, 2) "临时表空间剩余大小(G)", round(d.bytes_used / 1024 / 1024 / 1024, 2) "临时表空间使用大小(G)", round(d.bytes_used * 100 / c.bytes, 4) || '%' "使用率 %" from (select tablespace_name, sum(bytes) bytes from dba_temp_files GROUP by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool GROUP by tablespace_name) d where c.tablespace_name = d.tablespace_name;

–查询表空间位置
SELECT TABLESPACE_NAME "表空间名", BYTES/1024/1024 "表空间大小(M)", FILE_NAME "文件路径",FILE_ID "文件ID" FROM DBA_DATA_FILES order by TABLESPACE_NAME,FILE_NAME;

扩容：
–一个表空间最大为32g，超过32g需要增加，例如：

alter tablespace DAS ADD  datafile '/mc_data/oradata/zsk/das01.dbf' SIZE 30G;

扩展阅读：

删除表空间扩容文件:

#alter tablespace 表空间名称 drop datafile 文件id;
alter tablespace UNDOTBS1 drop datafile 6;

删除临时表空间扩容文件:

#alter tablespace 临时表空间名称 drop tempfile 文件id;
alter tablespace UNDOTBS2 drop tempfile 7;

清理表空间：

alter  tablespace  IRFS_TEMP shrink space;

创建各种类型表空间：

-- 创建大小为50mb的永久表空间TEST01，禁止自动扩展数据文件
create tablespace TEST01
logging
datafile'F:\app\oraclezq\oradata\orcl\TEST01.dbf' size 50m
reuse autoextend off;

-- 创建永久表空间TEST02，允许自动扩展数据文件，本地管理方式
create tablespace TEST02
logging
datafile'F:\app\oraclezq\oradata\orcl\TEST02.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local;

-- 创建永久表空间TEST03，允许自动扩展数据文件，本地管理方式，区分配方式为自动分配
create tablespace TEST03
logging
datafile'F:\app\oraclezq\oradata\orcl\TEST03.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local autoallocate;

-- 创建永久表空间TEST04，允许自动扩展数据文件，本地管理方式，区分配方式为定制分配
create tablespace TEST04
logging
datafile'F:\app\oraclezq\oradata\orcl\TEST04.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local uniform size 10m;

-- 创建永久表空间TEST05，允许自动扩展数据文件，本地管理方式，区分配方式为自动分配，段管理方式为自动管理
create tablespace test05
logging
datafile'F:\app\oraclezq\oradata\orcl\TEST05.dbf' size 50m
reuse autoextend on next 10m maxsize 200M
extent management local autoallocate
segment space management auto;

-- 创建永久表空间TEST06，允许自动扩展数据文件，本地管理方式，区分配方式为定制分配，段管理方式为手动管理
create tablespace test06
logging
datafile'F:\app\oraclezq\oradata\orcl\TEST06.dbf' size 50m
reuse autoextend on next 10m maxsize 200M
extent management local uniform size 10m
segment space management manual;

扩容oracle表空间的四种方法

示例1：新增数据文件
ALTER TABLESPACE DSA ADD DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\DAS01.DBF’ SIZE 102400M;

示例2：新增数据文件，允许数据文件自动增长
ALTER TABLESPACE DSA ADD DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\DSA01.DBF’ SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

示例3：允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\DSA01.DBF’ AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

示例4：手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DSA01.DBF’ RESIZE 100M;