合 Oracle缩小表空间的方法
Tags： Oracle删除数据文件ORA-03262缩小表空间
👉 本文共约2196个字，系统预计阅读时间或需9分钟。
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~O(∩_∩)O\~：
① 收缩表空间的几种办法
② 表空间大小查询
③ AIX下查询磁盘空间大小的shell脚本
④ 删除数据文件的正确方法
⑤ ORA-03262处理
⑥ 缩小数据文件
⑦ su - grid asmcmd lsdg的使用
⑧ 其他常用命令
环境介绍
| 项目 | source db |
|---|---|
| db 类型 | RAC |
| db version | 11.2.0.3.0 |
| db 存储 | ASM |
| OS版本及kernel版本 | AIX 64位 7.1.0.0 |
处理过程
一个同事过来说，表空间不够了，让我帮忙看看，好吧，首先看一下表空间的大小，SQL语句如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | WITH wt1 AS (SELECT ts.TABLESPACE_NAME, df.all_bytes, decode(df.TYPE, 'D', nvl(fs.FREESIZ, 0), 'T', df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ, df.MAXSIZ, ts.BLOCK_SIZE, ts.LOGGING, ts.FORCE_LOGGING, ts.CONTENTS, ts.EXTENT_MANAGEMENT, ts.SEGMENT_SPACE_MANAGEMENT, ts.RETENTION, ts.DEF_TAB_COMPRESSION, df.ts_df_count FROM dba_tablespaces ts, (SELECT 'D' TYPE, TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM dba_data_files d GROUP BY TABLESPACE_NAME UNION ALL SELECT 'T', TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) FROM dba_temp_files d GROUP BY TABLESPACE_NAME) df, (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM dba_free_space GROUP BY TABLESPACE_NAME UNION ALL SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes FROM gv$sort_usage a, dba_tablespaces d WHERE a.tablespace = d.tablespace_name GROUP BY tablespace_name) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)) SELECT (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#, t.TABLESPACE_NAME TS_Name, round(t.all_bytes / 1024 / 1024) ts_size_M, round(t.freesiz / 1024 / 1024) Free_Size_M, round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per, round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g, round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / MAXSIZ, 3) USED_per_MAX, round(t.BLOCK_SIZE) BLOCK_SIZE, t.LOGGING, t.ts_df_count FROM wt1 t UNION ALL SELECT to_number('') TS#, 'ALL TS:' TS_Name, round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M, round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m, round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per, round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size, to_number('') "USED,% of MAX Size", to_number('') BLOCK_SIZE, '' LOGGING, to_number('') ts_df_count FROM wt1 t order by TS# ; |
TPCCIND表空间占用了99%了，剩下378M，不够用了，好吧，看看系统还有剩余的空间没有:
这个截图的shell脚本如下，可以在AIX环境下查看磁盘的使用情况，作者曾花了接近2天的时间写的（主要是不熟悉AWK，汗颜，，，）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [ZFLHRADB1:root]:/>more disk* if [ 1 = 1 ] ;then sum=0;asmnum=0 awk 'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n"; printf "%-43s %-18s %-14s %-8s %-15s %-14s\n","| disk ","| PVID ","| no_reserve ","| size(G)","| disktype ","| disk_storage |"; printf "------------------------------------------------------------------------------------------------------------------------------\n";}' for diskname in `lspv | grep disk | awk '{print $1}'`;do mydiskname=`ls -l /dev/r$diskname |grep -w /dev/r$diskname| cut -c 1-12,17-38,59-76` mydiskpvid=`lquerypv -H /dev/$diskname | cut -c 1-16` if [ "${mydiskpvid}" = "" ];then mydiskpvid="0000000000000000" ; fi 2>/dev/null mydiskreserve=`lsattr -El $diskname | grep -i reserve_policy | cut -c 17-30` mydisksize=`bootinfo -s $diskname 2>/dev/null` ; let "mydisksize1=$mydisksize/1024" 2>/dev/null mydiskvg=`lspv | grep -w $diskname | awk '{print $3}'` mydiskasmgroup=`lquerypv -h /dev/r$diskname|head -n 7|tail -n 1|awk -F ' ' '{print $NF}'|sed -e 's/\.//g' -e 's/\|//g' | awk '{ if ($1 != "") printf "+"$1 ; else print "NULL"}'` mydiskflag=`lquerypv -h /dev/r$diskname 2>/dev/null|grep -i orcldisk|wc -l` if [ ${mydisksize} -lt 1000 ];then mydisktype="HeadDisk" ; elif [ ${mydisksize} -gt 1000 -a ${mydiskflag} -gt 0 ];then mydisktype="ASM:"$mydiskasmgroup; elif [ ${mydisksize} -gt 1000 -a ${mydiskflag} -eq 0 -a $mydiskvg != "None" ];then mydisktype=$mydiskvg ; else mydisktype="Not_Used"; fi 2>/dev/null mydiskpath=`lspath -l $diskname 2>/dev/null|head -1|awk '{print $NF}'|sed "s/.$//"` mydiskstring=`odmget -q attribute="unique_id" CuAt|egrep "name|value"|paste - -|tr '\t' ' '|grep -w ${diskname}|sed 's/\"//g'` mydiskstorage=`echo ${mydiskstring} 2> /dev/null|awk '{ if($NF ~ /EMC/) {print "EMC"} else if ($NF ~ /NETAPP/) {print "NETAPP"} else if($NF ~ /HITACHI/) {print "HDS"}}'` mydiskdepth=`lsattr -El ${diskname}|grep queue_depth|awk '{print $2}'` mydiskstorage1=$mydiskstorage","$mydiskpath","$mydiskdepth [ $mydisksize1 -gt 1 -a ${mydiskflag} -gt 0 ] && { (( sum=sum+$mydisksize1 )) ; (( asmnum=$asmnum+1 )) ;} echo "$mydiskname" "$mydiskpvid" "$mydiskreserve" "${mydisksize1%.*}" "$mydisktype" "$mydiskstorage1" | awk '{printf "| %-10s %-6s %-8s %-14s | %-17s | %-12s | %-8s| %-15s | %-14s |\n",$1,$2,$3,$4,$5,$6,$7,$8,$9}' done awk 'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}' echo "ASMDISK_TOTAL:$asmnum" "TOTAL_SIZE(GB):$sum" |awk '{printf "| %-20s %-101s |\n", $1,$2}' awk 'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}' fi |
从截图可以看出分配给数据库的一共8块磁盘，看看数据库中有多少：
1 | SELECT * FROM v$asm_disk; |
看了8块磁盘分配完了，而系统剩余空间还有769M：
那就只能隔东墙补西墙，看到系统里TPCCHIS表空间剩下270G，经开发确认，可以缩小一些空间：
而缩小表空间有3种办法：
1、ALTER TABLESPACE test SHRINK SPACE KEEP 20M; --主要针对临时表空间
2、删除数据文件
3、缩小数据文件的大小
我们试试第一种：
1 | ALTER TABLESPACE TPCCHIS SHRINK SPACE KEEP 100G; |
ORA-12916: cannot shrink permanent or dictionary managed tablespace
报错了，永久表空间或是字典管理的不能SHRINK，只能针对temp表空间进行SHRINK SPACE。
试试第二种办法：删除数据文件
1 | SELECT * FROM dba_data_files d WHERE d.tablespace_name='TPCCHIS'; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
