对Oracle分区表进行表空间迁移并处理ORA-14511问题

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:109

[p=28, null, left][font=-apple-system, "]1.因为工作需要,需要将CAMS_CORE用户下的表做一次表空间迁移,生成迁移命令脚本如下:
[table=98%,none]
[tr=none ][align=right][align=right]1
[align=right]select 'alter table CAMS_CORE.'|| TABLE_NAME || ' move tablespace cams_core_tab;' from dba_tables where owner='CAMS_CORE';

[p=28, null, left][font=-apple-system, "]2.将生成的语句进行迁移,其中有2个表为Interval Partition分区表,迁移时遇到了问题:
[table=98%,none]
[tr=none ][align=right][align=right]1
[align=right]2
[align=right]alter table CAMS_CORE.BP_VOUCHER_RECENT move tablespace cams_core_tab;
alter table CAMS_CORE.BP_VOUCHER_HISTORY move tablespace cams_core_tab;

[p=28, null, left][font=-apple-system, "]提示错误[table=98%,none]
[tr=none ][align=right][align=right]1
[align=right]ORA-14511: cannot perform operation on a partitioned object

[p=28, null, left][font=-apple-system, "]3.使用oerr查看错误信息[table=98%,none]
[tr=none ][align=right][align=right]1
[align=right]2
[align=right]3
[align=right]4
[align=right]5
[align=right][oracle@XLJ181 dump]$ oerr ORA 14511
14511, 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.

[p=28, null, left][font=-apple-system, "]4.从错误提示上看,应该是分区表的迁移不能基于表迁移,需要基于分区进行迁移,特此改进操作,先查看dba_tab_partitions表的字段[table=98%,none]
[tr=none ][align=right][align=right]1
[align=right]2
[align=right]3
[align=right]4
[align=right]5
[align=right]6
[align=right]7
[align=right]8
[align=right]9
[align=right]10
[align=right]11
[align=right]12
[align=right]13
[align=right]14
[align=right]15
[align=right]16
[align=right]17
[align=right]18
[align=right]19
[align=right]20
[align=right]21
[align=right]22
[align=right]23
[align=right]24
[align=right]25
[align=right]26
[align=right]27
[align=right]28
[align=right]29
[align=right]30
[align=right]31
[align=right]32
[align=right]33
[align=right]34
[align=right]35
[align=right]36
[align=right]37
[align=right]38
[align=right]39
[align=right]40
[align=right]41
[align=right]42
[align=right]43
[align=right]44
[align=right]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)

[p=28, null, left][font=-apple-system, "]5.拼写自动生成迁移语句的sql[table=98%,none]
[tr=none ][align=right][align=right]1
[align=right]2
[align=right]3
[align=right]4
[align=right]5
[align=right]6
[align=right]7
[align=right]8
[align=right]9
[align=right]10
[align=right]11
[align=right]12
[align=right]13
[align=right]14
[align=right]15
[align=right]16
[align=right]17
[align=right]18
[align=right]19
[align=right]20
[align=right]21
[align=right]22
[align=right]23
[align=right]24
[align=right]25
[align=right]26
[align=right]27
[align=right]28
[align=right]29
[align=right]30
[align=right]31
[align=right]32
[align=right]33
[align=right]34
[align=right]SYS@cams> set pages 1000
SYS@cams> set lines 200
SYS@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.

[p=28, null, left][font=-apple-system, "]6.将生成的sql重新执行,全部提示成功,表空间迁移顺利完成。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】