Table and Index Movement

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

木划离

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

阅读量880

点赞数

分类专栏： OracleDB 文章标签： table insert delete sql user query

本文链接：https://blog.csdn.net/compard/article/details/1906930

版权

OracleDB 专栏收录该内容

47 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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