Drop unused columns--2

说明：
如果被设置为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>

 

但是，我猜应该还是可以从某个地方（数据字典或者某个地方）知道这一列的名字，但是目前还未能测试出来。。。