RE: Help with moving lobsegments and lobindexes....

From: Elliott, Patrick <patrick.elliott_at_medtronic.com>
Date: Fri, 25 Jan 2008 14:52:01 -0600
Message-ID: <3B8B6A1700202C43A89D61CE495C894E0D0398ECB0@MSPM1BMSGM103.ent.core.medtronic.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Friday, January 25, 2008 1:44 PM
To: David Sharples; Bobak, Mark
Cc: oracle-l_at_freelists.org
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_at_il.proquest.com<mailto:mark.bobak_at_il.proquest.com>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>

ProQuest...Start here.

From: David Sharples [mailto:davidsharples_at_gmail.com]
Sent: Friday, January 25, 2008 2:34 PM
To: Bobak, Mark
Cc: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 25 2008 - 14:52:01 CST