扩展表空间（PDB数据库）：add_tbs.sh

#!/bin/bash

source ~/.bash_profile

export pdb_name=pdb      #PDB名称
export ts_name=default_tbs       #表空间
export inc_size_gb=1   #需要增加的GB数,单位G

#单次增长上限
inc_size_mb_threshold=$((32 * 1024))
inc_size_mb=$((${inc_size_gb} * 1024))

pdb_name=$(echo $pdb_name | tr 'a-z' 'A-Z')
ts_name=$(echo $ts_name | tr 'a-z' 'A-Z')
echo PDB名:${pdb_name}
echo 表空间名: ${ts_name}

#该函数执行SQL
function exec_select(){
sqlplus -s '/as sysdba' <    set head off feedback off serveroutput on lines 1000
   $1
   quit;
EOF
}

function exec_ddl(){
sqlplus -s '/as sysdba' <    set head off feedback off serveroutput on
   alter session set container=$1;
   $2
   quit;
EOF
}

db_role=$(exec_select "select database_role from v\$database;")
echo 主备库角色:${db_role}
if [ ${db_role} != 'PRIMARY' ]; then
    echo 表空间扩容需要在主库执行
    exit 1
fi

pdb_exist=$(exec_select "select count(*) from CDB_PDBS where pdb_name = '${pdb_name}';")
if [ $pdb_exist -ne 1 ]; then
   echo 目标PDB不存在
   exit 1
fi

bigfile_ts=$(exec_select "SELECT bigfile FROM CDB_PDBS d join cdb_tablespaces t on d.con_id = t.con_id WHERE d.pdb_name = '${pdb_name}' and t.tablespace_name = '${ts_name}';")
if [ ! $bigfile_ts ]; then
   echo PDB${pdb_name}下${ts_name}表空间不存在
   exit 1
fi

# 文件尺寸上限
file_size_mb_limit=$((32 * 1024 - 100))
if [ $bigfile_ts = 'YES' ]; then
   echo "big file tablespace"
   file_size_mb_limit=$((32 * 1024 * 1024 - 512))
else
   echo "normal tablespace"
fi

data_file_info=$(exec_select "SELECT * FROM (SELECT f.file_name, ROUND(f.bytes/1024/1024,5) size_mb, ROUND(f.maxbytes/1024/1024,5) max_size_mb,f.autoextensible FROM CDB_DATA_FILES f join CDB_PDBS d on f.con_id = d.con_id WHERE d.pdb_name ='${pdb_name}' and f.tablespace_name = '${ts_name}' order by max_size_mb) where rownum = 1;")
file_name=$(echo ${data_file_info} | awk '{ print $1 }')
file_size_mb=$(echo ${data_file_info} | awk '{ print $2 }' | tr '.' '0.')
file_max_size_mb=$(echo ${data_file_info} | awk '{ print $3 }' | tr '.' '0.' )
file_autoextensible=$(echo ${data_file_info} awk '{ print $4 }')

echo 数据文件${file_name}
echo 文件当前尺寸${file_size_mb}MB
echo 文件可扩展:$file_autoextensible
echo 文件自增最大尺寸:${file_max_size_mb}MB

# 增长后的新文件尺寸
new_file_size_mb=$( echo ${file_size_mb} + ${inc_size_mb} | bc)

if [ ${new_file_size_mb} -gt ${file_size_mb_limit} ]; then
   echo 超出数据文件最大限制${file_size_mb_limit}MB
   exit 1
fi

# 所属ASM磁盘组组名
asm_disk_group=$(echo $file_name | awk -F '/' '{ print $1 }')
asm_disk_group=${asm_disk_group/+/}

# 统计ASM磁盘空间
asm_dg_info=$(exec_select "SELECT free_mb,total_mb FROM V\$asm_diskgroup_stat WHERE name = '${asm_disk_group}';")
asm_dg_free_mb=$(echo ${asm_dg_info} | awk '{ print $1 }')
asm_dg_total_mb=$(echo ${asm_dg_info} | awk '{ print $2 }')
fix_asm_dg_free_mb=$(echo "${asm_dg_total_mb} * 0.95 - (${asm_dg_total_mb} - ${asm_dg_free_mb})" | bc | awk -F "." '{ print $1 }')

if [ ${inc_size_mb} -gt ${fix_asm_dg_free_mb} ]; then
   echo ${asm_disk_group}磁盘组剩余${asm_dg_free_mb}MB. 要预留5%的空间，最多能扩展${fix_asm_dg_free_mb}MB
   exit 1
fi

# 新文件尺寸超过文件自增最多尺寸的话，先调整文件最大尺寸
if [ ${new_file_size_mb} -gt ${file_max_size_mb} ]; then
   echo "ALTER DATABASE DATAFILE '${file_name}' AUTOEXTEND on MAXSIZE ${new_file_size_mb}M;"
   exec_ddl ${pdb_name} "ALTER DATABASE DATAFILE '${file_name}' AUTOEXTEND on MAXSIZE ${new_file_size_mb}M;"
fi

echo "ALTER DATABASE DATAFILE '${file_name}' resize ${new_file_size_mb}M;"
exec_ddl ${pdb_name} "ALTER DATABASE DATAFILE '${file_name}' RESIZE ${new_file_size_mb}M;"

echo 操作完成
new_ts_size_mb=$(exec_select "SELECT ROUND(sum(f.bytes)/1024/1024) size_mb FROM CDB_DATA_FILES f join CDB_PDBS d on f.con_id = d.con_id WHERE d.pdb_name = '${pdb_name}' and f.tablespace_name = '${ts_name}';")

segment_total_mb=$(exec_select "SELECT ROUND(sum(s.bytes)/1024/1024) size_mb FROM cdb_segments s join CDB_PDBS d on s.con_id = d.con_id WHERE d.pdb_name = '${pdb_name}' and s.tablespace_name = '${ts_name}';")

new_ts_free_size_mb=$(echo ${new_ts_size_mb} - ${segment_total_mb} | bc)
echo 新表空间尺寸${new_ts_size_mb}MB,空闲空间${new_ts_free_size_mb}MB

new_asm_dg_free_mb=$(exec_select "SELECT free_mb FROM V\$asm_diskgroup_stat WHERE name = '${asm_disk_group}';")
echo ${asm_disk_group}磁盘组最新剩余${new_asm_dg_free_mb}MB

=========================打印正常内容如下=============================

oracle@racdb1:/home/oracle>sh add_tbs.sh 
PDB名:PDB
表空间名: DEFAULT_TBS
主备库角色: PRIMARY
normal tablespace
数据文件+DATADG/YQBDB/FA8C41145AA66161E0530AC5A8C04C82/DATAFILE/default_tbs.286.1148745207
文件当前尺寸100MB
文件可扩展:+DATADG/YQBDB/FA8C41145AA66161E0530AC5A8C04C82/DATAFILE/default_tbs.286.1148745207 100 3072 YES awk { print $4 }
文件自增最大尺寸:3072MB
ALTER DATABASE DATAFILE '+DATADG/YQBDB/FA8C41145AA66161E0530AC5A8C04C82/DATAFILE/default_tbs.286.1148745207' resize 1124M;
操作完成
新表空间尺寸 1124MB,空闲空间330MB
DATADG磁盘组最新剩余 688MB

oracle@racdb1:/home/oracle>