轉摘:EXCHANGE PARTITION those pesky columns
感覺有點意思,特引用:
Here is my partitioned table
SQL> desc PAR
Name Null? Type
—————————– ——– ————
X NUMBER(38)
Y NUMBER(38)
and it has a couple of partitions
SQL> select partition_name
2 from dba_tab_partitions
3 where table_name = ‘PAR’;
PARTITION_NAME
——————————
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform. an exchange partition operation.
SQL> create table EXCH as
2 select * from PAR
3 where 1=0;
Table created.
SQL> desc EXCH
Name Null? Type
—————————– ——– ————–
X NUMBER(38)
Y NUMBER(38)
So now I’m ready to go…But then this happens…
SQL> alter table PAR exchange partition P1 with table EXCH;
alter table PAR exchange partition P1 with table EXCH
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Well…that’s odd. I created the EXCH table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.
SQL> alter table PAR exchange partition P1 with table EXCH without validation;
alter table PAR exchange partition P1 with table EXCH without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Nope…still problems. So I go back and double check the columns
SQL> select column_name
2 from dba_tab_columns
3 where table_name = ‘PAR’;
COLUMN_NAME
——————————
X
Y
SQL> select column_name
2 from dba_tab_columns
3 where table_name = ‘EXCH’;
COLUMN_NAME
——————————
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my partitioned table that is no longer readily apparent.
I dropped a column. Or more accurately, because it was a partitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS
SQL> select column_name
2 from dba_tab_cols
3 where table_name = ‘PAR’;
COLUMN_NAME
——————————
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.
So am I stuck forever ? Do I have to drop the column ? Or reload the PAR table without the unused column ? All of those things don’t sound too palatable.
No. All I need do is get the columns in my template table into a similar state.
SQL> alter table EXCH add Z int;
Table altered.
SQL> alter table EXCH set unused column Z;
Table altered.
And we can try again…
SQL> alter table PAR exchange partition P1 with table EXCH;
Table altered.來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/354732/viewspace-753023/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- exchange partition(轉)
- exchange partition
- Pruning、Reference Partition、Exchange Partition
- exchange partition原理探究
- exchange partition 實驗
- exchange partition 的用法
- [Oracle] Partition table exchange Heap tableOracle
- Exchange Partition的實驗例子
- exchange partition的一些測試
- 巧妙使用exchange partition的一個案例
- Exchange partition分割槽結構的“乾坤挪移”
- 分割槽表UNUSED列後的EXCHANGE PARTITION操作
- 使用exchange partition來交換不同schema之間的表
- 記次10g exchange partition很慢的問題
- exchange partition 交換的問題ORA-14130:
- Oracle12c分割槽新特性之TRUNCATEPARTITION和EXCHANGE PARTITION級聯功能Oracle
- 儲存概述_轉摘
- CSS columnsCSS
- web service相關轉摘Web
- 轉摘_raid概論AI
- Infiniband簡介_轉摘
- New Balance thrives due to the passion and dedication of those we hire
- 分割槽表PARTITION table(轉)
- oracle drop columnsOracle
- 核心除錯神器SystemTap 轉摘除錯
- linux命令大整合_轉摘Linux
- hds儲存簡介_轉摘
- 金額大寫轉換(摘)
- partition table and partition indexIndex
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- 真愛 & 來自非洲的智慧 ( 轉摘 )
- DataAdapter & DataSet 使用小結_轉摘APT
- oracle表結構設計轉摘Oracle
- CSS3 columnsCSSS3
- MySQL COLUMNS分割槽MySql
- Indexing on Virtual ColumnsIndex