• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to find space usage of lob segments in Oracle database

How to find space usage of lob segments in Oracle database

March 16, 2019 by techgoeasy Leave a Comment

Generally A select from the BYTES column in DBA_SEGMENTS for the table shows the table segment and its size.

select bytes/1024/1024 ,segment_name from dba_segments where segment_name='&1' and segment_type='TABLE';

But does not include LOB (CLOB or BLOB) segments sizes.To calculate the total size for the table and the associated LOBS segments a sum of the following must occur:
the bytes for the table => from dba_segments
+
the bytes for the LOB segments => from dba_lobs and dba_segments where segment_type is LOBSEGMENT
+
the bytes for the LOB Index (Lob Locator) = from dba_indexes and dba_segments

Following query can be used to find the size of the table including LOBSEGMENTS and LOBINDEX

ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name:  '
SELECT
 (SELECT SUM(S.BYTES)                                                                                                 -- The Table Segment size
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Segment Size
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Index size
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE"
FROM DUAL;

Another query to find size of lob objects oracle

select sum(bytes), s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = '&TABNAME'
and s.segment_name = l.segment_name
group by s.segment_name,s.segment_type;

Let us take the example of FND_LOBS tables in Oracle Apps Database. FND_LOBS has LOB datatype column FILE_DATA

How to  find how much space is actually used by the lob segments

Query to find the actual bytes used by the lob datatype

select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS;
SUM(DBMS_LOB.GETLENGTH(FILE_DATA))
----------------------------------
13313

The total space allocated in the extents could be found running:

select sum(bytes), s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'FND_LOBS'
and s.segment_name = l.segment_name
group by s.segment_name,s.segment_type;

SUM(BYTES) SEGMENT_NAME SEGMENT_TYPE
---------- --------------------------------- ------------------
113444 SYS_LOB0000039757C00004$$ LOBSEGMENT

The total space allocated to the table

ACCEPT SCHEMA PROMPT 'Table Owner: '
ACCEPT TABNAME PROMPT 'Table Name:  '
SELECT
 (SELECT SUM(S.BYTES)                                                                                                 -- The Table Segment size
  FROM DBA_SEGMENTS S
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Segment Size
  FROM DBA_SEGMENTS S, DBA_LOBS L
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
 (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Index size
  FROM DBA_SEGMENTS S, DBA_INDEXES I
  WHERE S.OWNER = UPPER('&SCHEMA') AND
       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
  "TOTAL TABLE SIZE"
FROM DUAL;
TOTAL TABLE SIZE
---------------
1111233

Related Articles

How to move LOB segment to another tablespace

Query to check table size in Oracle

Information on FND_LOBS

How to find the High Water mark of the Oracle Table

Filed Under: Oracle, Oracle Database, Oracle Ebuisness Suite Tagged With: FND_LOBS, How to find how much space is actually used by the lobsegments

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to use sed to remove comments and blank lines
  • How to recover database using RMAN
  • How to check Stale statistics
  • Java web start(JWS) in R12
  • How to delete the archive logs in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us