因为分区表监控的脚本不想频繁访问dba_tab_subpartitions视图,需要创建临时表T1,偶然间发现oracle 10g的一个bug,在metalink上也没能找到该问题的解决方案。
Table created.
no rows selected
insert into t1 select table_name,partition_name,subpartition_name,to_lob(high_value) HIGH_VALUE from dba_tab_subpartitions
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
1.创建临时表T1:
SQL> create table T1 as select table_name,partition_name,subpartition_name,to_lob(high_value) HIGH_VALUE from dba_tab_subpartitions;Table created.
2.查询有没有数据:
SQL> select * from t1 where rownum < 3;no rows selected
3.显式插入数据时报错 ORA-00932:
SQL> insert into t1 select table_name,partition_name,subpartition_name,to_lob(high_value) HIGH_VALUE from dba_tab_subpartitions;insert into t1 select table_name,partition_name,subpartition_name,to_lob(high_value) HIGH_VALUE from dba_tab_subpartitions
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG
NOTE:这是一个非常常见的long类型处理错误,官方说法:通过to_lob把long类型转换为clob可以解决这个报错。那么就看一下是不是呢?
SQL> desc t1Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)