— laurentsch (@laurentsch) October 6, 2016
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) end; end; select value(t), f(value(t)) from table(sys.odcinumberlist(4,6))t VALUE(T) F(VALUE(T)) ---------- ----------- 4 24 6 720
It is neither quicker nor shorter than the one I posted in 2005, but it could not have worked in 2005
I just added a few features to WordPress, please report any bug and test your comments here.
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
For purge dba_recyclebin, you probably should purge tables individually
exec for f in(select*from dba_recyclebin where owner!='SYS' and type='TABLE')loop execute immediate 'purge table "'||f.owner||'"."'||f.object_name||'"';end loop;
For DBMS_STREAMS_AUTH, what I am actually missing, is the GRANT OPTION on some documented dba views and dbms package. So I could safely grant the grant option to my user for all sys objects that have been granted to DBA, PUBLIC and any other roles.
create table scott.t as select distinct owner,table_name,privilege from dba_tab_privs t where privilege not in ('USE','DEQUEUE') and owner='SYS' ; begin for f in(select * from scott.t) loop execute immediate 'grant '||f.privilege||' on "'||f.owner||'"."' ||f.table_name||'" to scott with grant option'; end loop; end; /
It is better to not select from dba_tab_privs directly, as executing immediate while opening the cursor may have unexpected side effects.
This may help you to increase your security by reducing your connections as sys.
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.
* * * * * /usr/bin/date > /tmp/bar
Later, I quit vi with :q!
O Surprise, the crontab is * * * * * /usr/bin/date > /tmp/foo again
According to the doc :
When you finish creating entries and exit the file, the crontab command
copies it into the /var/spool/cron/crontabs directory
Even if you did not make change, you overwrite the content of your crontab !
If you want to exit your crontab editor really without overwritting the crontab, you need to kill yourself.
CTRL-Z  + Stopped (SIGTSTP) crontab -e $ kill %2 $  + Stopped (SIGTTOU) crontab -e
Thanks to Colin comment, I realized I could not kill with kill, let’s kill with -9
$ kill -9 %2 $  + Killed crontab -e
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 slow keyboard…
Even more confusing
PS> get-date "1970-01-01 00:00:00 +00:00" -u %s 3600
Ok, why that? this is related to the Kind
PS> (get-date "1970-01-01 00:00:00 +00:00").kind Local
A Unix timestamp of Kind Local is useless. Don’t use this.
Unix timestamp should only refers to Utc.
PS> get-date Sonntag, 11. März 2012 10:47:35 PS> get-date ((get-date).touniversaltime()) -u %s 1331459257.78432
still it is very confusing to using the unixformat in Windows
PS> get-date -u %c__%Z__%s So Mrz 11 10:55:05 2012__+01__1331463305.85197 PS> get-date ((get-date).touniversaltime()) -u %c__%Z__%s So Mrz 11 09:55:46 2012__+01__1331459746.07913
the %s does not work as expected with local (as it refers to a local 1970-01-01) and %Z does not work at all with utc (it sould be +00
OK, let’s switch to the Microsoft time. You count the time as the number of tenth of microseconds (or ticks) since 1 Jan 0001. Again it may differ if you do not use utc.
Let’s define constant $c as number of ticks until 1970-01-01, in utc
PS> $c = (get-date "1970-01-01 00:00:00 +00:00").touniversaltime().ticks PS> $c 621355968000000000
Let’s convert it to a human readable format
PS C:\> New-Object datetime($c) Donnerstag, 1. Januar 1970 00:00:00
So far so good.
Lets convert 1331461119 back and forth
PS> (New-Object datetime($c + 1331461119 * 10000000)).tolocaltime() Sonntag, 11. März 2012 11:18:39 PS> (New-Object datetime($c + 1331461119 * 10000000)) Sonntag, 11. März 2012 10:18:39 PS> ( (get-date "2012-03-11 10:18:39 +00:00").touniversaltime().ticks - $c ) / 10000000 1331461119 PS> ( (get-date "2012-03-11 11:18:39 +01:00").touniversaltime().ticks - $c ) / 10000000 1331461119
BTW, what does Oracle think of the number of ticks of current date ?
PS> $d=36217;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000) TO_CHAR(DA ---------- 0100-02-28 Sonntag, 28. Februar 0100 00:00:00 PS C:\> $d=36218;"select to_char(date '0001-01-01' + $d,'YYYY-MM-DD') from dual;"|sqlplus -s -L scot/tiger;New-Object datetime($d*864000000000) TO_CHAR(DA ---------- 0100-02-29 Montag, 1. März 0100 00:00:00
Apparently Microsoft does not have a leap year in 100AD.
Ok, whatelse to say about date?
There is one more format that is commonly used in Microsoft world, the so-called DMTF or Distributed Management Task Force. Let’s have a quick look with 1-JAN-2000.
PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00")) 20000101000000.000000+060 PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00 +00:00").touniversaltime()) 20000101000000.000000+000 PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+060') Samstag, 1. Januar 2000 00:00:00 PS C:\> [System.Management.ManagementDateTimeConverter]::ToDateTime('20000101000000.000000+000').touniversaltime() Samstag, 1. Januar 2000 00:00:00
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 grantee, privilege, REPLACE ( REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'), '^/[^/]*'), '/', ' --> ') ROLES, owner, table_name, column_name FROM (SELECT PRIVILEGE, GRANTEE, OWNER, TABLE_NAME, NULL column_name FROM DBA_TAB_PRIVS WHERE owner NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN', 'MDSYS', 'ORDSYS', 'XDB', 'WKSYS', 'EXFSYS', 'OLAPSYS', 'DBSNMP', 'DMSYS', 'CTXSYS', 'WK_TEST', 'ORDPLUGINS', 'OUTLN', 'ORACLE_OCM', 'APPQOSSYS') UNION SELECT PRIVILEGE, GRANTEE, OWNER, TABLE_NAME, column_name FROM DBA_COL_PRIVS WHERE owner NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN', 'MDSYS', 'ORDSYS', 'XDB', 'WKSYS', 'EXFSYS', 'OLAPSYS', 'DBSNMP', 'DMSYS', 'CTXSYS', 'WK_TEST', 'ORDPLUGINS', 'OUTLN', 'ORACLE_OCM', 'APPQOSSYS') UNION SELECT GRANTED_ROLE, GRANTEE, NULL, NULL, NULL FROM DBA_ROLE_PRIVS WHERE GRANTEE NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN', 'MDSYS', 'ORDSYS', 'XDB', 'WKSYS', 'EXFSYS', 'OLAPSYS', 'DBSNMP', 'DMSYS', 'CTXSYS', 'WK_TEST', 'ORDPLUGINS', 'OUTLN', 'ORACLE_OCM', 'APPQOSSYS')) T START WITH grantee IN (SELECT username FROM dba_users) CONNECT BY PRIOR PRIVILEGE = GRANTEE) WHERE table_name IS NOT NULL AND grantee != OWNER ORDER BY grantee, owner, table_name, column_name, privilege;
GRANTEE PRIVILEGE ROLES OWNER TABLE_NAME COLUMN_NAME ------- --------- --------------- ------ ---------- ----------- U UPDATE --> R SCOTT DEPT DNAME U SELECT SCOTT EMP U2 UPDATE --> R2 --> R SCOTT DEPT DNAME
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
Later, to access your Enterprise Manager Grid Control homepage, access the Apache server and not the oms upload server
opmnctl status -l HTTP_Server http1:7779,http2:7201,https1:4445,https2:1159,http3:4890
https1 is Apache (4445) and https2 is Upload (1159)
So the url will be https://yourserver.dom.com:4445/em
Check note 1278231.1
I just upgraded my blog to wordpress 3
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 :
create database link lsc_link_2 using 'DB02'; create table lsc_small_1 as select rownum x from dual connect by level<100; alter table lsc_small_1 add primary key(x); create table lsc_big_1 as select rownum x from dual connect by level<1000000; alter table lsc_big_1 add primary key(x);
create table lsc_small_2 as select rownum x from dual connect by level<100; alter table lsc_small_2 add primary key(x); create table lsc_big_2 as select rownum x from dual connect by level<1000000; alter table lsc_big_2 add primary key(x);
and here are the two test queries (run on DB01)
select count(*) from lsc_big_1 b1 join lsc_small_2@lsc_link_2 s2 using (x); COUNT(*) ---------- 99 Elapsed: 00:00:00.10 select count(*) from lsc_big_2@lsc_link_2 b2 join lsc_small_1 s1 using (x); COUNT(*) ---------- 99 Elapsed: 00:00:10.31
As shown above, first one is 100x faster. Anything to tune? For sure!
The trick is to execute the join remotely when the remote table is much larger than the local one!
select /*+ DRIVING_SITE(b2) */ count(*) from lsc_big_2@lsc_link_2 b2 join lsc_small_1 s1 using (x); COUNT(*) ---------- 99 Elapsed: 00:00:00.06
Oracle Database Download
Download my feed :
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
connect by prior mgr=empno
start with ename=’SCOTT’;
I start with Scott and the hierarchy is built. I can use the pseudo column LEVEL in hierarchical queries.
One of the biggest problem in hierarchical queries is ORA-01436: CONNECT BY loop in user data.
If you are your own boss, or if you are the boss of your boss, then you have built a cycle. It is probably not wished to have this relation, but it cannot be enforced by a constraint and before 10g, it was difficult to detect. In 10g, you have a new clause, CONNECT BY NOCYCLE, which detect cycles and give flag.
Here I want to present an alternative way of using hierarchies.
I have five boxes of five different sizes. Tiny up to 5 liters. Small up to 10 liters. Medium up to 15 liters. Big up to 20 liters. Hudge up to 25 liters.
I have 3 fluids, and I want to find the smallest boxes.
Yeah! very easy, you do select fluids.volume, min(box.capacity) from fluids, box where capacity>=volume group by volume, do not you?
Ok, but I do not want to mix the fluids! So I need 3 different containers.
This means, I will start with the first product, find the smallest box, go to the second, find the smallest free box, and so on.
Hmm… It seems an impossible task with analytics, least, min, keep, lag, lead, ???
Ok, I am going to build a hierarchy, based on volume > prior volume and capacity > prior capacity.
Then I do a min with the sys_connect_by_path function.
select max(sys_connect_by_path(volume,’/’)) volume_path,
(select row_number() over (order by volume) r, count(*) over () c, volume from fluids)
connect by capacity > prior capacity and r > prior r;
There is also one more CONNECT I would like to briefly mention, is the connect without prior.
select level from dual connect by level<11;
It is a special way of creating pivot tables, but it is fairly dangerous, and could make your session / database hang, depending on your oracle version.
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 a lot of reports using in max subqueries, like in
select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
With analytics, I can rewrite it with
select deptno,ename,sal from ( select emp.*, rank() over ( partition by deptno order by sal desc) r from emp ) where r=1;
If I want to get exactly one row per deptno, I could then write something like
select deptno,ename,sal from ( select emp.*, row_number() over ( partition by deptno order by sal desc) r from emp ) where r=1;
select deptno,ename,sal from ( select emp.*, row_number() over ( partition by deptno order by sal desc, empno) r from emp ) where r=1;
row_number will select only one row. I prefer the second, because empno is a primary key and the result will be constant over time. In the first solution, I cannot determine which row will be returned, and Oracle may choose one on the morning, and another one in the afternoon, depending on the execution plan (new index/new stats/different load/…).
Note that I can also select the 2nd biggest salary (r=2), or the top 5 (r<=5) It is also very performant, because you are doing only one full table scan instead of two