Laurent Schneider

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 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));       
          1
-----------
          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                   
--------------------
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.

DB2

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 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
http://www.mysql.com/products/enterprise/unlimited.html

oracle buys sun

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> desc v
VIEW v
 Name              Null?    Type        
 ----------------- -------- ------------
 X                          VARCHAR2(0) 

null

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 !

oracle buys sun

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!

scale

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, go for html : supporthtml.oracle.com

metalink

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

solaris x86_64

Good news, go there http://www.oracle.com/technology/software/products/database and there
Quick install guide

release

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 and dual returning wrong results. Reproducible at will on any db 10.2.0.4 and below.

After 2 weeks the helpful engineer told me it is fixed with patch 6471020 and in 11g. I asked if he tried it but he said no. As the patch is not available on my platform I asked if could try it but then he came back with an amazing workaround he became from an ARE (advanced resolution engineer) :

Remove the function from the view.

Unreal

bug

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 14:47 _anon__3c8651198__AB.plz

let’s check one, briefly :

*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d79fb40 has no MAP method.
Source Location = _anon__3d2474b28__AB[1, 7]

== Dump of OPT Context Object ffffffff7c519ec8. ==
  Tue Oct  6 16:31:11 2009
  Event 10944 = 0
  plsp          = ffffffff7fff67a8
  lu            = 3c9c18210
  Diana root    = 0x20014 = 131092
  Diana proc    = 0x20012 = 131090
  Graph         = ffffffff7d774d70
  Dump file     = /app/oracle/admin/DB01/udump/_anon__3d2474b28__AB.plz
  CG            = 0
...

It is a kind of dump file, apparently. I could not find details on metalink. I generate the one above in 10.2.0.4 sparc with the following code

create or replace type t1 as object (x number)
/
create or replace type t2 as table of t1
/
exec if t1(1)member of t2()then null;end if

BEGIN if t1(1)member of t2()then null;end if; END;
Error at line 10
ORA-06550: line 1, column 7:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d7ba280 has no MAP method.; _anon__3c929b088__AB[1, 7]]

PLS-00801

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

release

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 $ORACLE_HOME/jdbc/lib/classes12.jar HelloWorld.java

and run

$ java -classpath $ORACLE_HOME/jdbc/lib/classes12.jar:. HelloWorld
Hello World

that’s all folks!

jdbc

You will get CPU and even PSU (Patch Set Updates are proactive cumulative patches comprised of recommended bug fixes that are released on a regular and predictable schedule)

But no more patchset. This the first time I see a release with only 1 pachset.

Well, if you use Linux, you can go to 11gR2. Otherwise you may better stick to 10.2.0.4 for a few more months

Ref: 742060.1
11.1.0.7 is the last patch set for Release 11.1

11.1.0.7

Something I like in ksh is to change from /my/old/directory/path to /my/new/directory/path by typing cd old new. This does not work in bash

So I had to find a workaround

$ ksh
$ cd /app/oracle/product/11.1.0.6/database/rdbms/admin
$ cd 6 7
$ pwd 
/app/oracle/product/11.1.0.7/database/rdbms/admin
$ bash
$ cd 7 6
bash: cd: 7: No such file or directory
$ c() { cd ${PWD/$1/$2}; }
$ c 7 6
$ pwd
/app/oracle/product/11.1.0.6/database/rdbms/admin

bash

In my current project we are going to move from 9i to 10g really soon. One of the typical issue is to get the right privileges, because the schema owners typically had only CONNECT and RESOURCE, and that does no longer include CREATE VIEW in 10gR2.

I was just reading the 11gR2 readme today :
7.2 UNLIMITED TABLESPACE Privilege Changes
The UNLIMITED TABLESPACE system privilege will be removed from the RESOURCE role in a future Oracle Database release (reference Bug 7614645).

So, probably I should ask for TABLESPACE quotas before we go to 12g

unlimited tablespace

This will be a killer in the Oracle forums
listagg

LISTAGG (measure_expr [, 'delimiter_expr'])
  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

for instance

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) FROM emp;
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

stragg

Good news today, you can download Oracle Database 11g Release 2

download

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :

SQL> /
Enter value for n1: 1
old  14:      (SELECT &n1 n
new  14:      (SELECT 1 n
Enter value for n2: 3
old  17:       SELECT &n2 n
new  17:       SELECT 3 n
Enter value for n3: 4
old  20:       SELECT &n3 n
new  20:       SELECT 4 n
Enter value for n4: 6
old  23:       SELECT &n4 n
new  23:       SELECT 6 n
Enter value for result: 49
old 143:              ) = &result
new 143:              ) = 49

result
------------------------------------
(4+3)*(6+1)
(3+4)*(6+1)
(6+1)*(4+3)
(1+6)*(4+3)
(6+1)*(3+4)
(1+6)*(3+4)
(4+3)*(1+6)
(3+4)*(1+6)

8 rows selected.

Elapsed: 00:00:11.28

But for 24 it is not that simple at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2)
   RETURN NUMBER
IS
   x   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;'
               USING OUT x;
   RETURN x;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

I will post the rest of the code as a comment later

riddle

This is one of the thing I hate to see, recursively changing everything to 777

If you want to give read access to all, then 644 is enough for files and 755 for directories. If you want to give execution permission too, you could give 755 to executable files.

Also sometimes you have files with special permission like suid or guid bit (to run as the owner instead of to run as the run user), so it would be best to use relative (go+r) to make the file readable to group and others.

Therefore I prefer relative change. Still there is one thing I do not want, is making every file executable…

Ok, here it is

chmod -R a+rX .

note the big X we change recursively all files and dirs to be readable, and we set the executable flag ONLY IF the file is executable for the owner !

chmod

When you create a table as select (CTAS), you lose a lot of information like grants, partitioning, organization, referential integrity, check constraints. But the NOT NULL constraints remain. Sometimes …

Let’s see when the not null constraints are not copied to the new table.

Here is the test case :

create table lsc_t1(
  c0 number constraint C_PRIMARY_KEY primary key,
  c1 number constraint C_DEFERRABLE not null deferrable, 
  c2 number constraint C_NOVALIDATE not null novalidate,
  c3 number constraint C_DISABLE not null disable,
  c4 number constraint C_DISABLE_VALIDATE not null disable validate, 
  c5 number constraint C_NOT_NULL not null  
  );
create table lsc_t2 as select * from lsc_t1;

let’s describe the tables :

SQL> desc lsc_t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C0                            NOT NULL NUMBER
 C1                                     NUMBER
 C2                                     NUMBER
 C3                                     NUMBER
 C4                            NOT NULL NUMBER
 C5                            NOT NULL NUMBER

SQL> desc lsc_t2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C0                                     NUMBER
 C1                                     NUMBER
 C2                                     NUMBER
 C3                                     NUMBER
 C4                            NOT NULL NUMBER
 C5                            NOT NULL NUMBER

The NOT NULL of c0 has been lost. C0 is the primary key, and the primary key is not transferred to the target table. We can see the non-deferrable validated not-null constraints c4 and c5.

Let’s compare all the constraints :

select * from user_constraints where table_name in ('LSC_T1','LSC_T2');

	LSC_T1	LSC_T2
C0	PRIMARY KEY
C1	DEFERRABLE
C2	ENABLED NOT VALIDATED
C3	DISABLED NOT VALIDATED
C4	DISABLED VALIDATED	ENABLED VALIDATED
C5	ENABLED VALIDATED	ENABLED VALIDATED

The deferrable and not-validated check constraints are lost. The regular ENABLE VALIDATE constraint is ok, but the DISABLE VALIDATE constraint is now enabled.

CTAS, NOT NULL

More than once customers wanted me to tune queries where the LIKE clause made the query very slow…

The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index.

Is it a correct answer? Maybe not.

Let’s imagine my application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
    || ' '
    || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
    ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true)

let’s measure the time for a full table scan

SQL> set timi on
SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';

       ID NAME              BIRTHDATE
--------- ----------------- ---------
    60249 ABCBIFAB KRKBCRN  11-MAR-90
    16714 AF YABCG          09-OCT-95
    55571 BABCIQ GESGLW     27-MAR-50
    77561 BP GABC           24-APR-90
    80027 DALSABC TZLOAWDV  05-NOV-01
    49817 EABCTFIY XWB      10-FEB-88
    23283 EMMOGGBF DABCB    20-DEC-87
    39530 FMABCKB AB        18-SEP-87
    68605 FTPGOHE ABCC      28-SEP-28
    74615 KIFDWABC CSSUQ    08-AUG-82
    31772 KNOABCT BO        08-SEP-77
    68730 KRYIEN LMABC      10-APR-07
    43317 LUFJKZJT AUABCZR  19-DEC-88
    76851 MZABC TEIFG       14-SEP-92
    54589 NXE YABCDX        03-MAY-88
     6940 OIWABCZ DLFFXY    29-MAR-88
    59070 ONIB ADGABCI      29-JUL-07
    27264 PGHOABC ZY        05-OCT-90
    38157 QABC OPZHE        13-JUN-87
    17511 QPDKD CIABCJ      08-AUG-69
    25507 RX OWULOABC       24-FEB-92
    62159 SEABC DAILK       25-JUN-02
     3845 SK CCABCG         22-JAN-80
    50059 SPABC BVHRHW      18-MAR-86
    54700 UABCPC WUHAJS     28-OCT-71
    70207 UKY OIDUABC       23-APR-88
    39484 WABC TJLYHVJZ     14-MAR-78
    14561 WDRWABC XZKDH     29-MAR-86
    61501 YBYU RYABCGI      28-JUN-78
    30578 YEWENGX ABCHARA   12-SEP-67
    35397 YHBEABC HFKO      25-AUG-85
    26450 YOABCVG HJT       23-DEC-98
    87224 ZKNLNY YAABC      13-NOV-61

33 rows selected.

Elapsed: 00:00:02.56

about 3 seconds for retrieving 33 rows out of 100000

let’s try with an index

SQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';

       ID NAME              BIRTHDATE
--------- ----------------- ---------
    60249 ABCBIFAB KRKBCRN  11-MAR-90
    16714 AF YABCG          09-OCT-95
    55571 BABCIQ GESGLW     27-MAR-50
    77561 BP GABC           24-APR-90
    80027 DALSABC TZLOAWDV  05-NOV-01
    49817 EABCTFIY XWB      10-FEB-88
    23283 EMMOGGBF DABCB    20-DEC-87
    39530 FMABCKB AB        18-SEP-87
    68605 FTPGOHE ABCC      28-SEP-28
    74615 KIFDWABC CSSUQ    08-AUG-82
    31772 KNOABCT BO        08-SEP-77
    68730 KRYIEN LMABC      10-APR-07
    43317 LUFJKZJT AUABCZR  19-DEC-88
    76851 MZABC TEIFG       14-SEP-92
    54589 NXE YABCDX        03-MAY-88
     6940 OIWABCZ DLFFXY    29-MAR-88
    59070 ONIB ADGABCI      29-JUL-07
    27264 PGHOABC ZY        05-OCT-90
    38157 QABC OPZHE        13-JUN-87
    17511 QPDKD CIABCJ      08-AUG-69
    25507 RX OWULOABC       24-FEB-92
    62159 SEABC DAILK       25-JUN-02
     3845 SK CCABCG         22-JAN-80
    50059 SPABC BVHRHW      18-MAR-86
    54700 UABCPC WUHAJS     28-OCT-71
    70207 UKY OIDUABC       23-APR-88
    39484 WABC TJLYHVJZ     14-MAR-78
    14561 WDRWABC XZKDH     29-MAR-86
    61501 YBYU RYABCGI      28-JUN-78
    30578 YEWENGX ABCHARA   12-SEP-67
    35397 YHBEABC HFKO      25-AUG-85
    26450 YOABCVG HJT       23-DEC-98
    87224 ZKNLNY YAABC      13-NOV-61

33 rows selected.

Elapsed: 00:00:00.06

Much better

reposted due to % in url

like, tuning

more than one user may wondered who created those BIN$ when they first connected to a 10g database.

create table lsc_t(x number) 
partition by range(x) 
(partition LESS_THAN_ONE values less than (1));

drop table lsc_t;

select object_name, subobject_name, created 
from user_objects 
where object_name like 'BIN$%';
OBJECT_NAME                    SUBOBJECT_NAME  CREATED  
------------------------------ --------------- ---------
BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE   07-JUL-09

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.

purge recyclebin;

select object_name, subobject_name, created 
from user_objects 
where object_name like 'BIN$%';

no rows selected.

select * from recyclebin;

no rows selected.

So far so good…

Let’s see what’s happening with my primary keys

purge recyclebin;
create table lsc_t(x number constraint lsc_t_pk primary key);
drop table lsc_t;
select object_name, original_name, type from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE 
------------------------------ ------------- -----
BIN$bh23ggtBHALgRAAUT+rFpg==$0 LSC_T         TABLE
BIN$bh23ggtAHALgRAAUT+rFpg==$0 LSC_T_PK      INDEX

The primary key index is now in the recycle bin as well.

let’s recover the recycle bin version :

flashback table lsc_t to before drop;
select index_name from user_indexes where table_name='LSC_T';

INDEX_NAME                    
------------------------------
BIN$bh3GgNi1HR3gRAAUT+rFpg==$0

select object_name, original_name, type from recyclebin;

no rows selected.

So if you cannot exclude BIN$ objects from your dba maintenance scripts, you will need to deal with thoses as they may be recovered indexes!

recyclebin

This is asked over and over in the forums, but why not proposing an 11g solution here

create table t(description varchar2(12) primary key, 
  numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION	NUMBERS
PRIME	2,3,5,7
ODD	1,3,5,7,9

Now I want to unpivot numbers in rows

select description,(column_value).getnumberval()  
from t,xmltable(numbers)

DESCRIPTION	(COLUMN_VALUE).GETNUMBERVAL()
PRIME	2
PRIME	3
PRIME	5
PRIME	7
ODD	1
ODD	3
ODD	5
ODD	7
ODD	9

It is that simple

Works also with strings :

select (column_value).getstringval() 
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c

xml

Ftp is seen as an old-time unsecure protocol. Many shops nowadays have switched or are switching to sftp. I will try to point out some differences :

Compatibility: none. the protocol is completly different. Multiple graphical clients however do support both mode. But the basic “ftp” client will not work with sftp.

Ascii mode: only in ftp. In sftp, it is always binary so there will be no conversion. Also no blocksize, recordlength or primary/secondary space for your OS/390 connections.

Interactive mode: similar. you enter your username and password, do cd, put and get. But to quit, by will not work in sftp Use quit or exit instead

Batch mode: different. Most probably you will end up setting a private/public key infrastructure for your ssh connection and use scp (secure copy). If you are using a ssh client like putty, it is possible to do something like pscp -l user -pw password server:file .

Security: sftp is secure, ftp is not.

Speed: ftp is fast, sftp is slow !

Oh NOOOOOOO!!!!! What’s the point is bringing something new if it is slower !!!

Ok, let’s try to download a 100m file:

$ time (echo "open dbsrv01
user oracle secret
bin
get 100m"|ftp -n )

real    0m24.673s
user    0m0.030s
sys     0m0.016s
$ time scp -q oracle@dbsrv01:100m .

real    1m46.978s
user    0m0.108s
sys     0m0.202s

it is about 4x slower! Is there anything we could do about it?

Well, maybe :

$ time scp -q -o Compression=yes oracle@dbsrv01:100m .

real    0m18.634s
user    0m0.748s
sys     0m0.452s

ssh/scp/sftp have a compression mode. If you are transferring your large files across a slow network, this may be an interesting option to consider !

OpenSSH homepage : http://www.openssh.org

ssh

Is it safe to do a TO_NUMBER(TO_CHAR(:n)) ?

var n number
exec :n :=  9.9999999999999999999999999999999999e125
select to_number(to_char(:n)) from dual;
ORA-01722: invalid number

Why?
SQL Reference
If a positive NUMBER value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value

select to_char(:n) from dual;
TO_CHAR(:N)
--------------
~

I wrote more about infinity here

infinity

When and how do you commit a transaction?

SQL> insert into lsc_t(x) values(1);         ... (1) not committed
SQL> commit;                                 ... (1) committed

SQL> insert into lsc_t(x) values(2);         ... (2) not committed
SQL> alter table lsc_t disable primary key;  ... (2) committed

SQL> set autocommit ON
SQL> insert into lsc_t(x) values(3);         ... (3) committed
SQL> set autocommit OFF

SQL> insert into lsc_t(x) values(4);         ... (4) not committed
SQL> disc                                    ... (4) committed

Row 1 is committed after the commit keyword.

Row 2 is implicitely committed after before the alter table ddl statement.

Row 3 is autocommitted. Autocommit exists in sqlplus, but is more often seen in stateless applications (for instance a web application).

Row 4 is committed after a successfull disconnection.

But what appends if you kill your current session?

SQL> insert into lsc_t(x) values (5);

1 row created.

SQL> !ps
   PID TTY      TIME CMD
 13903 pts/33   0:00 ksh
 22384 pts/33   0:00 sqlplus

SQL> !kill 22384
Terminated

The row 5 is not commited

Ref: Overview of Transaction Management

A transaction ends when any of the following occurs:
- A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
- A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER (*).
If the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL statement
as a new, single statement transaction.
- A user disconnects from Oracle Database. The current transaction is committed.
- A user process terminates abnormally. The current transaction is rolled back.

(*) but not ALTER SESSION …

commit

the idea of this post was largely inspired by nuke_y on developpez.net (in French)

I will try to rephrase it in a hierarchical way.

Let’s take all managers of Adams in EMP :

SELECT     empno, ename, mgr
      FROM emp
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
     EMPNO ENAME             MGR
---------- ---------- ----------
      7876 ADAMS            7788
      7788 SCOTT            7566
      7566 JONES            7839
      7839 KING                 

So far so good. Let’s imagine SCOTT has two managers, JONES and PAUL.

create table lsc_t AS
     (SELECT ename, empno, mgr
        FROM emp
      UNION ALL
      SELECT 'SCOTT', 7788, 9999
        FROM DUAL
      UNION ALL
      SELECT 'PAUL', 9999, NULL
        FROM DUAL);
SELECT     empno, ename, mgr
      FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
     EMPNO ENAME             MGR
---------- ---------- ----------
      7876 ADAMS            7788
      7788 SCOTT            7566
      7566 JONES            7839
      7839 KING                 
      7788 SCOTT            9999
      9999 PAUL                 

EMP is not supposed to allow this (primary key on empno), so I created a table LSC_T.

So far still fine, Paul is in my list of managers.

Let’s imagine Scott is the manager of Paul. In 9i and below, this would result in an ORA-0146 CONNECT BY loop in user data, but in 10g, and according to the doc The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.

Should I try ???

UPDATE lsc_t
   SET mgr = 7788
 WHERE ename = 'PAUL';
COMMIT ;
SELECT     empno, ename, mgr
      FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
     EMPNO ENAME             MGR
---------- ---------- ----------
      7876 ADAMS            7788
      7788 SCOTT            7566
      7566 JONES            7839
      7839 KING                 
      7788 SCOTT            9999

Due to a loop in user data, PAUL is no longer returned. I have no explanation so far.

NOCYCLE

It is Friday, I wanted to give my readers some stuff for the week-end

Imagine that piece of code :

countlines() {
  c=$(sqlplus -s /nolog <<EOF | grep -v Connected
connect scott/tiger
set feed off head off
select count(*) from $1;
EOF
)
  echo there are $c lines in $1
}

countlines EMP
countlines DEPT

I can run this

time ./script1
there are 14 lines in EMP
there are 4 lines in DEPT

real    0m0.46s
user    0m0.06s
sys     0m0.09s

Sounds like a regular shell script. How could we optimize the countlines function? Well, we could create the connection only once and use coprocess pipe (with |& that is pipe ampersand)

sqlplus -s /nolog |&

print -p "connect scott/tiger"

read -p line
if [ $line != Connected. ]
then
  exit 1
fi

print -p "set feed off head off"

countlines() {
  print -p "select count(*) from $1;"
  read -p c
  echo "there is $c lines in $1"
}

countlines EMP
countlines DEPT

print -p disconnect

A two-ways pipe is opened with sqlplus. There is only one connect, and one disconnect.

Let’s check the performance :

$ time ./script2
there is 14 lines in EMP
there is 4 lines in DEPT

real    0m0.23s
user    0m0.00s
sys     0m0.01s

About twice as fast! Note the “Connected” output may not exist in recent version of sqlplus in silent mode. If you have a script that generates hundreds of connections, or which create a connection every 5 seconds or so, think about it

Enjoy your week-end

coprosess pipe

In my previous post hide-password-from-ps-output-sql-loader I mentioned a way to pass the password to the loader thru a parameter file. As correctly suggested by Brian Tkatch, the password could be passed as standard input

sqlldr control=x.ctl silent=header,feedback <<EOF
scott/tiger
EOF
Username:

The Username: prompt is displayed  

How do we get rid of this ?

sqlldr control=x.ctl silent=header,feedback <<EOF | grep -v "^Username:"
scott/tiger
EOF

There is no output. But what’s the error code

echo $?
1

The return code is 1

This is not the error code from sqlldr, but the error code from grep !

Ok, here is the trick, a bit cryptic if you are not familiar with file descriptors

( ( (sqlldr control=x <<EOF;echo $? >&3) |grep -v "^Username:" >&4 ) 3>&1 |(read x;exit $x) )4>&1
scott/tiger
EOF
echo $?
0

The return code is 0

return code

By reporting the process status with ps, any Unix user will see the command line arguments

ps -ef 
     UID   PID  PPID  C    STIME TTY      TIME CMD
lsc      13837 13825  0   May 11 pts/17   0:01 -ksh
oracle    4698  6294  0 12:00:40 ?        0:00 sqlplus -s system/manager
appluser  4229  4062  0 12:00:03 ?        0:00 sqlldr scott/tiger
applrun0 28445 28281  0 11:54:03 ?        0:00 imp king/gold full=y
...

What you see here above is definitely a security issue. For sqlplus, the trick is to use sqlplus /nolog and then pass connect system/manager as input or script.

For sqlldr (and exp/imp etc…), the trick is to use a parameter file.

To make it as safe as possible, the file must be unique, readable only for owner and removed after usage.

Ex:

umask 0077
TMPFILE=$(mktemp)
echo "userid=scott/tiger" >$TMPFILE
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback
rm $TMPFILE

mktemp is an Unix utility that creates temp files with unique names.

SQL Loader, temporary file

a google search on oracle and mysql will returns concerns about the future of MySQL. When SUN bought mysql, the enthousiasm was also not unanimous in the opensource community. But now it really need repositioning.

I was just reading : http://www.mysql.com/products/enterprise/unlimited.html
Deploy an unlimited number of MySQL Enterprise Servers for the price of
a single CPU of Oracle Enterprise Edition
Today, there are over 5,000 organizations that spend more than $1Million on
License fees to Oracle, Microsoft, Sybase and IBM. In many cases, that’s money
that doesn’t need to be spent. Not only does open source save money, it
provides an architecture that is more scalable for modern web-based applications.
No wonder open source software has become a strategic initiative for CIOs & CFOs
in many organizations.

Clearly this need repositioning. (For Oracle, money need to be spent )

On the redundant offering MySQL and Oracle Database Express Edition, I could imagine Oracle will get rid of the free edition. Oracle seems to put effort on not releasing any database patch or security patch and the amount of features as well as the cpu/memory limitation are just way to annoying.

This is why I believe MySQL will survive XE.

Pure guess of course…

mysql