本篇内容主要讲解“怎么查看oracle数据库表空间使用情况 ”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么查看oracle数据库表空间使用情况 ”吧!
1.现象
<br font-size:16px;white-space:normal;" />
2.诊断过程
SQL> set timing on
SQL> set autotrace traceonly
SQL> Select a.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_Free
2 From
3 (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
4 (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name;
已选择21行。
已用时间: 00: 23: 59.93
执行计划
----------------------------------------------------------
Plan hash value: 341960732
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 2 | 120 |
568 (51)| 00:00:07 |
|* 1 | HASH JOIN | | 2 | 120 |
568 (51)| 00:00:07 |
| 2 | VIEW | | 2 | 60 |
5 (20)| 00:00:01 |
| 3 | HASH GROUP BY | | 2 | 40 |
5 (20)| 00:00:01 |
| 4 | VIEW | DBA_DATA_FILES | 2 | 40 |
4 (0)| 00:00:01 |
| 5 | UNION-ALL | | | |
| |
| 6 | NESTED LOOPS | | 1 | 356 |
2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 342 |
1 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 329 |
1 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KCCFN | 1 | 310 |
0 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 19 |
1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I_FILE1 | 1 | |
0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 3 | 39 |
0 (0)| 00:00:01 |
| 13 | TABLE ACCESS CLUSTER | TS$ | 1 | 14 |
1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | I_TS# | 1 | |
0 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 399 |
2 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 385 |
1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 372 |
1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 362 |
0 (0)| 00:00:01 |
|* 19 | FIXED TABLE FULL | X$KCCFN | 1 | 310 |
0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 |
0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 10 |
1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | I_FILE1 | 1 | |
0 (0)| 00:00:01 |
|* 23 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 3 | 39 |
0 (0)| 00:00:01 |
| 24 | TABLE ACCESS CLUSTER | TS$ | 1 | 14 |
1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_TS# | 1 | |
0 (0)| 00:00:01 |
| 26 | VIEW | | 6 | 180 |
563 (51)| 00:00:07 |
| 27 | HASH GROUP BY | | 6 | 120 |
563 (51)| 00:00:07 |
| 28 | VIEW | DBA_FREE_SPACE | 2437K| 46M|
352 (21)| 00:00:05 |
| 29 | UNION-ALL | | | |
| |
| 30 | NESTED LOOPS | | 1 | 63 |
3 (0)| 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 57 |
3 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | FET$ | 1 | 39 |
3 (0)| 00:00:01 |
|* 33 | TABLE ACCESS CLUSTER | TS$ | 1 | 18 |
0 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_TS# | 1 | |
0 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 |
0 (0)| 00:00:01 |
| 36 | NESTED LOOPS | | 80 | 5520 |
4 (0)| 00:00:01 |
| 37 | NESTED LOOPS | | 80 | 5040 |
4 (0)| 00:00:01 |
|* 38 | TABLE ACCESS FULL | TS$ | 6 | 144 |
4 (0)| 00:00:01 |
|* 39 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 14 | 546 |
0 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 |
0 (0)| 00:00:01 |
|* 41 | HASH JOIN | | 2437K| 244M|
300 (25)| 00:00:04 |
| 42 | TABLE ACCESS FULL | RECYCLEBIN$ | 17654 | 172K|
221 (1)| 00:00:03 |
|* 43 | HASH JOIN | | 557K| 50M|
57 (88)| 00:00:01 |
| 44 | MERGE JOIN CARTESIAN | | 217 | 6510 |
7 (0)| 00:00:01 |
|* 45 | TABLE ACCESS FULL | TS$ | 6 | 144 |
4 (0)| 00:00:01 |
| 46 | BUFFER SORT | | 39 | 234 |
3 (0)| 00:00:01 |
| 47 | INDEX FAST FULL SCAN | I_FILE2 | 39 | 234 |
1 (0)| 00:00:01 |
| 48 | FIXED TABLE FULL | X$KTFBUE | 100K| 6347K|
45 (100)| 00:00:01 |
| 49 | NESTED LOOPS | | 1 | 86 |
45 (0)| 00:00:01 |
| 50 | NESTED LOOPS | | 1358 | 86 |
45 (0)| 00:00:01 |
| 51 | NESTED LOOPS | | 1 | 76 |
5 (0)| 00:00:01 |
| 52 | NESTED LOOPS | | 1 | 70 |
5 (0)| 00:00:01 |
|* 53 | TABLE ACCESS FULL | TS$ | 1 | 18 |
4 (0)| 00:00:01 |
| 54 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 |
1 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | |
1 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 |
0 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 1358 | |
8 (0)| 00:00:01 |
|* 58 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 10 |
40 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")
9 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE'
) AND
BITAND("FNFLG",4)<>4)
10 - filter("F"."SPARE1" IS NULL)
11 - access("FNFNO"="F"."FILE#")
12 - filter("FE"."FENUM"="F"."FILE#")
14 - access("F"."TS#"="TS"."TS#")
19 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE'
) AND
BITAND("FNFLG",4)<>4)
20 - filter("FNFNO"="HC"."KTFBHCAFNO")
21 - filter("F"."SPARE1" IS NOT NULL)
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$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 O
R
"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"."KTFBUESEG
TSN"="RB"."TS#"
AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."B
LOCK#")
43 - access("U"."KTFBUEFNO"="FI"."RELFILE#")
45 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 O
R
"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数据库表空间使用情况 ”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
