Index Online Rebuild

Online Rebuild

1. create Temp Tablespace (psapreorg, size 30G)

2. index online rebuild script
select 'alter index ' ||'"' ||segment_name ||'"' ||' rebuild online
tablespace psapgreati;' from dba_segments
where tablespace_name = 'PSAPBTABI'

3. sql login in the SQL session(only can effect this session.
alter session set sort_area_size = 10240000;

4. run the script(for more fast speed,split the script)
#>split -l 2000

5. rebuild tablespace file resize (space)
alter database datafile '/oracle/DSP/....' resize 1M;

6. use sapdba drop tablespace -> PSAPBTABI

7. use sapdba recreate same tablespace -> PSAPBTABI

8. change the script tablespace psapreorg to PSAPBTABI
and run the sql again.

9. analyze


Reorg

1. create Temp Tablespace (data will be move,so need new space,need 90% as old space)

2. column long, long row cann't be moved, it need exp
long column create by exp
select distinct a.table_name from dba_tab_columns a, dba_segments b
where a.data_type like '%LONG%'
and a.table_name = b.segment_name
and b.tablespace_name = 'PSAPBTABD'

3. move script create

select 'alter table ' ||'"' || segment_name ||'"' ||' move tablespace PSAPGREATI'
||' storage ( initial ' || initial_extent || ' next ' ||
next_extent || ' minextents ' || min_extents || ' maxextents ' ||
max_extents || ' pctincrease ' || pct_increase || ' freelists ' || freelists || ');'
from user_segments
where segment_type = 'TABLE'
and tablespace_name = 'PSAPBTABD'
/

4. run the script(for more fast speed,split the script)

5. after run the script,check the original tablespace's segment (segment will be export so the count will be 0)
long table cann't be moved (maybe happen)
drop the table (before need exp must check it)

6. tablespace drop(rebuild)

7. tablespace create(temp size)

8. move again(temp -> tablespace)

9. index online rebuild

10. long table imp

11. analyze[@more@]