本文共 2570 字,大约阅读时间需要 8 分钟。
[20161023]为什么以前可以这样的表.txt
--上午看https://oracleblog.org/working-case/ora-01401-impdp-same-character/
CREATE TABLE ASS_ACCHSHT_GREEN_MEMORY
( "GREEN_ID" VARCHAR2(16) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100), "ADDED_DATE" VARCHAR2(16) DEFAULT sysdate ) ;报这个错误ORA-01401: inserted value too large for column.我关心的是为什么以前ok。
当我看到执行执行如下一下明白过来。
SCOTT@book> select length(sysdate),lengthb(sysdate) from dual;
LENGTH(SYSDATE) LENGTHB(SYSDATE)
--------------- ---------------- 19 19--我的测试环境如果 $ echo $NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
--如果我定义:
$ export NLS_DATE_FORMAT='YYYYMMDDHH24:MI:SS'SCOTT@book> select length(sysdate),lengthb(sysdate) from dual;
LENGTH(SYSDATE) LENGTHB(SYSDATE) --------------- ---------------- 16 16SCOTT@book> CREATE TABLE ASS_ACCHSHT_GREEN_MEMORY
( "GREEN_ID" VARCHAR2(16) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100), "ADDED_DATE" VARCHAR2(16) DEFAULT sysdate ) ; Table created.--可以发现这样就ok了。
SCOTT@book> @ &r/ddl scott.ASS_ACCHSHT_GREEN_MEMORY
C100 ---------------------------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."ASS_ACCHSHT_GREEN_MEMORY" ( "GREEN_ID" VARCHAR2(16) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100), "ADDED_DATE" VARCHAR2(16) DEFAULT sysdate ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ;--虽然开发建表很不规范,实际上思路很混乱,数据类型都搞错。
--正确的写法如下:SCOTT@book> drop table ASS_ACCHSHT_GREEN_MEMORY purge ;
Table dropped.--退出shell,重新登录:
$ echo $NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS
SCOTT@book> select length(sysdate),lengthb(sysdate) from dual;
LENGTH(SYSDATE) LENGTHB(SYSDATE) --------------- ---------------- 19 19SCOTT@book> CREATE TABLE ASS_ACCHSHT_GREEN_MEMORY
( "GREEN_ID" VARCHAR2(16) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100), "ADDED_DATE" VARCHAR2(16) DEFAULT to_char(sysdate,'YYYYMMDDHH24MISS') ) ; Table created.SCOTT@book> insert into ASS_ACCHSHT_GREEN_MEMORY(green_id ,account_id,added_by) values ('1','2','3');
1 row created.SCOTT@book> commit ;
Commit complete.SCOTT@book> select * from ASS_ACCHSHT_GREEN_MEMORY;
GREEN_ID ACCOUNT_ID ADDED_BY ADDED_DATE ---------------- ---------------- ---------- ---------------- 1 2 3 20161023100908 --补充正常数据类型应该选择date: CREATE TABLE ASS_ACCHSHT_GREEN_MEMORY ( "GREEN_ID" VARCHAR2(16) NOT NULL ENABLE, "ACCOUNT_ID" VARCHAR2(16) NOT NULL ENABLE, "ADDED_BY" VARCHAR2(100), "ADDED_DATE" date DEFAULT sysdate ) ;转载地址:http://wesso.baihongyu.com/