Changing the DEFAULT DATE FORMAT in Oracle Database

Changing the DEFAULT DATE FORMAT in Oracle Database.

Changing NLS_DATE_FORMAT parameter to specific value in session/system will be overlooked by the NLS_LANG parameter, so to have the NLS_DATE_FORMAT permanently changed in the DB, we need to create logon trigger as below.

create or replace trigger CHANGE_DATE_FORMAT
after logon
ON DATABASE
begin
   execute immediate
  'alter session set nls_date_format = ''DD/MM/YYYY'' ';
end;
/