转摘：EXCHANGE PARTITION those pesky columns

最新推荐文章于 2024-03-18 10:28:37 发布

转载最新推荐文章于 2024-03-18 10:28:37 发布 · 242 阅读

文章标签：

本文探讨了在Oracle数据库中遇到的一个问题：如何正确地创建模板表并使用它进行分区表交换操作。通过实例分析，文章揭示了在创建模板表时未考虑到历史修改的隐含问题，并提供了解决方案，即确保模板表与目标分区表在结构上完全一致，包括隐含删除的列。最终，通过增加缺失的列并设置为无效状态，成功实现了分区表的交换。

参阅http://www.oaktable.net/content/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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-753023/