查詢Oracle 資料庫中帶有lob欄位的某一個表的大小

查詢Oracle 資料庫中帶有lob欄位的某一個表的大小

注意：由於lob欄位有獨立的lob segment來儲存，故對於帶有lob欄位的表，不能僅僅查詢dba_segments.

以下指令碼來自：

How to Compute the Size of a Table containing Outline CLOBs and BLOBs[Article ID 118531.1]

--------------------------------------------------------------------------------

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

--------------------------------------------------------------------------------

經過修改:改為了NVL(SUM(S.BYTES),0)

SQL> col "TOTAL TABLE SIZE" format 99999999999999

---注意：以下sql可以直接執行。為了方便複製該指令碼，因此不貼SQL語句的log，而是放上了執行時的SQL語句。

1. SELECT
2. (SELECT NVL(SUM(S.BYTES),0) -- The Table Segment size
3. FROM DBA_SEGMENTS S
4. WHERE S.OWNER = UPPER('LC0019999') AND
5. (S.SEGMENT_NAME = UPPER('ZWPZK'))) +
6. (SELECT NVL(SUM(S.BYTES),0) -- The Lob Segment Size
7. FROM DBA_SEGMENTS S, DBA_LOBS L
8. WHERE S.OWNER = UPPER('LC0019999') AND
9. (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ZWPZK') AND L.OWNER = UPPER('LC0019999'))) +
10. (SELECT NVL(SUM(S.BYTES),0) -- The Lob Index size
11. FROM DBA_SEGMENTS S, DBA_INDEXES I
12. WHERE S.OWNER = UPPER('LC0019999') AND
13. (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ZWPZK') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LC0019999')))
14. "TOTAL TABLE SIZE"
15. FROM DUAL;
17. TOTAL TABLE SIZE
18. ----------------
19. 3571869286
20. SQL> select 3571869286/1024/1024/1024 from dual;
21. 35718692864/1024/1024/1024
22. --------------------------
23. 3.3265625