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.
