Table and Index Movement

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(<clolumn_name>) store as (tablespace <tablespacename>)" in the end of it.

eg:

alter table APPLSYS.FND_LOBS move tablespace FNDD1 initrans 10
storage(freelists 10 freelist groups 10 pctincrease 0)
lob(FILE_DATA) store  as lobsegment (tablespace FNDD1 storage( freelists 10
freelist groups 10 pctincrease 0));
alter index APPLSYS.FND_LOBS_U1 rebuild tablespace FNDX1 initrans 10
storage(freelists 10 freelist groups 10 pctincrease 0);