Gary Han - A Nerd Who Tries To Be Cool And Failed Thus Far

When you get a database dump from a server and try to import it to one another, often u will face errors like:

 DEFAULT '1753-01-01 00:00:00', "end_date" DATE DEFAULT '1753-01-01
ORA-39083: Object type TABLE failed to create with error:
ORA-01861: literal does not match format string

This indicates that the database dump might be using a string format which is different from your server.

To prove this, login to sqlplus, and do:

select sysdate from dual;

if the result is not of:

xxxx-xx-xx xx:xx:xx (from above error)

then you will need to change this default date format upon logining.

We can do this with a trigger:

create or replace trigger dateformattrig after logon on database

begin execute immediate

'alter session set nls_date_format=''YYYY-MM-DD HH24:MI:SS''';

end dateformattrig;

remember the / after this trigger to actually execute it.