[20161023]为什么以前可以这样的表.txt

[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               16

SCOTT@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               19

SCOTT@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
) ;