Query the size of a table with a lob field in the Oracle database.
Note: Because the lob field has an independent lob segment for storage, for a table with a lob field, you cannot only query dba_segments.
The following scripts are from:
How to Compute the Size of a Table containing Outline CLOBs and BLOBs [Article ID 118531.1]
Modified to NVL (SUM (S. BYTES), 0)
SQL> col "TOTAL TABLE SIZE" format 99999999999999
--- Note: The following SQL statements can be executed directly. For the convenience of copying the script, the SQL statement at execution is placed instead of the log of the SQL statement. SELECT (select nvl (SUM (S. BYTES), 0) -- The Table Segment size FROM DBA_SEGMENTS s where s. OWNER = UPPER ('lc001129 ') AND (S. SEGMENT_NAME = UPPER ('zwpzk') + (select nvl (SUM (S. BYTES), 0) -- The Lob Segment Size FROM DBA_SEGMENTS S, DBA_LOBS l where s. OWNER = UPPER ('lc0020179') AND (L. SEGMENT_NAME = S. SEGMENT_NAME and l. TABLE_NAME = UPPER ('zwpzk') and l. OWNER = UPPER ('lc0020179') + (select nvl (SUM (S. BYTES), 0) -- The Lob Index size FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S. OWNER = UPPER ('lc0020179') AND (I. INDEX_NAME = S. SEGMENT_NAME and I. TABLE_NAME = UPPER ('zwpzk') AND INDEX_TYPE = 'lob' and I. OWNER = UPPER ('lc0020179') "total table size" from dual; total table size -------------- 3571869286SQL> select 3571869286/1024/1024/1024 from dual; 35718692864/1024/1024/1024 ------------------------ 3.3265625
How to view the space occupied by a table in oracle? This table contains blob fields.
Write one. Try:
Select a. TABLESPACE_NAME "TableSpace Name ",
Round (a. BYTES/1024/1024) "MB Allocated ",
Round (a. BYTES-nvl (B. BYTES, 0)/1024/1024) "MB Used ",
Nvl (round (B. BYTES/1024/1024), 0) "MB Free ",
Round (a. BYTES-nvl (B. BYTES, 0)/a. BYTES) * 100,2) "Pct Used"
From (select TABLESPACE_NAME,
Sum (BYTES) BYTES
From sys. dba_data_files
Group by TABLESPACE_NAME),
(Select TABLESPACE_NAME,
Sum (BYTES) BYTES
From sys. dba_free_space
Group by TABLESPACE_NAME) B
Where a. TABLESPACE_NAME = B. TABLESPACE_NAME (+)
And a. tablespace_name in ('tablespace name', 'tablespace name', 'tablespace name ');
Find the table name that contains a certain field in the oracle database.
-- The field name and column here are the same meaning: select column_name, table_name, data_type, data_length, data_precision, data_scale from user_tab_columns where column_name = 'field name '; -- find the relevant table name based on the field name. Record down -- query the queried table and find the table with this field select * from table name where field name = 'xiaoming'