首页 > Linux操作系统 > Linux操作系统 > Table and Index Movement

Table and Index Movement

原创 Linux操作系统 作者:xin2v 时间:2009-03-19 14:23:49 0 删除 编辑

1.How to move an table or index from one tablespace to another tablespace.

alter index <index_name> rebuild tablespace <tablespace_name>;
alter table <table_name> move tablespace <tablespace_name>;
alter table <table_name> move tablespace <tablespace_name> storage (....);   
alter table <table_name> move tablespace <tablespace_name> lob(lob1,lob2) store as(tablesapce tbs_name); 

2.Can we give a movement action in one tablespace?

Yes,I has been tested it in ora8174 and ora9205, results are successful.detail as below:

create table aa as select * from dual-->size 0.06M
insert into aa select * from aa   -->when recordcount>1,000,000,size=4M
delete from aa; commit;--> size=4M
select * from user_users -->get the default tablespace,name is "users"
alter table aa move tablespace users-->size=0.06M

someone will ask me "Mr. Compard,how about result of following?": 

insert into aa select * from aa   -->when recordcount>1,000,000,size=4M
delete from aa;--> don't commit 
select * from user_users -->get the default tablespace,name is "users"
alter table aa move tablespace users-->size=?

 I will not show the answer directly, but tell him SQL "alter" is belong to DLL, what do you think about it?

3.Best use of Move action.

I think the best use is falling back the High-water-mark instead of "backup data/truncate/insert" way.Of course falling back the High-water-mark can improve the performance.



A: Cause : sql sentenses are wrong. I lost the key words "tablespace" and the error is occured.

Q:ORA-01502:state unusable

A:Today ,after moved 3 big tables(size >1G),I get the error ORA-01502 when query them.No way but rebuild the indexes to resolve this error.It spend me more time to do it. So I think maybe exp/imp is better.

Q:How to move LOB index

A:It's can't be move directly,but you can use move it by SQL for moving table,add" LOB() store as (tablespace )" in the end of it.

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量