ORA-14511: cannot perform operation on a partitioned object

2020-07-28

分区表move到新的表空间。
alter table T1 move tablespace TS2;
 
1、ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

2、表空间重命名
alter tabspace ts_name1 rename to ts_name2;

3、实例:
#!/bin/sh
nm=$(date +%Y%m -d 1month)

ts_name=test_${nm}

sqlplus -s system/121point << EOF
set hea off
set linesize 200
col SEGMENT_NAME format a30
col INDEX_NAME format a30
col TABLE_NAME format a30;
--select TABLE_NAME,PARTITION_COUNT,DEF_TABLESPACE_NAME from USER_PART_TABLES where TABLE_NAME like '%$ym' order by DEF_TABLESPACE_NAME;
--移动分区
begin
for x in(select partition_name from dba_tab_partitions where table_name='${tname}')
loop
execute immediate 'alter table $tname move partition ' || x.partition_name || ' tablespace ${ts_name}';
end loop;
end;
/
---------移动索引
begin
for x in(select distinct INDEX_NAME from user_indexes where TABLE_NAME='${tname}')
loop
execute immediate 'alter index '|| x.INDEX_NAME || ' rebuild tablespace ${ts_name}';
end loop;
end;
/
alter table $tname modify default attributes tablespace $ts_name;
select distinct SEGMENT_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='$ts_name' order by SEGMENT_NAME;
quit;
EOF
~

分类:数据库 | 标签: |

相关日志

评论被关闭!