This version of TOAD provides READONLY access!

This Toad 11 beta new feature made me so happy !

Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only.

For any reason, bored, ill, under pressure, tired, you may screw up your database one of those days.

I feel so good that I can now setup my prod connection to be read-only. It is REALLY cool.

Setting the connection read-only is a TOAD feature. As it is in beta it may not be 100% bullet-proof. Mostly it allows you to do only SELECT in the editor, even if you are logged in as SYSTEM or SYS.

Anything comparable in SQL Developer?

[FUN] How to tune select count(*) from t?

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?

user007@PROD> select count(*) from t;

Elapsed: 00:03:05.00

Let’s try in test :

user007@TEST> select count(*) from t
select count(*) from t
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16

Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time :twisted:

SQL developer for dba

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba.

But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally.

Still no session browser, but I am delighted that the database administrators are not getting forgotten !

Thanks Surachart for spreading the news!

jdbc ssl

I already wrote about jdbc hello world and listener with tcps.

Let’s combine both technologies !

import java.util.Properties;
import java.sql.*;

public class TCPS {
  public static void main(String argv[]) throws SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);
    ResultSet res = conn.
      prepareCall("select 'Hello SSL World' txt from dual").

I have an auto-login wallet (cwallet.sso) with the trusted certificate from the server.

There are a few jar’s to use:

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ export CLASSPATH
$ javac
$ java TCPS
Hello SSL World

Greatly inspired by Jean de Lavarene’s white paper : SSL With Oracle JDBC Thin Driver

How to check if I have a pending transaction?

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;


SQL> insert into t values (1);

1 row created.

SQL> select dbms_transaction.step_id from dual;


SQL> commit;

Commit complete.

SQL> select dbms_transaction.step_id from dual;


SQL> insert into t values (2);

1 row created.

SQL> select dbms_transaction.step_id from dual;


SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;


on my bookshelf

I just started a new job as a dba and placed some books on my desk.

Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars
SQL Design Patterns by Vadim Tropashko
Beginning Oracle Database 11g Administration by Iggy Fernandez
Expert Oracle Database Architecture by Tom Kyte
Oracle 11g New Features by Brian Carr, John Garmany, V. J. Jain, Steve Karam and Lutz Hartmann
Advanced Oracle SQL Programming my book…

I have not worked as an 11g dba before, I will surely learn a lot in those books (and giveaway my book) !

I have read on Surachart Opun’s blog that is available, but also that the patchset is now a full installation.

In the past, if you wanted to have or, you needed to install both the base version, or and the patchset, or Very often the patchset itself was bigger and took more time than the base database installation.
According to Note 1189783.1, it is now possible to install without having to install both + a patchset.

if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release patch set

I like this :-)

last access time of a file

I was reading and there was a command about printing the modification details of a file.

In Linux / Cygwin “stat” exists as a command

$ stat /etc/hosts
Access: 2010-08-25 15:20:49.782522200 +0200
Modify: 2010-08-18 14:04:25.868114200 +0200
Change: 2010-08-18 14:04:26.072413100 +0200

Or use the one-liner perl below

### st_atime;         /* Time of last access */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[8])'
Wed Aug 25 15:20:08 2010
### st_mtime;         /* Time of last data modification */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[9])'
Wed Jun 10 11:36:40 2009
### st_ctime;         /* Time of last file status change */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[10])' 
Wed Aug 25 01:00:07 2010

OCE Solaris Network Admin

I read Paul Sorensen blog : Sun certifications will be renamed next week.

For instance the Sun Certified Network Administrator will be Oracle Certified Expert, Oracle Solaris 10 Network Administrator.

But to get the OCE Solaris title, you need to upgrade your certification [...] to receive an Oracle certification title (check more details on the Oracle Certification website here).

I never upgraded my Sun credentials, I am a Solaris 2.6 system admin and Solaris 7 network admin, but this equivalency with OCE surprised me.

There are even two OCM Java certifications : Oracle Certified Master, Java EE 5 Enterprise Architect, originally Sun Certified Enterprise Architect (SCEA) and Oracle Certified Master, Java SE6 Developer, originally Sun Certified Java Developer (SCJD)

Also, since a few months, there is an upgrade to the 9i OCM DBA certification : 11g OCM Upgrade. I hope I can do this one :)

Do you know the ORA- nonerrors?

In one of my script, I am checking at the end for any ORA- error. And if I have any ORA- error, I quit with an error.

So far so good.

Also when I run a report from the shell, I do set the sqlplus settings I like and I expect the script to receive the output with no ORA- error at the beginning.

But watch this !

$ echo "set feed off hea off newp none
conn scott/tiger
select * from dual;"|sqlplus /nolog
ORA-28002: the password will expire within 10 days


My password will expire in 10 days. And this screw up my day :(

Hardcoding SYSDATE

I see TRUNC(SYSDATE) in the code very often. Is this good or bad?

Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.

But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.


You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

On Express Edition

If you want to start with developing free software on old technology, you can download the Oracle Database Express Edition.

Actually, Oracle 10g is more than 5 years old and it is the only version available as Express Edition, 11g has not been released at the time of the writing, and no patch has been released, not even for the worst bugs or security breaches.

Most probably you will not want to run this for your sensitive data.

If you are serious about Oracle Development, you could download the Enterprise Edition for free, and get the Personal Edition on Oracle Shop for support and patches.

Last Friday 13th August, a news/rumor (not an official announcement) was posted on the opensolaris forum, apparently OpenSolaris has been cancelled in favor of an Oracle Solaris 11 Express. Not sure if it will come in the same format as the database XE, but this does not sound to please the community.

After the Oracle sues Google, it does not make Oracle very popular in the opensource community at the very moment


to cvs or to subversion

First surprise, after migration, the size of my subversion folder is double the size of my cvs folder. With a bunch of 2Gb disks shared amoung dozens of unix persons, and regular reminders the current usage reached 100%, you will feel the pain of having each developers doublesizing its home directory…
The reason is a .svn/test-base directory containing a duplicate of your local copy.
The benefit there is that it reduces network usage, for instance when diffing with the working version.

Second surprise, not sure if it is a generic issue or related to the one above, a full checkout take ages :( .

svn and cvs command line interfaces almost have the same options, a bit like vi and vim -vi is my favorite- but the “tagging” massively differs. The is no cvs tag command in subversion, you use a different syntax.
cvs :

$ cd /home/lsc/CVS/proj1/source/pkg
$ cvs tag MYTAG helloworld.pkb helloworld.pks

now enjoy the power of subversion! [updated as I found the --parents option]

$ cd /home/lsc/SVN/proj1/source/pkg
$ svn copy --parents helloworld.pkb -m "my first svn tag"
$ svn copy --parents helloworld.pks -m "my first svn tag"

I always loved the cvs rename command

$ cvs rename pkg1.pkb pkg2.pkb
cvs [rename aborted]: Remote server does not support rename

Joke apart, in CVS I used to physically logon to the server and manually move the pkg1.pkb,v to pkg2.pkb,v
Renaming works in subversion :

$ svn move pkg1.pkb pkg2.pkb
A         pkg2.pkb
D         pkg1.pkb

Update: One more annoyance, you cannot checkout a single file :(
subversion faq

extract xml from the command line

I just discovered this morning this cool utility in my /bin directory : xmllint

You can use it to extract values from your xml files within your shell scripts

$ cat foo.xml
  <emp no="1">
  <emp no="2">
$ echo 'cat //emplist/emp[@no="1"]/ename/text()'|
  xmllint --shell foo.xml |
  sed -n 3p

I like this !

connect by and recursive with (part 2)

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

  1. Create a big emp
    create table big_emp as
    with t(empno,mgr) as
    (select 1, null from dual
    union all 
    select empno+1,trunc(dbms_random.value(1+empno/10,empno))
    from t 
    where empno<100000)
    select * from t

  3. execution plan

    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null;
    E M 
    - -
    1 -  
    2 1 
    4 2 
    6 2 
    8 6 
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  185      78
    TABLE ACCESS FULL         BIG_EMP      1    1   61      10
    HASH JOIN                              2    2  122      46
    TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000

    recursive WITH

    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select empno,mgr
    from e;
    E M
    - -
    1 -  
    2 1 
    3 1 
    4 2 
    5 3 
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  183      78
    VIEW                                   3    3  183      78
    TABLE ACCESS FULL BIG_EMP              1    1   61      10
    HASH JOIN                              2    2  122      46
    TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000

    In this particular simple case, it seems CONNECT BY have a 1% higher cost.

  5. execution time

    select sum(mgr)
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null
    1 rows returned in 0.73 seconds 

    recursive with

    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select sum(mgr)
    from e;
    1 rows returned in 1.24 seconds 

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in

And then one day you find, ten years have got behind you.

CONNECT BY and Recursive CTE

11gR2 introduced a new mechanism to build up hierarchies.

I remembered a thread in that reveals the dubious implementation of nocycle in 10g.

For the CONNECT BY ISLEAF, I have read the technique on

Ok, here is my graph

The 10g query

with o as
SELECT 'A' obj, 'B' link from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual)
select connect_by_root obj root,level,obj,link,
from o 
connect by nocycle obj=prior link
start with obj='A';

---- ----- - - -------------------- ----- -----
A        1 A B ,A->B                    0     1
A        1 A C ,A->C                    0     0
A        2 C D ,A->C,C->D               1     0
A        3 D E ,A->C,C->D,D->E          1     1

Obviously in 10g the connect by nocycle does not work that well with that kind of graphs, D-C and E-E are missing and C-D and D-E are marked as cycling…

Let’s try the 11gR2 equivalency.

with o(obj,link) as
SELECT 'A', 'B' from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual),
t(root,lev,obj,link,path) as (
select obj,1,obj,link,cast(obj||'->'||link 
as varchar2(4000))
from o 
where obj='A'  -- START WITH
union all
  t.path||', '||o.obj||
from t, o 
search depth first by obj set ord
cycle obj set cycle to 1 default 0
select root,lev,obj,link,path,cycle,
    when (lev - lead(lev) over (order by ord)) < 0
    then 0
    else 1
    end is_leaf
 from t;

ROOT LEV  OBJ  LINK PATH                        CYCLE IS_LEAF
---- ---- ---- ---- --------------------------- ----- -------
A    1    A    B    A->B                            0       1
A    1    A    C    A->C                            0       0
A    2    C    D    A->C, C->D                      0       0
A    3    D    C    A->C, C->D, D->C                0       0
A    4    C    D    A->C, C->D, D->C, C->D          1       1
A    3    D    F    A->C, C->D, D->E                0       0
A    4    F    F    A->C, C->D, D->E, E->E          0       0
A    5    F    F    A->C, C->D, D->E, E->E, E->E    1       1

It looks good :)

If you exclude the rows with cycle=1, you get the six rows for the graph.

read without Enter

A small unix tip today.

Do you want to continue ?

If you are expecting “y” or “n” but do not want to enforce the user to type y[Enter] but simply y, you can use the -n option in bash.

Within a ksh script:

yorn=$(bash -c 'read -p "Do you want to continue ? " -n 1 ans;echo "$ans"')

Within bash:

read -n 1 yorn

number series

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries ;)

with t(x) as (select 1 from dual 
union all
select x+1 from t where x<5 )
select x from t;


with t(x,y) as (select 1 x, 1 y from dual 
union all
select x+1,y*(x+1) from t where x<5 )
select x,y "X!" from t;

X  X!
1  1
2  2
3  6
4  24
5  120

with t(r,x,y) as (select 1,1,1 from dual 
union all
select r+1,y,x+y from t where r<5)
select x fib from t


with t1(x) as (select 1 from dual 
union all
select x+1 from t1 where x<4),
t2(x,y,z) as (select x, 1,x from t1
union all 
select x,y+1,x*(y+1) from t2 where y<2)
select listagg(z,';') within group (order by x) s from t2 group by y;


where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?

SYS@lsc01> create user u1 identified by u1;

User created.

SYS@lsc01> create user u2 identified by u2;

User created.

SYS@lsc01> grant create table, unlimited tablespace to u1;

Grant succeeded.

SYS@lsc01> grant create session, create trigger to u2;

Grant succeeded.

SYS@lsc01> create table u1.t(x number);

Table created.

SYS@lsc01> grant select on u1.t to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
U2@lsc01> create trigger after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /
create trigger after insert on u1.t for each row
ERROR at line 1:
ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.

U2@lsc01> connect / as sysdba
SYS@lsc01> grant CREATE ANY TRIGGER to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
U2@lsc01> create trigger after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.


Do you know cd ? I thought I did until this afternoon …

OK, let’s start some basic.

I create two directories

$ echo $SHELL
$ mkdir /tmp/foo
$ mkdir /tmp/bar

create a symlink /tmp/bar/baz pointing to /tmp/foo

$ ln -s /tmp/foo /tmp/bar/baz

create a file foo1 in foo

$ touch /tmp/foo/foo1

change to the symbolic link baz

$ cd /tmp/bar/baz

Ok, so far so good. Let’s check what is in ../foo

$ ls ../foo

From the symbolic baz, .. point to /tmp/foo. This is because ls and most command line utilities use the physical path.

To print the Logical [default] and Physical working directories, use pwd -L and pwd -P

$ pwd -L
$ pwd -P

to change directory relatively to the logical path, use cd -L … (default), for physical, use cd -P … !

$ pwd -L
$ cd -L ../foo
ksh: ../foo:  not found

Obviously /tmp/bar/foo does not exist

$ pwd -L
$ pwd -P
$ cd -P ../foo
$ pwd

Obviously /tmp/foo/../foo is /tmp/foo

So far so good, some of you may know that already.

Let’s bring some devil element in play

$ bash

Arghh!!! Ôôôôôôôôôôôôôô râge, Ôôôôôôôôôôôôôô désespoir, I switched to a non-working shell!

$ cd /tmp/bar/baz
$ cd -L ../foo
$ pwd -L

Even if I switched to a not working directory, bash cd -L weirdly decided to switch to the physical path instead of the logical path.

Let’s retry

$ cd /tmp/bar/baz
$ mkdir /tmp/bar/foo
$ cd -L ../foo
$ pwd

This time bash cd -L changed to the logical path. So if you use bash and cd, you cannot possibly know where you are landing without checking first if the directory exist !

BTW, I just discovered Digger HD , unrelated to this post of course …

where c not in (:b1, :b2, … , :b9999)

I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in

create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);

If you want to however do this in one query you can still use AND

SQL> select foo from bar where c not in (
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000

SQL> select foo from bar where 
c!=:b1 and
c!=:b2 and


Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…

If you use dynamic expression list, this will bypass the ORA-01795 error

Book review: oracle sql developper

Sue Harper is the product manager for Oracle SQL developer. She is also the author of a book, Oracle SQL Developer 2.1 .

A bunch of Oracle blogger received a free online version of the book with the task to write a review.

I have read a few chapters and here are my general comments.

buy from amazon
+ The picture on the book is very nice, not related to SQL Developer, but shot by Sue :) .

– There is no chapter on unit testing, which is one of the newest feature of SQL Developer. It is not very mature yet, but it is a good addition and free.

+ The book is not a marketing joke, the positioning against tool like TOAD is quite the way I see it.
page 1: The skeptics mentioned are ever concerned that Oracle is not really interested in the product,
backing up this concern with the fact that SQL Developer is free.

+ Even as a sqlplus/sh fanatic I did not feel ignored
page 6: We’re aware that you’ll never fully move from the command-line

– If you are a bit of a gray hair security admin you will be shocked by statements like page 21: create a connection for SYSTEM and page 22: Select the Save Password checkbox. This makes life easy. Hmm, do you give SYSTEM access to your developers and do your security policies recommend/allow them to save the password locally?

+ Still SQL Developer is good tool and it is driven by community. Save password is no longer the default (an old thread). You can post your own suggestions like Use Wallet for Logins to the SQL Developer Exchange.

+ There are plenty of good features in this tool and the book covers them. I like reports for instance.

= The book is still a book about click-click-click. Most of my readers here know how to drag and drop and resize windows.

PS: if it was not obvious in my comments, yes I do like this book!

on analytics and superaggregation

When I wrote my book, I did not expect having the advanced Oracle sql features available on other dabatase engine.

This week-end I downloaded for fun a try of db2, v9.7.
1) download db2 linux64bit trial on
2) install enterprise edition (next-next-install principle)
3) create the instance
4) create the sample database (where database and instance means something different than in Oracle)

$ db2 create db test pagesize 32 k
$ db2 connect to test
$ db2 create user temporary tablespace temp
$ db2 connect reset

5) connect with clpplus, which looks similar to sqlplus, even cooler at first look
$ clpplus db2inst1/***@vaio:50001/TEST

I tried a few examples from my book. Left, full and cross join works, but not partitioned outer join.

Scalar, Inline and nested query works too.

Surprisely many function that I thought Oracle specific work too, like sysdate and to_char.

In aggegation, the KEEP FIRST/LAST does not work,
In Super aggregations, all GROUPING SETS, CUBE, ROLLUP do work.
No Pivot or Unpivot.

No dual, but a powerful TABLE function

SQL> select * from table(values(1),(2));       

Analytics works, With range, rows, order by partition, all the fancy stuff.

I have not checked at XML, but it sounds to be differently implemented.
Some things will work however

SQL> select xmlquery('1 to 10') from dual;

1 2 3 4 5 6 7 8 9 10

No CONNECT BY, but as in Oracle 11gR2, recursive query factoring does build the hierarchy

No Model, obviously.

Oracle – Sun, what has changed ?

Did you go to recently? You will land on !

But more will come.

The conference JavaOne will be in SanFrancisco with Oracle OpenWorld.

The Sun Developer Network and Bigadmin will be integrated in OTN.

Welcome Sun Developers Ensuring community continuity

MySql and have the Oracle logo.

That was fast! Well, Oracle is quite used to acquisition, only this week they bought AmberPoint and Convergin.

Still MySQL strategy will probably change, I cannot believe Oracle will encourage the users to save millions on Oracle License by migrating to MySQL

what is the type of NULL

I was a bit surprised to see a VARCHAR2(0) column in my schema

select column_name, data_type, data_length 
from user_tab_columns 
where table_name='V';
--------------- -------------------- -----------
X               VARCHAR2                       0

What’s this datatype? It is the datatype of NULL !!!

SQL> create or replace view v as select null x from dual
View created.
SQL> desc v
 Name              Null?    Type        
 ----------------- -------- ------------
 X                          VARCHAR2(0) 

How many decimals do you need?

Do you user NUMBER or NUMBER(p,s) for your datatypes?

I posted last year about 1!=1

Today I realized this could be solved with the scale :)

SQL> drop table lsc_t
Table dropped.
SQL> create table lsc_t(x number, y number(*,6))
Table created.
SQL> insert into lsc_t values (1/3*3,1/3*3)
1 row created.
SQL> commit
Commit complete.
SQL> select * from lsc_t where x=1
no rows selected.
SQL> select * from lsc_t where y=1

         X          Y
---------- ----------
1.00000000          1

So if you have amounts in US$ or GB£ or CHF, do not use NUMBER, use NUMBER(*,6) or NUMBER(*,2) or whatever is relevant to your business!

Oracle Certified Master