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.

4.FQA:

Q:ORA-14133:

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博客

Table and Index Movement

最新文章