Category Archives: Uncategorized

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.


update, 2012-07-24
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.

Kind of

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' ;
  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;

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.

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.

* * * * * /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.


[2] + Stopped (SIGTSTP)        crontab -e
$ kill %2
[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
[2] + Killed                   crontab -e

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'"
PS> get-date -u %s

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

Ok, why that? this is related to the Kind

PS> (get-date "1970-01-01 00:00:00 +00:00").kind

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

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

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
PS> ( (get-date "2012-03-11 11:18:39 +01:00").touniversaltime().ticks - $c ) / 10000000

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)


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)


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"))
PS C:\> [System.Management.ManagementDateTimeConverter]::TodmtfDateTime((get-date "2000-01-01 00:00:00 +00:00").touniversaltime())
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

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

    FROM (    SELECT CONNECT_BY_ROOT grantee grantee,
                     REPLACE (
                        REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
                        ' --> ')
                FROM (SELECT PRIVILEGE,
                             NULL column_name
                        FROM DBA_TAB_PRIVS
                       WHERE owner NOT IN
                      SELECT PRIVILEGE,
                        FROM DBA_COL_PRIVS
                       WHERE owner NOT IN
                      SELECT GRANTED_ROLE,
                        FROM DBA_ROLE_PRIVS
                       WHERE GRANTEE NOT IN
                                 'APPQOSSYS')) T
          START WITH grantee IN (SELECT username FROM dba_users)
   WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,

sample output

------- --------- --------------- ------ ---------- -----------
U       UPDATE     --> R          SCOTT  DEPT       DNAME      
U       SELECT                    SCOTT  EMP                   
U2      UPDATE     --> R2 --> R   SCOTT  DEPT       DNAME      

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 

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

Check note 1278231.1

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 :

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);
Elapsed: 00:00:00.10

select count(*) from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
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);
Elapsed: 00:00:00.06

Ref: the DRIVING_SITE hint

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’;


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,
ltrim(min(lpad(sys_connect_by_path(capacity,’/'),999))) capacity_path
from box,
(select row_number() over (order by volume) r, count(*) over () c, volume from fluids)
where volume
connect by capacity > prior capacity and r > prior r;

/7/11/14 /10/15/20

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.

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 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;

or better

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)

It is also very performant, because you are doing only one full table scan instead of two