如何对Oracle分区表进行表空间迁移并处理

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

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

select 'alter table CAMS_CORE.'|| TABLE_NAME || ' move tablespace cams_core_tab;' from dba_tables where owner='CAMS_CORE';

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

alter table CAMS_CORE.BP_VOUCHER_RECENT move tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move tablespace cams_core_tab;

提示错误

ORA-14511: cannot perform operation on a partitioned object

3.使用oerr查看错误信息

[oracle@XLJ181 dump]$ oerr ORA 1451114511, 00000, "cannot perform operation on a partitioned object"// *Cause: An attempt was made to perform an operation that is not allowed//         on partitioned tables or indexes.// *Action: Retry the command with correct syntax.

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

SYS@cams> desc dba_tab_partitions Name        Null?    Type ----------------------------------------- -------- ---------------------------- TABLE_OWNER         VARCHAR2(30) TABLE_NAME         VARCHAR2(30) COMPOSITE         VARCHAR2(3) PARTITION_NAME         VARCHAR2(30) SUBPARTITION_COUNT        NUMBER HIGH_VALUE         LONG HIGH_VALUE_LENGTH        NUMBER PARTITION_POSITION        NUMBER TABLESPACE_NAME        VARCHAR2(30) PCT_FREE         NUMBER PCT_USED         NUMBER INI_TRANS         NUMBER MAX_TRANS         NUMBER INITIAL_EXTENT         NUMBER NEXT_EXTENT         NUMBER MIN_EXTENT         NUMBER MAX_EXTENT         NUMBER MAX_SIZE         NUMBER PCT_INCREASE         NUMBER FREELISTS         NUMBER FREELIST_GROUPS        NUMBER LOGGING         VARCHAR2(7) COMPRESSION         VARCHAR2(8) COMPRESS_FOR         VARCHAR2(12) NUM_ROWS         NUMBER BLOCKS          NUMBER EMPTY_BLOCKS         NUMBER AVG_SPACE         NUMBER CHAIN_CNT         NUMBER AVG_ROW_LEN         NUMBER SAMPLE_SIZE         NUMBER LAST_ANALYZED         DATE BUFFER_POOL         VARCHAR2(7) FLASH_CACHE         VARCHAR2(7) CELL_FLASH_CACHE        VARCHAR2(7) GLOBAL_STATS         VARCHAR2(3) USER_STATS         VARCHAR2(3) IS_NESTED         VARCHAR2(3) PARENT_TABLE_PARTITION        VARCHAR2(30) INTERVAL         VARCHAR2(3) SEGMENT_CREATED        VARCHAR2(4)

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

SYS@cams> set pages 1000SYS@cams> set lines 200SYS@cams> select 'alter table ' ||table_owner|| '.' || table_name || ' move partition ' || partition_name || ' tablespace cams_core_tab;' as move_sql  from dba_tab_partitions where table_owner='CAMS_CORE' and table_name in ('BP_VOUCHER_RECENT','BP_VOUCHER_HISTORY'); MOVE_SQL-------------------------------------------------------------------------------------------------------------------------------------------------alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition P0 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition P1 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition P2 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P118 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P119 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P120 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P121 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P122 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P123 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P124 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P125 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P126 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P127 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P128 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P129 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P130 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P131 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P132 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P133 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_HISTORY move partition SYS_P134 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P0 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P1 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P2 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P3 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P4 tablespace cams_core_tab;alter table CAMS_CORE.BP_VOUCHER_RECENT move partition P5 tablespace cams_core_tab; 26 rows selected.

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

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