本篇内容主要讲解“怎么查看oracle数据库表空间使用情况 ”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么查看oracle数据库表空间使用情况 ”吧!

1.现象
<br font-size:16px;white-space:normal;" />

2.诊断过程

SQL>settimingonSQL>setautotracetraceonlySQL>Selecta.Tablespace_Name,a.Total||'M'Total_Space,(a.Total-b.Free)||'M'Used_Space,To_Char((a.Total-b.Free)/a.Total*100,'99.99')||'%'Pct_Free2From3(SelectTablespace_Name,Sum(Bytes)/1024/1024TotalFromDba_Data_FilesGroupBytablespace_Name)a,4(SelectTablespace_Name,Sum(Bytes)/1024/1024FreeFromDba_Free_SpaceGroupByTablespace_Name)bwherea.Tablespace_Name=b.Tablespace_Name;已选择21行。已用时间:00:23:59.93执行计划----------------------------------------------------------Planhashvalue:341960732--------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||2|120|568(51)|00:00:07||*1|HASHJOIN||2|120|568(51)|00:00:07||2|VIEW||2|60|5(20)|00:00:01||3|HASHGROUPBY||2|40|5(20)|00:00:01||4|VIEW|DBA_DATA_FILES|2|40|4(0)|00:00:01||5|UNION-ALL|||||||6|NESTEDLOOPS||1|356|2(0)|00:00:01||7|NESTEDLOOPS||1|342|1(0)|00:00:01||8|NESTEDLOOPS||1|329|1(0)|00:00:01||*9|FIXEDTABLEFULL|X$KCCFN|1|310|0(0)|00:00:01||*10|TABLEACCESSBYINDEXROWID|FILE$|1|19|1(0)|00:00:01||*11|INDEXUNIQUESCAN|I_FILE1|1||0(0)|00:00:01||*12|FIXEDTABLEFIXEDINDEX|X$KCCFE(ind:1)|3|39|0(0)|00:00:01||13|TABLEACCESSCLUSTER|TS$|1|14|1(0)|00:00:01||*14|INDEXUNIQUESCAN|I_TS#|1||0(0)|00:00:01||15|NESTEDLOOPS||1|399|2(0)|00:00:01||16|NESTEDLOOPS||1|385|1(0)|00:00:01||17|NESTEDLOOPS||1|372|1(0)|00:00:01||18|NESTEDLOOPS||1|362|0(0)|00:00:01||*19|FIXEDTABLEFULL|X$KCCFN|1|310|0(0)|00:00:01||*20|FIXEDTABLEFIXEDINDEX|X$KTFBHC(ind:1)|1|52|0(0)|00:00:01||*21|TABLEACCESSBYINDEXROWID|FILE$|1|10|1(0)|00:00:01||*22|INDEXUNIQUESCAN|I_FILE1|1||0(0)|00:00:01||*23|FIXEDTABLEFIXEDINDEX|X$KCCFE(ind:1)|3|39|0(0)|00:00:01||24|TABLEACCESSCLUSTER|TS$|1|14|1(0)|00:00:01||*25|INDEXUNIQUESCAN|I_TS#|1||0(0)|00:00:01||26|VIEW||6|180|563(51)|00:00:07||27|HASHGROUPBY||6|120|563(51)|00:00:07||28|VIEW|DBA_FREE_SPACE|2437K|46M|352(21)|00:00:05||29|UNION-ALL|||||||30|NESTEDLOOPS||1|63|3(0)|00:00:01||31|NESTEDLOOPS||1|57|3(0)|00:00:01||32|TABLEACCESSFULL|FET$|1|39|3(0)|00:00:01||*33|TABLEACCESSCLUSTER|TS$|1|18|0(0)|00:00:01||*34|INDEXUNIQUESCAN|I_TS#|1||0(0)|00:00:01||*35|INDEXUNIQUESCAN|I_FILE2|1|6|0(0)|00:00:01||36|NESTEDLOOPS||80|5520|4(0)|00:00:01||37|NESTEDLOOPS||80|5040|4(0)|00:00:01||*38|TABLEACCESSFULL|TS$|6|144|4(0)|00:00:01||*39|FIXEDTABLEFIXEDINDEX|X$KTFBFE(ind:1)|14|546|0(0)|00:00:01||*40|INDEXUNIQUESCAN|I_FILE2|1|6|0(0)|00:00:01||*41|HASHJOIN||2437K|244M|300(25)|00:00:04||42|TABLEACCESSFULL|RECYCLEBIN$|17654|172K|221(1)|00:00:03||*43|HASHJOIN||557K|50M|57(88)|00:00:01||44|MERGEJOINCARTESIAN||217|6510|7(0)|00:00:01||*45|TABLEACCESSFULL|TS$|6|144|4(0)|00:00:01||46|BUFFERSORT||39|234|3(0)|00:00:01||47|INDEXFASTFULLSCAN|I_FILE2|39|234|1(0)|00:00:01||48|FIXEDTABLEFULL|X$KTFBUE|100K|6347K|45(100)|00:00:01||49|NESTEDLOOPS||1|86|45(0)|00:00:01||50|NESTEDLOOPS||1358|86|45(0)|00:00:01||51|NESTEDLOOPS||1|76|5(0)|00:00:01||52|NESTEDLOOPS||1|70|5(0)|00:00:01||*53|TABLEACCESSFULL|TS$|1|18|4(0)|00:00:01||54|TABLEACCESSCLUSTER|UET$|1|52|1(0)|00:00:01||*55|INDEXRANGESCAN|I_FILE#_BLOCK#|1||1(0)|00:00:01||*56|INDEXUNIQUESCAN|I_FILE2|1|6|0(0)|00:00:01||*57|INDEXRANGESCAN|RECYCLEBIN$_TS|1358||8(0)|00:00:01||*58|TABLEACCESSBYINDEXROWID|RECYCLEBIN$|1|10|40(0)|00:00:01|--------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")9-filter("FNNAM"ISNOTNULLAND"FNTYP"=4AND"INST_ID"=USERENV('INSTANCE')ANDBITAND("FNFLG",4)<>4)10-filter("F"."SPARE1"ISNULL)11-access("FNFNO"="F"."FILE#")12-filter("FE"."FENUM"="F"."FILE#")14-access("F"."TS#"="TS"."TS#")19-filter("FNNAM"ISNOTNULLAND"FNTYP"=4AND"INST_ID"=USERENV('INSTANCE')ANDBITAND("FNFLG",4)<>4)20-filter("FNFNO"="HC"."KTFBHCAFNO")21-filter("F"."SPARE1"ISNOTNULL)22-access("FNFNO"="F"."FILE#")23-filter("FE"."FENUM"="F"."FILE#")25-access("HC"."KTFBHCTSN"="TS"."TS#")33-filter("TS"."BITMAPPED"=0)34-access("TS"."TS#"="F"."TS#")35-access("F"."TS#"="FI"."TS#"AND"F"."FILE#"="FI"."RELFILE#")38-filter("TS"."CONTENTS$"=0AND"TS"."BITMAPPED"<>0AND("TS"."ONLINE$"=1OR"TS"."ONLINE$"=4))39-filter("TS"."TS#"="F"."KTFBFETSN")40-access("F"."KTFBFETSN"="FI"."TS#"AND"F"."KTFBFEFNO"="FI"."RELFILE#")41-access("TS"."TS#"="RB"."TS#"AND"RB"."TS#"="FI"."TS#"AND"U"."KTFBUESEGTSN"="RB"."TS#"AND"U"."KTFBUESEGFNO"="RB"."FILE#"AND"U"."KTFBUESEGBNO"="RB"."BLOCK#")43-access("U"."KTFBUEFNO"="FI"."RELFILE#")45-filter("TS"."CONTENTS$"=0AND"TS"."BITMAPPED"<>0AND("TS"."ONLINE$"=1OR"TS"."ONLINE$"=4))53-filter("TS"."BITMAPPED"=0)55-access("TS"."TS#"="U"."TS#")56-access("U"."TS#"="FI"."TS#"AND"U"."SEGFILE#"="FI"."RELFILE#")57-access("U"."TS#"="RB"."TS#")58-filter("U"."SEGFILE#"="RB"."FILE#"AND"U"."SEGBLOCK#"="RB"."BLOCK#")

到此，相信大家对“怎么查看oracle数据库表空间使用情况 ”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！