19c

19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found . Distinct listagg . Desupport sqlplus product profile . listener.log log rotation Go to the doc to find more https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html

🎂 10 years 🎂

Thanks to all my readers for being so faithful 🙂 I’ll post a new solution to calculate factorial. This is the one I posted 10 years ago : https://laurentschneider.com/2005/05/recursive-sql.html I also used it in the obfuscation contest with function f (x number) return number is begin return case x when 1 then x else x*f(x-1) […]

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations. Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role. Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE update, 2012-07-24 For purge […]

How to quit crontab -e without overwritting cron

Imagine my crontab * * * * * /usr/bin/date > /tmp/foo I am writing the date to /tmp/foo every minute $ cat /tmp/foo Thu Jul 5 08:45:01 CEST 2012 Now I want to view my crontab in my EDITOR (vi). $ crontab -e I do not quit yet. In the meantime, my colleague modify the […]

Powershell and dates

I wrote about unix timestamp i powershell. I wrote : It is chockingly easy ! but I should have written : … it is not correct 🙁 PS> ./perl -e “print time.’`n'” 1331454753 PS> get-date -u %s 1331458358.05694 there is about 3605 seconds difference. 1 hour for Europe/Zurich and 5 seconds to type on a […]

List of table and column privileges, including those via roles

I could not find this quickly enough in google so I wrote it myself. The list of table privileges, with a connect by subquery. COL roles FOR a60 COL table_name FOR a30 col privilege for a9 set lin 200 trims on pages 0 emb on hea on newp none SELECT * FROM ( SELECT CONNECT_BY_ROOT […]

Use your own wallet for EM

If you want to get rid of self signed certificate, and the annoying security warnings in your browser, here is how to do it in 2 easy steps 1) create a new wallet in [OMS]/sysman/wallet/console.servername/, either with owm (gui) or with orapki (command line) 2) restart opmn http server opmnctl stopproc process-type=HTTP_Server opmnctl startproc process-type=HTTP_Server […]

Tuning query over database link

I just learnt from a colleague a very useful hint for remote databases. Over a db link, Oracle does not have the necessary statistics to take the right decision. Here is the test case : DB01 create database link lsc_link_2 using ‘DB02’; create table lsc_small_1 as select rownum x from dual connect by level

Hierarchical queries

The typical hierarchical query is you want to select your boss, and the boss of your boss, etc. It could look like select prior ename ename, ename mgr from emp connect by prior mgr=empno start with ename=’SCOTT’; SCOTT SCOTT JONES JONES KING I start with Scott and the hierarchy is built. I can use the […]

Oracle analytics in basic sql queries

When I first saw analytics appearing in Oracle last century, I did not realised they were going to change my way of writting basic SQL queries. Some (Variance, deviance) are truely mathematical and still reserved for statistical analysis. Here I will try to describe ROW_NUMBER : Back in Oracle 7, I remember to have written […]