OraFAQ Forum: Server Administration » size problem storing JPG file in database

Home » RDBMS Server » Server Administration » size problem storing JPG file in database (DB 10.2.0.1.0, forms 10.1.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

size problem storing JPG file in database [message #543108]

Mon, 13 February 2012 02:41

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member

Hi

I am storing customer's snaps in a table ( column's data type as LONG RAW) using oracle forms Webutil. Now there are 250 snaps in the table. The file type of these snaps is JPG with the average size 30KB.

I made a backup using export utility before storing these snaps and the exported DMP file's size was 36MB. Now after storing these just 250 snaps of 30KB the DMP file's size is gone over 300MB.

Kindly help me do i need to change column's datatype? or some where in oracle forms's image item. Because on window's file system the size of these files is just 8MB.

Thanks in advance.

Report message to a moderator

Re: size problem storing JPG file in database [message #543119 is a reply to message #543108]

Mon, 13 February 2012 02:59

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member

I used this query to get the size of table and the result is.

SQL> ED
Wrote file afiedt.buf

  1  select segment_name table_name,sum(bytes)/(1024*1024) table_size_meg
  2  from user_extents
  3  where segment_type='TABLE'
  4  and segment_name = '&table_name'
  5* group by segment_name
SQL> /
Enter value for table_name: SNAPS
old   4: and segment_name = '&table_name'
new   4: and segment_name = 'SNAPS'

TABLE_NAME      TABLE_SIZE_MEG
--------------- --------------
SNAPS                      296

Report message to a moderator

Re: size problem storing JPG file in database [message #543121 is a reply to message #543119]

Mon, 13 February 2012 03:06

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

And the datatype should be BLOB. LONG RAW was deprecated in Oracle 8.0 in the last millennium, in 1997

Report message to a moderator

Re: size problem storing JPG file in database [message #543128 is a reply to message #543121]

Mon, 13 February 2012 03:16

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member

Thanks for reply

1) How can i change the datatype of column.
2) If create another column with BLOB type. Do i need to re-upload these images or should i use update statment to update new column.

Report message to a moderator

Re: size problem storing JPG file in database [message #543139 is a reply to message #543128]

Mon, 13 February 2012 03:30

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member

Thanks my problem solved. Just used this statment.


alter table snaps modify (snap blob)

After that

SQL> select
  2  segment_name table_name, 
  3  sum(bytes)/(1024*1024) table_size_meg
  4  from user_extents
  5  where segment_type='TABLE'
  6  and segment_name = '&table_name'
  7  group by segment_name
  8  /
Enter value for table_name: SNAPS
old   6: and segment_name = '&table_name'
new   6: and segment_name = 'SNAPS'

TABLE_NAME      TABLE_SIZE_MEG
--------------- --------------
SNAPS                    .0625

Thanks

[Updated on: Mon, 13 February 2012 03:31]

Report message to a moderator

Re: size problem storing JPG file in database [message #543153 is a reply to message #543139]

Mon, 13 February 2012 04:10

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because now the data are in another segment (LOB segment) and no more in the table segment.
Query USER_LOBS to know the name of the segment.

Regards
Michel

Report message to a moderator

Re: size problem storing JPG file in database [message #543174 is a reply to message #543153]

Mon, 13 February 2012 05:09

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member

name of segment is

SQL> select segment_name
  2  from user_lobs;

SEGMENT_NAME
------------------------------
SYS_LOB0000062991C00005$$

Report message to a moderator

Re: size problem storing JPG file in database [message #543175 is a reply to message #543174]

Mon, 13 February 2012 05:13

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

And then use that to query user_extents to see how much space they take.

Report message to a moderator

Re: size problem storing JPG file in database [message #543186 is a reply to message #543174]

Mon, 13 February 2012 05:42

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member


select segment_name,segment_type,bytes,blocks
from user_extents
where segment_name='SYS_LOB0000062991C00005$$'

SEGMENT_NAME              SEGMENT_TYPE            BYTES     BLOCKS
------------------------- ------------------ ---------- ----------
SYS_LOB0000062991C00005$$ LOBSEGMENT            1048576        128
SYS_LOB0000062991C00005$$ LOBSEGMENT            1048576        128
SYS_LOB0000062991C00005$$ LOBSEGMENT            8388608       1024
SYS_LOB0000062991C00005$$ LOBSEGMENT            8388608       1024
SYS_LOB0000062991C00005$$ LOBSEGMENT            8388608       1024

Report message to a moderator

Previous Topic:	analyse tables
Next Topic:	About Flashback Technology

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 02:40:02 CST 2024