这篇文章主要讲解了“如何对Oracle分区表进行表空间迁移并处理”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“如何对Oracle分区表进行表空间迁移并处理”吧！

1.因为工作需要，需要将CAMS_CORE用户下的表做一次表空间迁移，生成迁移命令脚本如下：

select'altertableCAMS_CORE.'||TABLE_NAME||'movetablespacecams_core_tab;'fromdba_tableswhereowner='CAMS_CORE';

2.将生成的语句进行迁移，其中有2个表为Interval Partition分区表，迁移时遇到了问题：

altertableCAMS_CORE.BP_VOUCHER_RECENTmovetablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovetablespacecams_core_tab;

提示错误

ORA-14511:cannotperformoperationonapartitionedobject

3.使用oerr查看错误信息

[oracle@XLJ181dump]$oerrORA1451114511,00000,"cannotperformoperationonapartitionedobject"//*Cause:Anattemptwasmadetoperformanoperationthatisnotallowed//onpartitionedtablesorindexes.//*Action:Retrythecommandwithcorrectsyntax.

4.从错误提示上看，应该是分区表的迁移不能基于表迁移，需要基于分区进行迁移，特此改进操作，先查看dba_tab_partitions表的字段

SYS@cams>descdba_tab_partitionsNameNull?Type-----------------------------------------------------------------------------TABLE_OWNERVARCHAR2(30)TABLE_NAMEVARCHAR2(30)COMPOSITEVARCHAR2(3)PARTITION_NAMEVARCHAR2(30)SUBPARTITION_COUNTNUMBERHIGH_VALUELONGHIGH_VALUE_LENGTHNUMBERPARTITION_POSITIONNUMBERTABLESPACE_NAMEVARCHAR2(30)PCT_FREENUMBERPCT_USEDNUMBERINI_TRANSNUMBERMAX_TRANSNUMBERINITIAL_EXTENTNUMBERNEXT_EXTENTNUMBERMIN_EXTENTNUMBERMAX_EXTENTNUMBERMAX_SIZENUMBERPCT_INCREASENUMBERFREELISTSNUMBERFREELIST_GROUPSNUMBERLOGGINGVARCHAR2(7)COMPRESSIONVARCHAR2(8)COMPRESS_FORVARCHAR2(12)NUM_ROWSNUMBERBLOCKSNUMBEREMPTY_BLOCKSNUMBERAVG_SPACENUMBERCHAIN_CNTNUMBERAVG_ROW_LENNUMBERSAMPLE_SIZENUMBERLAST_ANALYZEDDATEBUFFER_POOLVARCHAR2(7)FLASH_CACHEVARCHAR2(7)CELL_FLASH_CACHEVARCHAR2(7)GLOBAL_STATSVARCHAR2(3)USER_STATSVARCHAR2(3)IS_NESTEDVARCHAR2(3)PARENT_TABLE_PARTITIONVARCHAR2(30)INTERVALVARCHAR2(3)SEGMENT_CREATEDVARCHAR2(4)

5.拼写自动生成迁移语句的sql

SYS@cams>setpages1000SYS@cams>setlines200SYS@cams>select'altertable'||table_owner||'.'||table_name||'movepartition'||partition_name||'tablespacecams_core_tab;'asmove_sqlfromdba_tab_partitionswheretable_owner='CAMS_CORE'andtable_namein('BP_VOUCHER_RECENT','BP_VOUCHER_HISTORY');MOVE_SQL-------------------------------------------------------------------------------------------------------------------------------------------------altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionP0tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionP1tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionP2tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P118tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P119tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P120tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P121tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P122tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P123tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P124tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P125tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P126tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P127tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P128tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P129tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P130tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P131tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P132tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P133tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_HISTORYmovepartitionSYS_P134tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_RECENTmovepartitionP0tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_RECENTmovepartitionP1tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_RECENTmovepartitionP2tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_RECENTmovepartitionP3tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_RECENTmovepartitionP4tablespacecams_core_tab;altertableCAMS_CORE.BP_VOUCHER_RECENTmovepartitionP5tablespacecams_core_tab;26rowsselected.

6.将生成的sql重新执行，全部提示成功，表空间迁移顺利完成。

感谢各位的阅读，以上就是“如何对Oracle分区表进行表空间迁移并处理”的内容了，经过本文的学习后，相信大家对如何对Oracle分区表进行表空间迁移并处理这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！