ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 各个Oracle 版本下如何调整高水位(HWM)

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

原创 Linux操作系统 作者:tolywang 时间:2007-08-02 00:00:00 0 删除 编辑

个人总结整理 , 转载请注明出处。

以下没有注明版本号的各版本都适用。

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84837/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 省油与安全行车
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13306827