各個Oracle 版本下如何調整高水位(HWM)

個人總結整理 ， 轉載請註明出處。

以下沒有註明版本號的各版本都適用。

1.CTAS :

create table xxx_new
tablespace new_tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
as
select * from xxx
order by primary_index_key_values;

Running CTAS in parallel can dramatically speed up table reorganization

create table vbap_sorted
tablespace vbap_copy
storage (initial 500m
next 50m
maxextents unlimited
)
parallel (degree 4)
as
select *
from
sapr3.vbap
order by
mandt,
vbeln,
posnr;

Using Oracle dbms_redefinition: The dbms_redefinition package allows you to copy a table (using CTAS), create a snapshot on the table, enqueue changes during the redefinition, and then re-synchronize the restructured table with the changes that have accumulated during reorganization.

exec dbms_redefinition.abort_redef_table('PUBS','TITLES','TITLES2');

alter table titles add constraint pk_titles primary key (title_id);

exec dbms_redefinition.can_redef_table('PUBS','TITLES');

create table titles2

as

select * from titles;

exec dbms_redefinition.start_redef_table('PUBS','TITLES','TITLES2','title_id title_id,title
title,type type,pub_id pub_id,price price,advance advance,royalty*1.1 royalty,ytd_sales
ytd_sales,notes notes,pubdate pubdate');

exec dbms_redefinition.sync_interim_table('PUBS','TITLES','TITLES2');

exec dbms_redefinition.finish_redef_table('PUBS','TITLES','TITLES2');
If your reorganization fails, you must take special steps to make it re-start. Because the
redefinition requires creating a snapshot, you must call dbms_redefinition.abort_redef_table to
release the snapshot to re-start you procedure.

The ‘dbms_redefinition.abort_redef_table’ procedure which accepts 3 parameters (schema, original table name, holding table name), and which “pops the stack” and allows you to start over.

http://blog.csdn.net/shongyu/archive/2007/06/29/1671611.aspx

2.EXP/IMP

太簡單，這個就不用說了，哪個版本都適用。

3.TABLE MOVE(9I)

alter table table_name move ...

aleter table move 只是給表中所有資料搬到新的儲存空間上,就相當於把A房間內亂七八糟的東西整理起來放到B房間裡面然後再把A房間讓出來給別人,這樣你不但讓出了整個A房間而且仍然只佔用一個房間,但是你的東西(資料)也整齊了可能只佔用B房間很少的一部分
也在同一表空間move——效果很好必須重建索引 ， 可以直接在本tbs上．　eg: alter table table_name move; 需要對index rebuild ;

4.SHRINK SPACE(10G)

alter table enable row movement;
alter table shrink space;