How Can NLS_DATE_FORMAT Help You?

When you connect to the Oracle database, anything that has the DATE datatype, is displayed in the default format of DD-MON-YY, and no time component.
There are multiple ways to see the time component of a DATE column, in sqlplus. You can either alter your session to set the date format, or you can use a function in your select statement to alter the format of the column.

alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
select sysdate from dual;

SYSDATE
-------------------
12/01/2022 20:19:06

select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
12-JAN-22 20:19:50

How about RMAN? How can you see the date and time a backup completed or started? By default you only see the same format as in sqlplus: DD-MON-YY.
This format is not always enough. You try an alter session command in RMAN (same as above), and it doesn’t work. Instead, what is the solution?

You must set the NLS_DATE_FORMAT environment variable outside of RMAN, before you connect to RMAN, to the format of the date and time you want. Then you connect to RMAN, and voila, you have the date displayed properly!

For Unix/Linux OS you have the NLS_DATE_FORMAT variable, and for Windows, you can add the same registry entry NLS_DATE_FORMAT under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx.

$ export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'

$ rman target /

list backup of controlfile;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4867    Full    22.02M     DISK        00:00:00     12/01/2022 15:36:52
BP Key: 981   Status: AVAILABLE  Compressed: NO  Tag: TAG20220112T153654
Piece Name: /oradata/bck/HRTST/RMAN/c-2178279485-20220112-02
Control File Included: Ckp SCN: 317921665833   Ckp time: 12/01/2022 15:36:52

Look at the Completion Time column, and the Ckp time. The format of the date is the exact format you wanted.

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !