Subtraction DATE from DATE in Oracle database - implementation feature or what ?

Recently I used lag function to find interval of creating new objects of the same type in the database. Particularly I was interested by the time interval between such creations. The original query was

SELECT
    file#
    ,creation_time
    , (creation_time - lag (creation_time) over (order by creation_time)) as interval
FROM
    v$datafile a
    , v$tablespace b
WHERE
    a.ts# = b.ts#
    AND b.name = 'XXX'
ORDER BY
    2 desc
/

It turned out that it is possible to use interval expression during subtraction :

SELECT
    file#
    ,creation_time
    , (creation_time - lag (creation_time) over (order by creation_time)) day (3) to second as interval
FROM
    v$datafile a
    , v$tablespace b
WHERE
    a.ts# = b.ts#
    AND b.name = 'BARS_739_ADD'
ORDER BY
    2 desc
/

The result is more presentable than in previous query. 

The Oracle SQL Language documentation says than the NUMBER datatype will be generated as result of "DATE - DATE" operation. With help dump function, I saw some mysterious (internal ?) datatype 14, not 2 (number datatype). You could also use such "feature" when subtracting DATE datatype values and if trunc or round functions are not of your suitable option.

Good Luck !

How to allow row to break across lines in Oracle iLOM host console

When you used to access to host console via iLOM, you probably noticed that long command lines (over 80 symbols) do not break to new continuation line, the new characters just go over existed, already typed characters. It looks like the terminal string

[root@xxx-xxxx-xxx ~]# ddddddddddddddddddddddddddddddddddddddddddddddddddddd

transforms into

aaaat@xxx-xxx-xxx ~]# dddddddddddddddddddddddddddddddddddddddddddddddddddddaaa 

To resolve the issue, set tty parameters corresponding to your terminal, for example :

[root@xxx-xxxx-xxx ~]# stty rows 45 columns 158

You should also reopen iLOM console for proper terminal functioning.

Good Luck !

Some features of implementation when using TNSNAMES connection descriptors

 1. You can double parameters in row, Oracle will you only last value. For example, when you use

             (ADDRESS=
                (PROTOCOL=tcp)
                (HOST=localhost)
                (port=1520)
                (PORT=1521)
              )

, the port 1521 which is the last in the group of values of the same group (address), and that one will be used in the connection (1520 will not be tried at all). This behavior is expected though, remember several addresses in the address_list group, but in case of address_list the client tries to establish the connection with every address until succeed, and in our case the client simply goes via the last parameter in the list.

2. You can use some abstract (not existed and not considered by oracle) parameters and values for debugging or for other purposes, for example,

            (CONNECT_DATA=
                (service_name = pdb1.db-01.site)
                (instance_name = cdb121)
                (instance_name = cdb_manual)
                (aaa = bbb)
            )

Here abstract parameter aaa is used. The listener entry in this case could be like this :

22-NOV-2020 09:22:31 * (CONNECT_DATA=(service_name=pdb1.db-01.site)(instance_name=cdb121)(instance_name=cdb_manual)(global_name=cdb121aaa)(aaa=bbb)(CID=(PROGRAM=sqlplus@db-01)(HOST=db-01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=21799)) * establish * pdb1.db-01.site * 0

As we can see sqlplus established connection with instance_name=cdb_manual (the last of the group of instance_name parameters inside of tns descriptor, you can confirm it with help of select sys_context from dual statement)

3. Will be continued...

Good Luck !