Author: Laurent Schneider

Pretty straightforward, check if dbms_transaction.step_id is null! SQL> select dbms_transaction.step_id from dual; STEP_ID ————— SQL> insert into t values (1); 1 row created. SQL> select dbms_transaction.step_id from dual; STEP_ID ————— 114352430549782 SQL> commit; Commit complete. SQL> select dbms_transaction.step_id from dual; STEP_ID ————— SQL> insert into t values (2); 1 row created. SQL> select dbms_transaction.step_id from […]

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

I have read on Surachart Opun’s blog that 11.2.0.2 is available, but also that the patchset is now a full installation. In the past, if you wanted to have 9.2.0.8 or 10.2.0.5, you needed to install both the base version, 9.2.0.1 or 10.2.0.1 and the patchset, 9.2.0.8 or 10.2.0.5. Very often the patchset itself was […]

I was reading http://blogs.oracle.com/myoraclediary 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 */ […]

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

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

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

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

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

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 John Jack $ echo ‘cat //emplist/emp[@no=”1″]/ename/text()’| xmllint –shell foo.xml | sed -n 3p John I like this !

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

11gR2 introduced a new mechanism to build up hierarchies. I remembered a thread in developpez.net that reveals the dubious implementation of nocycle in 10g. For the CONNECT BY ISLEAF, I have read the technique on amis.nl. Ok, here is my graph The 10g query with o as ( SELECT ‘A’ obj, ‘B’ link from dual […]

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 ? ” […]

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

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

I just came back from diving, parasailing, safari and more fun in Egypt. On my blog I saw a comment from Nicolas regarding the availability of the terminal patchset for 10gR2 Note 161818.1: Oracle Database Releases Status Summary 10.2.0.5: Patchset 8202632

Do you know cd ? I thought I did until this afternoon … OK, let’s start some basic. I create two directories $ echo $SHELL /bin/ksh $ 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 […]

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

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

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 ibm.com 2) install enterprise edition (next-next-install principle) 3) create the instance 4) create the sample database (where database […]

Did you go to http://www.sun.com recently? You will land on oracle.com ! 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 java.sun.com have the Oracle logo. That was fast! Well, Oracle […]

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’; COLUMN_NAME DATA_TYPE DATA_LENGTH ————— ——————– ———– 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> […]

After months of harassment between Oracle and Europe Commission, the deals got approved by the European Commission. Still MySQL founder Monty Widenius wants to have Russia and China reject the deal to save the future of MySQL… This will delay the deals further. Good for IBM, bad for Oracle !

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

It is quite a while I have not posted anything of interest, I pretty apologize to my faithful readers… the reason for being offline are multiple, one of them, I cannot access my blog from my workplace yet. Ok, the last hint from my dba regarding Oracle Support : If flash really drives you nuts, […]

Released as announced for 2009Q4… database, 11.2 for Solaris x86_64 Still Oracle is suffering with European Union Commission who is still blocking the Sun deal. google news about the deal

Good news, go there http://www.oracle.com/technology/software/products/database 🙂

It is quite a long time I did shot the pianist in my blog… probably patience and perseverance are better than aggressive behavior, but today I have been transfixed by an answer from metalink. Sometimes the issues I submit are a bit exotic but there it was a join with one view using one function […]

What are those .plz dump files in my user dump directory ? -rw-r–r– 1 oracle dba 15168 Oct 6 14:34 _anon__3ca8c5e38__AB.plz -rw-r—– 1 oracle dba 15883 Oct 6 14:45 db01_ora_10061.trc -rw-r–r– 1 oracle dba 15168 Oct 6 14:45 _anon__3c929b088__AB.plz -rw-r—– 1 oracle dba 15895 Oct 6 14:47 db01_ora_10666.trc -rw-r–r– 1 oracle dba 15168 Oct 6 […]

HPUX Itanium, Sun Sparc/x86_64, AIX –> october-december 2009 Windows, HPUX Risc –> april-june 2010 Ref: Release Schedule of Current Database Patch Sets Dates will change, keep an eye on the schedule, and do not base your business on expected dates ! Please be patient 🙂

I am in a java mood today, let’s check how to print hello world with jdbc 🙂 import java.sql.*; public class HelloWorld { public static void main(String[] args) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); ResultSet res = DriverManager. getConnection(“jdbc:oracle:thin:@srv1:1521:DB01”, “scott”, “tiger”). prepareCall(“select ‘Hello World’ txt from dual”). executeQuery(); res.next(); System.out.println(res.getString(“TXT”)); } } let’s compile javac -classpath […]