Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Help on Long Raw
Urgent Help on Long Raw [message #194924] Tue, 26 September 2006 03:27 Go to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
Hi,
I have the images stored in long raw object in one of the table.
But,need to clean up the table.

If i update the table with long raw column=NULL will release the memory space occupied in the data files. Or any other method to clean the large objects.

Eg:

UPDATE UNITHOLDERIMAGETBL SET SIGNATURE=NULL
WHERE IMAGEAVAILABLE <> 0;

HERE SIGNATURE COLUMN IS A LONG RAW COLUMN.

So,Will above will release the memory from data files.

Regards,
Anil

[Updated on: Tue, 26 September 2006 03:29]

Report message to a moderator

Re: Urgent Help on Long Raw [message #194938 is a reply to message #194924] Tue, 26 September 2006 04:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that you mean 'Disc space', not 'memory'

Setting the long_raws to null will release space in the tablespace by reducing the data in the individual blocks, but it won't reduce the size of the data files on the disc.
Re: Urgent Help on Long Raw [message #194976 is a reply to message #194924] Tue, 26 September 2006 06:27 Go to previous messageGo to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
I mean disc space only.

My intension here is one of our db occupied nearly 30GB as we have uploaded nearly 100000 customer data. So,i would like to clean up the data in tablespace as it is one of our test database.

Anyway,thanks for your info.

Rgds,
Anil
Re: Urgent Help on Long Raw [message #194977 is a reply to message #194976] Tue, 26 September 2006 06:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Once you've deleted the data, you can re-organise the table (do an ALTER TABLE <tablename> MOVE <name of current tablespace>) which will pack the table down into as few blocks as possible. This will probably free up enough contiguous space at the end of the datafiles to make resizing them worth doing.
You will need to rebuild all the indexes thoug, as all the rowids will have changed.
Re: Urgent Help on Long Raw [message #195065 is a reply to message #194977] Tue, 26 September 2006 16:22 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
a very important difference between LONG RAW and BLOB columns is that LONG RAW is stored inline with the rest of the record. BLOBS can be stored inline or in separete segments. This can be very important when doing a table scan because the BLOB column doesn't have to be read from disk (if it's in a separate segment).

Pleanty of other considerations too...
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_1.shtml

If a blob is stored in a separate segment - you can't just "move" the table to release overallocated space. I forget the best way to work around the issue.

CREATE TABLE BLOB_TST
( COL1 NUMBER,
BLOB_COL1 BLOB, -- inline
BLOB_COL2 BLOB -- in separate segment
)
LOB (BLOB_COL2) STORE AS ( disable STORAGE IN ROW );


alter table BLOB_TST move users;

ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
Re: Urgent Help on Long Raw [message #195223 is a reply to message #194924] Wed, 27 September 2006 08:10 Go to previous messageGo to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
JRowbottom,
After i update the large column to Null for all the rows,still my tablespace showing same disc space used up. But my UNDO tablespace is showing the 99% of alloted memory used. Earlier it was only 6% memory used up.

Also andrew again said i cannot move the tablepsace.

alter table unitholderimagetbl move TRX_DATA_AFS;

ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other perations.

Could u advise what can be done.

Thanks.
Re: Urgent Help on Long Raw [message #195231 is a reply to message #195223] Wed, 27 September 2006 08:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is what I said would happen - you have freed up a lot of space inside the blocks, but this will not reduce the overall size of the datafile without reorganising the tables in the datafile.

Don't worry about the UNDO tablespace - being full of data is what it's there for.

@Andrew_Again actually said you can't move the TABLE. The difference between a TABLE and a TABLESPACE is fairly important, so you should be sure you understand it.

You can move the table like this:

alter table blob_tst_2 move lob(blob_col2) store as ( tablespace <new blob tablespace> ) tablespace <new table tablespace>;[/CODE]

Previous Topic: Help to call Oracle Stored Funciton from ASP3.0
Next Topic: Create shcema authorization [not my username]
Goto Forum:
  


Current Time: Mon Dec 05 14:59:16 CST 2016

Total time taken to generate the page: 0.07767 seconds