目录
最近做数据库上线脚本的评审时,开发人员遇到了ORA-14758错误,本篇就针对这一错误进行分析,并给出解决方案。
1 软件环境
本实验的演示环境如下:
SQL> SELECT * from product_component_version;
PRODUCT VERSION STATUS
---------------------------------------- ---------- --------------------
NLSRTL 11.2.0.4.0 Production
Oracle Database 11g Enterprise Edition 11.2.0.4.0 64bit Production
PL/SQL 11.2.0.4.0 Production
TNS for Linux: 11.2.0.4.0 Production
2 问题模拟
按照如下步骤,创建数据库脚本,模拟ORA-14758错误。
2.1 新建分区表
SQL> create table t_interval
2 (
3 id number,
4 name varchar2(20),
5 cdate date default sysdate
6 )
7 partition by range(cdate)interval(numtodsinterval(1,'day'))
8 (
9 partition p1 values less than(to_date('2019-11-19','yyyy-mm-dd'))
10 );
Table created.
2.2 初始化数据
SQL> begin
2 for i in 1..10 loop
3 insert into t_interval values(i,'Test'||i,sysdate-2+i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
2.3 查看分区信息
SQL> col table_name for a30
SQL> select table_name,partition_name,partition_position from user_tab_partitions
2 where table_name='T_INTERVAL';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
T_INTERVAL P1 1
T_INTERVAL SYS_P41 2
T_INTERVAL SYS_P42 3
T_INTERVAL SYS_P43 4
T_INTERVAL SYS_P44 5
T_INTERVAL SYS_P45 6
T_INTERVAL SYS_P46 7
T_INTERVAL S