RE: Help with moving lobsegments and lobindexes....
- From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
- To: "Mark.Bobak@xxxxxxxxxxxx" <Mark.Bobak@xxxxxxxxxxxx>, David Sharples <davidsharples@xxxxxxxxx>
- Date: Fri, 25 Jan 2008 14:52:01 -0600
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
Other related posts:
