ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库培训-备份恢复-性能优化-集群容灾
Oracle数据库DBA高级工程师培训视频

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

内容发布:liubeiv4| 发布时间:2018-11-23 20:34:57

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

1

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


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

1

2

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

提示错误

1

ORA-14511: cannot perform operation on a partitioned object


3.使用oerr查看错误信息

1

2

3

4

5

[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.


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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

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.


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




上一篇:11g RAC不能启动ohasd进程
下一篇:删除UNDO表空间并处理ORA-01548问题
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表