Here is the script that I use to rebuild lobs. -- ---------------------------------------------------------------------------- -- FILE: $ORACLE_BASE/local/sql/lobrebuild.sql -- DESC: Creates table 'alter table modify lob' sql statements for a given table or tablespace. -- USAGE: -- ---------------------------------------------------------------------------- set lines 356 define tablespace = &tablespace define table = &table define owner = &owner define newtablespace = &newtablespace -- Get timestamp variable ----------------------------------------------------- set termout off break on currtime column currtime new_value _timestamp select to_char(sysdate,'YYYYMMDDHH24MISS') currtime from dual; clear breaks set termout off column wrap newline @setup_spool.sql set lines 200 spool lobrebuild&_timestamp..lst select distinct 'alter user ' || a.owner || ' quota unlimited on &newtablespace;' from dba_segments a, dba_lobs b where a.tablespace_name like upper('&tablespace') and a.owner like upper('&owner') and a.segment_type like '%LOB%' and a.owner = b.owner and a.segment_name = b.segment_name and b.table_name like upper('&table') -- and a.tablespace_name <> 'SYSTEM' ; select 'alter table ' || a.owner || '.' || b.table_name, ' move lob ('|| b.column_name ||') store as (tablespace &newtablespace ' ||');' wrap, ' ' wrap, 'prompt Done with: ' || a.segment_name wrap, 'alter tablespace ' || a.tablespace_name || ' coalesce ; ' wrap from dba_segments a, dba_lobs b, v$parameter where name = 'db_block_size' and a.tablespace_name like upper('&tablespace') and a.owner like upper('&owner') and a.owner = b.owner and a.segment_type like '%LOB%' -- and a.tablespace_name <> 'SYSTEM' -- and b.partitioned = 'NO' and a.segment_name = b.segment_name and b.table_name like upper('&table') order by a.tablespace_name, a.blocks desc ; spool off @reset.sql undefine newtablespace undefine tablespace undefine owner undefine table @lobrebuild&_timestamp..lst Pat ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark Sent: Friday, January 25, 2008 1:44 PM To: David Sharples; Bobak, Mark Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: Help with moving lobsegments and lobindexes.... Excellent, thanks, I missed that one! -- Mark J. Bobak Senior Database Administrator, System & Product Technologies ProQuest 789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346 +1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak@xxxxxxxxxxxxxxx<mailto:mark.bobak@xxxxxxxxxxxxxxx> www.proquest.com<http://www.proquest.com> www.csa.com<http://www.csa.com> ProQuest...Start here. From: David Sharples [mailto:davidsharples@xxxxxxxxx] Sent: Friday, January 25, 2008 2:34 PM To: Bobak, Mark Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Help with moving lobsegments and lobindexes.... Hi Mark, user_lobs will map it back to the table / column ___________________________________________________________________________________________________ CONFIDENTIALITY AND PRIVACY NOTICE Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com