checksum of a column

Something I always wanted arrived this week, a checksum of a column ! SQL> create table t1(x number); Table created. SQL> create table t2(x number); Table created. SQL> insert into t1(x) values (1); 1 row created. SQL> insert into t2(x) values (1); 1 row created. SQL> select (select checksum(x) from t1)t1, (select checksum(x) from t2)t2… Continue reading checksum of a column


19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 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

Oracle 19c

2014/05/distinct-listagg I wrote : Too bad the DISTINCT keyword was not implemented what a good surprise to see this working in Oracle 19 : 19C LISTAGG DISTINCT

Enterprise Manager 13c R2 is out

#EM13R2 is out — laurentsch (@laurentsch) October 6, 2016 Download it here : Documentation :

🎂 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 : 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)… Continue reading 🎂 10 years 🎂

Happy new year

I just added a few features to WordPress, please report any bug and test your comments here. Thanks

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… Continue reading grant select on sys tables

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… Continue reading Powershell and dates

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… Continue reading List of table and column privileges, including those via roles

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… Continue reading Use your own wallet for EM

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… Continue reading Hierarchical queries