對Oracle分割槽表進行表空間遷移並處理ORA-14511問題
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 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表的欄位
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 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重新執行,全部提示成功,表空間遷移順利完成。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31394774/viewspace-2221028/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 分割槽表對應的表空間遷移案例
- Oracle分割槽表遷移Oracle
- 處理表的行遷移的問題
- ORACLE表批量遷移表空間Oracle
- partition 分割槽表移動到其他表空間
- 批量移動分割槽表到其他表空間
- 分割槽表分批遷移
- 移動 oracle object 到新的表空間(表,索引,分割槽,LOB)OracleObject索引
- 表空間遷移
- 遷移表空間
- sysaux 表空間不足問題處理UX
- oracle系統表空間過大問題處理Oracle
- 一個利用傳輸表空間和分割槽交換技術進行資料遷移的案例
- oracle 分割槽表進行shrink操作Oracle
- Oracle 不同平臺間表空間遷移Oracle
- MyISAM分割槽表遷移 && 合併
- oracle的表空間、分割槽表、以及索引的總結Oracle索引
- 傳輸表空間及問題處理
- 處理TEMP表空間滿的問題
- Oracle中表空間、表、索引的遷移Oracle索引
- 利用RMAN遷移表空間碰到的問題(五)
- 利用RMAN遷移表空間碰到的問題(四)
- 利用RMAN遷移表空間碰到的問題(三)
- 利用RMAN遷移表空間碰到的問題(二)
- 利用RMAN遷移表空間碰到的問題(一)
- 【遷移】表空間transport
- RMAN遷移表空間
- 遷移SYSTEM表空間為本地管理表空間
- mysql 進行表分割槽MySql
- 分割槽表並行建立索引並行索引
- 分割槽表truncate慢處理
- 遷移表到新的表空間
- Oracle 表空間資料檔案遷移Oracle
- 跨平臺表空間遷移(傳輸表空間)
- 刪除UNDO表空間並處理ORA-01548問題
- expdp/impdp 遷移表空間
- Oracle表移動表空間Oracle
- oracle 表移動表空間Oracle