|
|
|
|
|
|
Re: Recreate tables with new storage parameters [message #173837 is a reply to message #173492] |
Wed, 24 May 2006 15:08 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Be sure to read this first: http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf
you MUST rebuild indexes after moving tables becuase all the rowids change.
CLOB, BLOB columns aren't trivial to move - not sure if there's an easy way...
create table tst1(pk number, col1 clob, col2 clob);
create index tst1_ix on tst1(pk);
alter table tst1 move tablespace users STORAGE (INITIAL 1M NEXT 1M);
alter index tst1_ix rebuild tablespace users STORAGE (INITIAL 1M NEXT 1M);
select object_name, object_type, created from user_objects where created > sysdate -1/24/60;
...
select segment_name, segment_type, tablespace_name
from user_segments
where segment_name in ('SYS_LOB0000067258C00002$$', 'SYS_LOB0000067258C00003$$', 'TST1')
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
TST1 TABLE USERS
SYS_LOB0000067258C00002$$ LOBSEGMENT TST_DATA
SYS_LOB0000067258C00003$$ LOBSEGMENT TST_DATA
|
|
|