Table and Index Movement

最新推荐文章于 2024-05-14 15:18:48 发布

原创最新推荐文章于 2024-05-14 15:18:48 发布 · 895 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #insert #delete #sql #user #query

OracleDB 专栏收录该内容

47 篇文章

订阅专栏

本文介绍如何在Oracle数据库中将表和索引从一个表空间迁移到另一个表空间的方法，包括使用ALTER TABLE和ALTER INDEX命令的具体语法，并讨论了在进行大量数据操作后的表空间迁移效果及最佳实践。

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);