Oracle维护之添加数据文件

1 以 oracle 用户登录 XXXX 主机

2 登录目标数据库
exoport ORACLE_SID = SID --根据实际情况指定
sqlplus /nolog
conn / as sysdba

3 查看现有表空间大小
set wrap off
set linesize 300
set pagesize 1000
col tablespace_name for a20
col status for a8
col “UsedPct%” format 990.99
col “MaxUsedPct%” format 990.99
col “MaxSize(MB)” format 9999990.99
col file_name for a60
select * from (
select tablespace_name, status, sum(Size_MB) “Size(MB)”, sum(UsedSize_MB) “UsedSize(MB)”, sum(FreeSize_MB) “FreeSize(MB)”, round(sum(UsedSize_MB)/sum(Size_MB)*100,2) “UsedPct%”, sum(MaxSize_MB) “MaxSize(MB)”, sum(MaxSize_MB-UsedSize_MB) “MaxFreeSize(MB)”, round(sum(UsedSize_MB)/sum(MaxSize_MB)*100,2) “MaxUsedPct%” from (
select df.tablespace_name, tbs.status, df.file_name,
round(decode(df.autoextensible, ‘YES’, max(df.maxbytes), max(df.bytes))/1024/1024, 2) MaxSize_MB,
round(max(df.bytes)/1024/1024, 2) Size_MB,
decode(sum(fs.bytes), null, 0, round(sum(fs.bytes)/1024/1024,2)) FreeSize_MB,
decode(sum(fs.bytes), null, 0, round((max(df.bytes)/1024/1024) - (sum(fs.bytes)/1024/1024),2)) UsedSize_MB
from dba_data_files df, dba_tablespaces tbs, dba_free_space fs
where df.tablespace_name = tbs.tablespace_name
and df.file_id = fs.file_id(+)
and tbs.contents = ‘PERMANENT’
group by df.tablespace_name, tbs.status, df.file_name, df.autoextensible, tbs.block_size
union all
select df.tablespace_name, tbs.status, df.file_name,
round(decode(df.autoextensible, ‘YES’, max(df.maxbytes), max(df.bytes))/1024/1024, 2) MaxSize_MB,
round(max(df.bytes)/1024/1024, 2) Size_MB,
round((max(df.bytes) - sum(decode(ue.status, ‘ACTIVE’, ue.bytes, ‘UNEXPIRED’, ue.bytes, 0)))/1024/1024, 2) FreeSize_MB,
round(sum(decode(ue.status, ‘ACTIVE’, ue.bytes, ‘UNEXPIRED’, ue.bytes, 0))/1024/1024, 2) UsedSize_MB
from dba_data_files df, dba_tablespaces tbs, dba_undo_extents ue
where df.tablespace_name = tbs.tablespace_name
and df.file_id = ue.file_id(+)
and tbs.contents = ‘UNDO’
group by df.tablespace_name, tbs.status, df.file_name, df.autoextensible
union all
select tf.tablespace_name, tbs.status, tf.file_name,
round(decode(tf.autoextensible, ‘YES’, tf.maxbytes, tf.bytes)/1024/1024,2) MaxSize_MB,
round(tf.bytes/1024/1024,2) Size_MB,
nvl(round((tf.bytes-ep.bytes_cached)/1024/1024,2),0) FreeSize_MB,
nvl(round(ep.bytes_cached/1024/1024,2),0) UsedSize_MB
from dba_temp_files tf, dba_tablespaces tbs, v$temp_extent_pool ep
where tf.tablespace_name = tbs.tablespace_name
and tf.file_id = ep.file_id(+)
) group by tablespace_name, status)
order by tablespace_name;

TABLESPACE_NAME STATUS Size(MB) UsedSize(MB) FreeSize(MB) UsedPct% MaxSize(MB) MaxFreeSize(MB) MaxUsedPct%

TBS_DATA ONLINE 843776 682141.69 161634.44 80.84 843776.00 161634.31 80.84
TBS_IDX ONLINE 278528 185409.71 93118.35 66.57 278528.00 93118.29 66.57

4 确定表空间扩容大小
UsedSize/(MaxSize + ExtendSize) <= 70%

例如，通过计算得出如下扩容需求
TBS_DATA 扩 160G (1016G)
TBS_IDX 扩 48G ( 316G)

5 文件系统扩容（如需要）
检查现有文件系统大小是否满足扩容需求
vgdisplay -v vgTBSDBdat01 --查看Free PE 大小是否满足
vgdisplay -v vgTBSDBidx01 --查看Free PE 大小是否满足
若不满足，扩充现有VG大小，或新建文件系统
新建vgTBSDBdat02，大小200G，并在其下新建如下LV：
新建lvTBSDBdat02 大小198G 挂载点/oradata/TBSDB/dat02
新建vgTBSDBidx02，大小100G，并在其下新建如下LV：
新建lvTBSDBidx02 大小99G 挂载点/oradata/TBSDB/idx02

6 扩容表空间
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA66.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA67.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA68.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA69.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA70.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA71.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA72.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA73.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA74.dbf’ size 16384M;
alter tablespace TBS_DATA add datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA75.dbf’ size 16384M;

alter tablespace TBS_IDX add datafile ‘/tbsdata/TBSDB/datafile/TBS_IDX12.dbf’ size 16384M;
alter tablespace TBS_IDX add datafile ‘/tbsdata/TBSDB/datafile/TBS_IDX13.dbf’ size 16384M;
alter tablespace TBS_IDX add datafile ‘/tbsdata/TBSDB/datafile/TBS_IDX14.dbf’ size 16384M;

7 检查扩容后的表空间大小
set lines 150
col tablespace_name for a20
col file_name for a60
select tablespace_name, file_name, bytes/1024/1024 size_mb, status, autoextensible from dba_data_files order by 1, 2;

表空间利用率检查：
select tablespace_name,
total_m,
free_m,
to_char(100 * (1 - free_m / total_m), ‘09.99’) || ‘%’ as pct_used
from (select tablespace_name, trunc(sum(bytes) / (1024 * 1024)) as total_m
from dba_data_files
group by tablespace_name),
(select tablespace_name as fs_ts_name,
trunc(sum(bytes) / (1024 * 1024)) as free_m
from dba_free_space
group by tablespace_name)
where tablespace_name = fs_ts_name
order by tablespace_name;
确认表空间利用率达到预期。

8 文件系统空间检查
df -g
确认 /tbsdata 文件系统使用率低于 80% 。

回退步骤：

1. oracle用户登录主机 XXXX
   sqlplus / as sysdba
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA66.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA67.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA68.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA69.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA70.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA71.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA72.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA73.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA74.dbf’;
   alter tablespace TBS_DATA drop datafile ‘/tbsdata/TBSDB/datafile/TBS_DATA75.dbf’;

alter tablespace TBS_IDX drop datafile ‘/tbsdata/TBSDB/datafile/TBS_IDX12.dbf’;
alter tablespace TBS_IDX drop datafile ‘/tbsdata/TBSDB/datafile/TBS_IDX13.dbf’;
alter tablespace TBS_IDX drop datafile ‘/tbsdata/TBSDB/datafile/TBS_IDX14.dbf’;