ORA-01861: literal does not match format string

Get following error when importing a schema to new database,

—————————–
orA-39083: Object type TABLE failed to create with error:
orA-01861: literal does not match format string
Failing sql is:
Create TABLE “BLOG”.”MT_AUTHOR” (“AUTHOR_ID” NUMBER(*,0) NOT NULL ENABLE, “AUTHO
R_API_PASSWORD” NVARCHAR2(60), “AUTHOR_CAN_Create_BLOG” NUMBER(1,0), “AUTHOR_CAN
_VIEW_LOG” NUMBER(1,0), “AUTHOR_EMAIL” NVARCHAR2(75), “AUTHOR_ENTRY_PREFS” NVARC
HAR2(255), “AUTHOR_EXTERNAL_ID” NVARCHAR2(255), “AUTHOR_HINT” NVARCHAR2(75), “AU
THOR_IS_SUPERUSER” NUMBER(1,0), “AUTHOR_NAME” NVARCHAR2(255) NOT NUL
—————————–
there is a column in this database defined like this,

“LOG_MODIFIED_ON” DATE DEFAULT ‘20000101000000’ NOT NULL ENABLE,  

and oracle thinks ‘20000101000000’  is not formated right, so it gives ora-01861 error.

Here is my troubleshooting steps,
1. Alter SESSION SET NLS_DATE_FORMAT=’YYYYMMDDHH24MISS’ ;
this doesn’t help since even though i set this in my sqlplus session, it will create another session when importing data.

2. Alter SYSTEM SET NLS_DATE_FORMAT=’YYYYMMDDHH24MISS’ SCOPE=SPFILE ;
this doesn’t help either, since when login database and query the sysdate, the format is not like ‘YYYYMMDDHH24MISS’ , it likes nothing changed and the result is 19-Oct-2010.

3. Add NLS_DATE_FORMAT in register,
cmd ==> regedit ==> HKEY_LOCAL_MACHINE ==> SOFEWARE ==> orACLE ==> orAHOME1 (here is the oracle home name) ==> modify the value of NLS_DATE_FORMAT to YYYYMMDDHH24MISS if it’s already there, if not, add a string value and set the name to NLS_DATE_FORMAT and value to YYYYMMDDHH24MISS .
then import again, it works perfect !!!

4. don’t forget to remove this string / modify the string back once the import is done, since this change may impact other database or actions.

so we can tell that database format is derived from nls_date_format in register, if nls_date_format is not setup, it will determined by  NLS_TERRITORY.

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>