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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

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

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.

Locked on Feb 14 2012

Added on Jan 15 2012

#general-database-discussions

5 comments

5,686 views

Oracle Database Discussions

Blob table size

Comments

Post Details