当前位置：首页 > news >正文

ORACLE表空间扩容

news 2025/6/25 8:00:26

Oracle表空间创建与空间不足运维操作指南

一、表空间创建操作

1. 基本表空间创建语法

CREATE TABLESPACE tablespace_name
DATAFILE '/path/to/datafile.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

2. 创建表空间详细参数说明

小文件表空间(默认)

CREATE TABLESPACE ts_small
DATAFILE '/u01/oradata/DB1/ts_small01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 2G;

大文件表空间(单个超大文件)

CREATE BIGFILE TABLESPACE ts_large
DATAFILE '/u01/oradata/DB1/ts_large01.dbf' SIZE 5G;

临时表空间

CREATE TEMPORARY TABLESPACE temp_ts
TEMPFILE '/u01/oradata/DB1/temp_ts01.dbf' SIZE 1G;

二、表空间空间监控

1. 常用监控SQL

-- 表空间使用情况概览
SELECT df.tablespace_name "表空间",df.bytes/1024/1024 "总大小(MB)",(df.bytes-fs.bytes)/1024/1024 "已用(MB)",fs.bytes/1024/1024 "空闲(MB)",ROUND(100*(df.bytes-fs.bytes)/df.bytes) "使用率(%)"
FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df,(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;-- 数据文件自动扩展状态
SELECT file_name, tablespace_name, autoextensible, bytes/1024/1024 "当前大小(MB)", increment_by*8/1024 "增量(MB)", maxbytes/1024/1024 "最大大小(MB)"
FROM dba_data_files;

2. 设置监控预警

-- 创建表空间使用率监控表
CREATE TABLE tbs_monitor_history (monitor_time TIMESTAMP,tablespace_name VARCHAR2(30),used_pct NUMBER(5,2)
);-- 定期收集统计信息(可设置为JOB)
INSERT INTO tbs_monitor_history
SELECT SYSTIMESTAMP, a.tablespace_name, ROUND((a.bytes-b.bytes)/a.bytes*100,2) used_pct
FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

三、表空间空间不足处理方案

1. 扩容现有数据文件

-- 手动调整数据文件大小
ALTER DATABASE DATAFILE '/u01/oradata/DB1/users01.dbf' RESIZE 2G;-- 启用自动扩展(如果未启用)
ALTER DATABASE DATAFILE '/u01/oradata/DB1/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

2. 添加新的数据文件

-- 向表空间添加新数据文件
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/DB1/users02.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

3. 清理表空间(释放空间)

-- 查找大表/大对象
SELECT segment_name, segment_type, owner, bytes/1024/1024 MB
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC;-- 归档/清理历史数据
-- 重建表释放碎片空间
ALTER TABLE schema.table_name MOVE TABLESPACE users;
-- 重建索引
ALTER INDEX schema.index_name REBUILD TABLESPACE users;

4. 启用Oracle空间压缩

-- 表压缩(适用于大量重复数据)
ALTER TABLE schema.table_name COMPRESS FOR OLTP;-- 表空间级压缩
CREATE TABLESPACE compressed_ts
DATAFILE '/u01/oradata/DB1/compressed01.dbf' SIZE 1G
DEFAULT COMPRESS FOR OLTP;

四、自动化运维脚本

1. 自动扩容脚本(PL/SQL)

DECLAREv_sql VARCHAR2(1000);v_tbs_name VARCHAR2(30) := 'USERS';v_extend_size NUMBER := 100; -- MBv_max_size NUMBER := 10240; -- MB (10G)
BEGINFOR f IN (SELECT file_id, file_name, bytes/1024/1024 current_sizeFROM dba_data_files WHERE tablespace_name = v_tbs_nameAND autoextensible = 'YES'AND (bytes/1024/1024 + v_extend_size) <= v_max_size)LOOPv_sql := 'ALTER DATABASE DATAFILE ''' || f.file_name || ''' RESIZE ' || (f.current_size + v_extend_size) || 'M';EXECUTE IMMEDIATE v_sql;DBMS_OUTPUT.PUT_LINE('Expanded: ' || f.file_name || ' to ' || (f.current_size + v_extend_size) || 'MB');END LOOP;
END;
/

2. 表空间自动维护JOB

BEGINDBMS_SCHEDULER.CREATE_JOB (job_name        => 'AUTO_TBS_MAINTENANCE',job_type        => 'PLSQL_BLOCK',job_action      => 'BEGIN -- 检查空间使用率>90%的表空间FOR t IN (SELECT tablespace_name FROM dba_tablespace_usage_metrics WHERE used_percent > 90)LOOP-- 执行扩容操作-- 可以调用上面的自动扩容脚本END LOOP;END;',start_date      => SYSTIMESTAMP,repeat_interval => 'FREQ=HOURLY; INTERVAL=1',enabled         => TRUE,comments        => 'Automatic tablespace maintenance job');
END;
/