Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Blob table size

poornaJan 15 2012 — edited Jan 17 2012
Hi,

Oracle Version : 10.2.0.3
Operating System : linux

Here i want to get the size of a table which consists of BLOB data type. I am able to get the normal table size with the below query.
select
   segment_name           table_name,   
   sum(bytes)/(1024*1024) table_size_meg
from  
   user_extents
where 
   segment_type='TABLE'
and   
   segment_name = 'MYTAB'
group  segment_name;
Why i am asking is when i take data pump backup for the Blob table it is showing around 8.5 GB.But when i use the same query for the BLOB data type tables is not show the 8.5 GB it is showing some thing around 4 MB.

Please help me how to calculate the size of the table with BLOB data type.

Thanks & Regards,
Poorna Prasad.S

Comments

CKPT
Use



select dbms_lob.getlength(column_name) from table_name;
poorna
Hi CKPT,

Thanks for your quick replay . Here i don't want the length of the column i want size of the table.

Regards,
Poorna Prasad.S
NC
Hi,

Check this article

[url http://www.dba-oracle.com/t_finding_lob_blob_clob_length.htm]

Regards,
NC

Edited by: NC on Jan 16, 2012 11:43 AM
Helios-GunesEROL
Hi;

Please see:

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

Regard
Helios
poorna
Hi Helios,

Thanks for your replay. But when i use the query that meta link id i am getting multiple segment_name why it is like that and also to find the size of my table also it displays multiple records.
    select bytes, s.segment_name,s.segment_type
    from dba_segments s
    where s.segment_name='FAX_MGMT';

65536	FAX_MGMT	TABLE
65536	FAX_MGMT	TABLE
65536	FAX_MGMT	TABLE
196608	FAX_MGMT	TABLE
393216	FAX_MGMT	TABLE
393216	FAX_MGMT	TABLE
7340032	FAX_MGMT	TABLE
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 = 'FAX_MGMT'
    and s.segment_name = l.segment_name
    group by s.segment_name,s.segment_type;

2097152	          SYS_LOB0000076179C00007$$	LOBSEGMENT
65536	          SYS_LOB0000137128C00007$$	LOBSEGMENT
18874368	 SYS_LOB0000108312C00007$$	LOBSEGMENT
4032823296	SYS_LOB0000153767C00007$$	LOBSEGMENT
141557760	SYS_LOB0000109608C00007$$	LOBSEGMENT
143654912	SYS_LOB0000132039C00007$$	LOBSEGMENT
16777216	SYS_LOB0000086960C00007$$	LOBSEGMENT
Here my doubt is my my blob table contains different segment names.

Regards,
Poorna Prasad.S

Edited by: SIDDABATHUNI on Jan 17, 2012 12:29 PM

Edited by: SIDDABATHUNI on Jan 17, 2012 12:31 PM
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 14 2012
Added on Jan 15 2012
5 comments
6,081 views