sqlplus -p db01
Local Net Naming configuration file: /usr/lib/oracle/23/client64/lib/network/admin/tnsnames.ora
Attempting to contact: (description=(address_list=(address=(protocol=TCP)(host=srv01.example.com)(port=1521)))(connect_data=(service_name=db01.example.com)))
Ok (1.143 msec)

Remark: I first posted ping from within sqlplus, but -p is obviously nice. Thanks Beat Ramseier | LinkedIn   via Post | LinkedIn

How to enforce this in a recent Oracle version?

create assertion x25 check (not exists(select * from emp e where sal*25<any(select sal from emp)));
update emp set sal=100000 where job='PRESIDENT';
ORA-08601: SQL assertion (SCOTT.X25) violated.

mycmd | grep mytext

the return code of command got lost, $? point to the return code of grep.  Back in time I posted https://laurentschneider.com/wordpress/2009/05/return-code-before-grep.html

( 
  ( 
    (
      mycmd
      echo $? >&3
    ) |grep mytext >&4 
  ) 3>&1 |(read x;exit $x) 
)4>&1

this did work like a charm for decades.

But earlier this year I found this sexy syntax

mycmd > >(grep mytext)

While it may abuse your memory, especially on legacy/ancient operating systems, it is more understandable. A little bit. Maybe. YMMV.

Last week I discovered also, while ansiblelinting my code

set -o pipefail
mycmd | grep mytext

It looks better but it is a disgusting hack. I tested a bit and very quickly decided to add

skip_list:
- 'risky-shell-pipe'

in my .ansible-lint file.

pipefail has an annoying handling of SIGPIPE.

# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
141
# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
0
# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
0
# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
0

Random absolutely unusable stuff… avoid!

SIGPIPE is documented as :

On a last note, pipes must have a reader and a writer. If a process tries to write to a pipe that has no reader, it will be sent the SIGPIPE signal from the kernel.

so when head stops writing, grep may no longer read and the pipe may get a death signal. But it is not consistent.

One is to use Oracle Native Encryption by setting

SQLNET.ENCRYPTION_SERVER=requested

on the server sqlnet.ora and that’s about it. Very easy to setup and nothing to do on the client. Also it was a good choice for performance and security.

The other option is to setup SSL/TLS on the listener by setting

PROTOCOL=TCPS

On both the server listener.ora and the client tnsnames.ora. It is more complex and support authentication, server checks and multiple features.

I’ve write about this here:

listener with tcps

There has been a lot of hassle with it. Back in the time, when it was part of Oracle Networking Option or later called Advanced Security Option, it was too expensive for most customers, and pretty unused. There were some nasty security issue with REMOTE_LISTENER and RAC in 9i/10g so Oracle decided to waived that requirement around 2011 if I recall correctly.

But nothing was ready, not even Oracle Enterprise Manager. Old jdbc Drivers where just hanging, firewalls were confused, sqldeveloper was hanging when selecting clobs, Oracle Support wasn’t helpful, really, I did not like it.

In 23ai Oracle updated his documentation and now recommends TLS over NNE in Security Guide – 19. Securing data for Oracle connections.

Per default, SSL in Oracle 23ai now supports TLS1.3, which is Post Quantum Cryptography (PQC) ready. If you care about security, TLS adoption for your Oracle listener is recommended

If you know, you know

$ sqlite3 test.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> create table t(x number);
sqlite> insert into t values(1);
sqlite> select * from t;
1
sqlite>
$ du -h test.db
8.0K test.db

I just created a database, a table and a row, issued one select. Used 8K on my home directory.

[satire on]What else do you expect from a database?[satire off]

if [ $x ]; then ...

The author told me, I was a junior java developer, it checks if x exists.

I didn’t like the code. I just found out that “1 = 2” does not exist…

$ x='1 = 2'; if [ $x ]; then echo ok; else echo notok; fi
notok
$ x='foo'; if [ $x ]; then echo ok; else echo notok; fi
ok

This is also documented in some less-ancient manpages

test "$1"
test ! "$1"
could not be used reliably on some historical systems. Unexpected results would occur if such a string condition were used and $1 expanded to !, (, or a known unary primary. Better constructs are, respectively,

test -n "$1"
test -z "$1"

Never trust your senior administrator, just test test test …

And even if you test but the code is not robust, don’t do it, it may break one day

Many users still use the old non case sensitive passwords. Resetting User Passwords That Use the 10G Password Version is a post-upgrade task of 19c.

Let’s try with the identified by values (as I wrote long time ago there alter-user-identified-by-values-in-11g )

SQL> create user u identified by values '18FE58AECB6217DB' /* password U */;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> select PASSWORD_VERSIONS from dba_users where username='U';
PASSWORD_VERSIONS
-----------------
10G
SQL> conn u/U
Connected.
SQL> alter user u identified by ************ replace U;
alter user u identified by ************ replace U
*
ERROR at line 1:
ORA-28008: invalid old password
SQL> password
Changing password for U
Old password: u
New password: ************
Retype new password: ************
Password changed
SQL> conn / as sysdba
Connected.
SQL> select PASSWORD_VERSIONS from dba_users where username='U';
PASSWORD_VERSIONS
-----------------
10G 11G 12C


You cannot use the REPLACE clause, but the sqlplus command PASSWORD still works.

PS: after having earnt a total less than 100 USD of advertissement for over 1000 USD of hosting in the last 20 years, I gave up with AdSense today. Enjoy AddFree

select
  to_char(trunc(hiredate,'Y'),'YYYY') year,
  count(*)
from
  scott.emp
group by
  trunc(hiredate,'Y')
order by 
  trunc(hiredate,'Y')

YEAR COUNT(*)
---- --------
1980        1
1981       10
1982        1
1987        2

select 
  replace(
    replace(
      to_char(
        trunc(
          months_between(
            hiredate, 
            date '1900-01-01'
          )/6
        )/2,
        '00.0'
      ),
    '.0','H1'
  ),'.5','H2') HY, 
  COUNT(*) 
from 
  scott.emp 
group by 
  trunc(months_between(hiredate, date '1900-01-01')/6) 
order by 
  trunc(months_between(hiredate, date '1900-01-01')/6);

HY         COUNT(*)
------- -----------
80H2              1
81H1              5
81H2              5
82H1              1
87H1              2

We could also add some offset to start in March and choose 180 days instead of 6 months etc… But it gets really hard to read (and write)

In Oracle 23.7, I could not test it yet, simply write …

select
  time_bucket(hiredate, interval '6' month, date '1900-01-01'),
  count(*)
from
  scott.emp
group by
  time_bucket(hiredate, interval '6' month, date '1900-01-01')

It is much nicer

jdbc:oracle:thin:@ldaps://ldap.example.com/db01?WALLET_LOCATION=/db00/mywallet&AUTHENTICATE_BIND=true&AUTHENTICATE_BIND_METHOD=none&DIRECTORY_SERVER_TYPE=OID

EasyConnect Plus appeared in 19c, but was further improved in 21c and 23ai.

See more

JDBC Developer’s Guide

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html#GUID-44572C63-10D2-478A-BB2E-ACF6674C59CC