各个Oracle 版本下如何调整高水位(HWM)

各个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;