说明:
如果被设置为UNUSED的列的列名未知的话(比如开发人员已经离职,没有很好的项目管理导致原始表结构丢失,等等。。。),那么这时不能执行exchange partition。
04:57:52 SQL>
04:58:47 SQL> create table lunar_test
05:01:05 2 (
05:01:05 3 OBJECT_ID NUMBER,
05:01:05 4 OWNER VARCHAR2(30),
05:01:05 5 SUBOBJECT_NAME VARCHAR2(30),
05:01:05 6 OBJECT_NAME VARCHAR2(128),
05:01:05 7 STATUS VARCHAR2(7),
05:01:05 8 TIMESTAMP VARCHAR2(19),
05:01:05 9 CREATED DATE,
05:01:05 10 GENERATED VARCHAR2(1),
05:01:05 11 OBJECT_TYPE VARCHAR2(19)
05:01:05 12 )
05:01:05 13 partition by range (OBJECT_ID)
05:01:05 14 (
05:01:05 15 partition PART_1 values less than ('10000'),
05:01:05 16 partition PART_2 values less than ('20000'),
05:01:05 17 partition PART_3 values less than ('30000'),
05:01:05 18 partition PART_4 values less than ('40000'),
05:01:05 19 partition PART_5 values less than (MAXVALUE)
05:01:05 20 );
Table created.
Elapsed: 00:00:00.17
05:01:06 SQL> insert into lunar_test(object_id,owner,subobject_name,object_name,status,timestamp,created,object_type)
05:01:12 2 select object_id,owner,subobject_name,object_name,status,timestamp,created,object_type from dba_objects
05:01:12 3 nologging;
commit;
11467 rows created.
Elapsed: 00:00:00.52
05:01:13 SQL> 05:01:13 SQL>
Commit complete.
Elapsed: 00:00:00.00
05:01:14 SQL> alter table lunar_test set unused column created;
alter table lunar_test set unused column object_type;
Table altered.
Elapsed: 00:00:00.07
05:01:20 SQL>
Table altered.
Elapsed: 00:00:00.05
05:01:22 SQL> select count(*) from lunar_test;
COUNT(*)
----------
11467
Elapsed: 00:00:00.02
05:01:48 SQL> alter table lunar_test set unused column created;
alter table lunar_test set unused column created
*
ERROR at line 1:
ORA-00904: "CREATED": invalid identifier
Elapsed: 00:00:00.00
05:03:06 SQL> alter table lunar_test set unused column object_type;
alter table lunar_test set unused column object_type
*
ERROR at line 1:
ORA-00904: "OBJECT_TYPE": invalid identifier
Elapsed: 00:00:00.01
05:03:06 SQL> alter table lunar_test set unused column created;
alter table lunar_test set unused column created
*
ERROR at line 1:
ORA-00904: "CREATED": invalid identifier
Elapsed: 00:00:00.00
05:03:28 SQL> desc lunar_test
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OWNER VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
STATUS VARCHAR2(7)
TIMESTAMP VARCHAR2(19)
GENERATED VARCHAR2(1)
05:03:44 SQL> show user
USER is "LUNAR"
05:03:59 SQL>
05:04:54 SQL> create table lunar_test2
05:05:08 2 (
05:05:08 3 AAA NUMBER,
05:05:08 4 BBB VARCHAR2(30),
05:05:08 5 SUBOBJECT_NAME VARCHAR2(30),
05:05:08 6 OBJECT_NAME VARCHAR2(128),
05:05:08 7 STATUS VARCHAR2(7),
05:05:08 8 TIMESTAMP VARCHAR2(19),
05:05:08 9 CREATED DATE,
05:05:08 10 GENERATED VARCHAR2(1),
05:05:08 11 OBJECT_TYPE VARCHAR2(19)
05:05:08 12 )
05:05:08 13 partition by range (OBJECT_ID)
05:05:08 14 (
05:05:08 15 partition PART_1 values less than ('10000'),
05:05:08 16 partition PART_2 values less than ('20000'),
05:05:08 17 partition PART_3 values less than ('30000'),
05:05:08 18 partition PART_4 values less than ('40000'),
05:05:08 19 partition PART_5 values less than (MAXVALUE)
05:05:08 20 );
partition by range (OBJECT_ID)
*
ERROR at line 13:
ORA-14030: non-existent partitioning column in CREATE TABLE statement
Elapsed: 00:00:00.02
05:05:10 SQL> create table lunar_test2
05:05:34 2 (
05:05:34 3 AAA NUMBER,
05:05:35 4 BBB VARCHAR2(30),
05:05:35 5 SUBOBJECT_NAME VARCHAR2(30),
05:05:35 6 OBJECT_NAME VARCHAR2(128),
05:05:35 7 STATUS VARCHAR2(7),
05:05:35 8 TIMESTAMP VARCHAR2(19),
05:05:35 9 CREATED DATE,
05:05:35 10 GENERATED VARCHAR2(1),
05:05:35 11 OBJECT_TYPE VARCHAR2(19)
05:05:35 12 )
05:05:35 13 partition by range (AAA)
05:05:35 14 (
05:05:35 15 partition PART_1 values less than ('10000'),
05:05:35 16 partition PART_2 values less than ('20000'),
05:05:35 17 partition PART_3 values less than ('30000'),
05:05:35 18 partition PART_4 values less than ('40000'),
05:05:35 19 partition PART_5 values less than (MAXVALUE)
05:05:35 20 );
Table created.
Elapsed: 00:00:00.34
05:05:36 SQL> create table lunar_test_part_2 as select * from lunar_test2 partition(PART_2);
Table created.
Elapsed: 00:00:00.17
05:05:53 SQL> alter table lunar_test_part_2 set unused column aaa;
alter table lunar_test_part_2 set unused column bbb;
Table altered.
Elapsed: 00:00:00.24
05:06:08 SQL>
Table altered.
Elapsed: 00:00:00.15
05:06:08 SQL> alter table lunar_test exchange partition PART_2 with table lunar_test_part_2;
alter table lunar_test exchange partition PART_2 with table lunar_test_part_2
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Elapsed: 00:00:00.02
05:06:13 SQL>
但是,我猜应该还是可以从某个地方(数据字典或者某个地方)知道这一列的名字,但是目前还未能测试出来。。。