Drop unused columns--2

最新推荐文章于 2021-12-11 17:02:59 发布

lunar2000

最新推荐文章于 2021-12-11 17:02:59 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： object table less exchange sql date

本文链接：https://blog.youkuaiyun.com/lunar2000/article/details/7910447

oracle 专栏收录该内容

137 篇文章

订阅专栏

本文探讨了在Oracle数据库中，当列被设置为UNUSED状态且列名未知时，如何解决无法执行exchangepartition操作的问题。通过创建表、插入数据、设置列为UNUSED状态以及尝试交换分区等步骤，展示了遇到的错误和解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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