🎂 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 crontab. * […]

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 grantee […]

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<100; alter […]