小麦苗的今日寄语
世界之大，能相逢的人不多
人海茫茫，能相知的心很少
心其实很小很小，装一份爱足够
时间其实很少很少，陪一个人就好
孩子，有骨气！！！
仔细看，会笑翻你的~！
熊孩子，电脑玩多了吧~
傻孩子，狄大人看多了？
这个 。。。这个。。。不解释。。。
什么才是真正的简明扼要~今天俺知道了！
无痛的人流。。。噗。。。老师开始喷血了；
理想很丰满，现实很骨感！！！
死了……
土豪啊……
爱就是成全……
这是贿赂……
老师居然给98分，逆天了……
你这么牛，你mama知道吗……
老师好不给面子的说……
这题我好像也做过……
我想你大概也会得这么大的分…
今天给大家分享的是Oracle一次缩小表空间的处理过程。
等待事件历史文章~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
● 【故障处理】队列等待之enq: TX - row lock contention
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
●【等待事件】User I/O类 等待事件（2.1）--db file sequential read（数据文件顺序读）
●【等待事件】User I/O类 等待事件（2.2）--db file scattered read（数据文件离散读）
●【等待事件】User I/O类 等待事件（2.3）--db file parallel read
● 【等待事件】User I/O类 等待事件（2.4）--db file single write
● 【等待事件】User I/O类 等待事件（2.5）--direct path read（直接路径读、DPR）
● 【等待事件】User I/O类 等待事件（2.6）--direct path write（直接路径写、DRW）
● 【等待事件】User I/O类 等待事件（2.7）--direct path read/write temp
● 【等待事件】User I/O类 等待事件（2.8）--read by other session
● 【等待事件】User I/O类 等待事件（2.9）--local write wait
● 【等待事件】User I/O类 等待事件（2.10）--所有User I/O类 等待事件总结
● 【等待事件】System I/O类 等待事件（3.1）--db file parallel write
● 【等待事件】System I/O类 等待事件（3.2）--control file parallel write
● 【等待事件】System I/O类 等待事件（3.3）--control file sequential read
Oracle一次缩小表空间的处理过程
Oracle一次缩小表空间的处理过程
1 BLOG文档结构图
2 前言部分
2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 收缩表空间的几种办法
② 表空间大小查询
③ AIX下查询磁盘空间大小的shell脚本
④ 删除数据文件的正确方法
⑤ ORA-03262处理
⑥ 缩小数据文件
⑦ su - grid asmcmd lsdg的使用
⑧ 其他常用命令
Tips：
本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
3 环境介绍
|
项目 |
source db |
|
db 类型 |
RAC |
|
db version |
11.2.0.3.0 |
|
db 存储 |
ASM |
|
OS版本及kernel版本 |
AIX 64位 7.1.0.0 |
4 处理过程
一个同事过来说，表空间不够了，让我帮忙看看，好吧，首先看一下表空间的大小，SQL语句如下：
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，汗颜，，，）：
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块磁盘，看看数据库中有多少：
SELECT * FROM v$asm_disk;
看了8块磁盘分配完了，而系统剩余空间还有769M：
那就只能隔东墙补西墙，看到系统里TPCCHIS表空间剩下270G，经开发确认，可以缩小一些空间：
而缩小表空间有3种办法：
1、ALTER TABLESPACE test SHRINK SPACE KEEP 20M; --主要针对临时表空间
2、删除数据文件
3、缩小数据文件的大小
我们试试第一种：ALTER TABLESPACE TPCCHIS SHRINK SPACE KEEP 100G;
ORA-12916: cannot shrink permanent or dictionary managed tablespace
报错了，永久表空间或是字典管理的不能SHRINK，只能针对temp表空间进行SHRINK SPACE。
试试第二种办法：删除数据文件
SELECT * FROM dba_data_files d WHERE d.tablespace_name='TPCCHIS';
SELECT D.HEADER_FILE, COUNT(1)
FROM DBA_SEGMENTS D
WHERE D.TABLESPACE_NAME = 'TPCCHIS'
GROUP BY D.HEADER_FILE;
所以，表都在23号文件上，其它文件应该可以删掉的。
ALTER TABLESPACE TPCCHIS DROP DATAFILE 13;
结果报错了：ORA-03262: the file is non-empty
[ZFLHRADB1:oracle]:/oracle>oerr ora 03262
03262, 00000, "the file is non-empty"
// *Cause: Trying to drop a non-empty datafile
// *Action: Cannot drop a non empty datafile
参考mos文章：
Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文档 ID 1050261.1)
违反下列任何一个条件，该datafile均不能被drop
1）必须为空，否则会报：ORA-03262: the file is non-empty。值得注意的是，non-empty的含义是有extent被分配给了table，而不是该table中有无rows，此时若是使用drop table xxx是不行的，必须使用 drop table xxx purge;或者在已经使用了drop table xxx的情况下，再使用purge table “xxx表在回收站中的名称”来purge该表，否则空间还是不释放，datafile依然drop不掉。
2）不能是所属表空间的第一个file
以上两者可以通过drop tablespace来达到目的。
3）不能在read-only表空间中。
4）不能被offline，否则会报：ORA-03264: cannot drop offline datafile of locally managed tablespace
针对该报错，解决方法为：
[oracle@rhel6 u01]$ oerr ora 3264
03264, 00000, "cannot drop offline datafile of locally managed tablespace"
// *Cause: Trying to drop offline datafile in lmts
// *Action: Try to drop file afetr making it online
5） Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace --该条来源于How to Drop a Datafile From a Tablespace (文档 ID 111316.1)
试了试其它文件还是不能删除，好吧，这个必须把表移动到其它表空间后才可以，算了，比较麻烦。
试试第3种办法：缩小数据文件的大小
SELECT 'alter database datafile ' || D.FILE_ID || ' resize 1G;'
FROM DBA_DATA_FILES D
WHERE D.TABLESPACE_NAME = 'TPCCHIS';
按照开发的要求，缩减100G即可，所以，我们执行3个即可，每个文件大小为30g，
alter database datafile 23 resize 1G;
alter database datafile 25 resize 1G;
alter database datafile 27 resize 1G;
执行完成之后，表空间大小：
空间多出100G：
我们可以给表空间TPCCIND加空间了：
ALTER TABLESPACE TPCCIND ADD DATAFILE '+DATA1' SIZE 30G;
ALTER TABLESPACE TPCCIND ADD DATAFILE '+DATA1' SIZE 30G;
ALTER TABLESPACE TPCCIND ADD DATAFILE '+DATA1' SIZE 30G;
增加完成后查看表空间大小：
好吧，可以了。
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 于 2016-09-06 09:00~2016-09-06 20:00 在中行完成
●【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】
本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请删除。
