Author: Laurent Schneider

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

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

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

This will be a killer in the Oracle forums 😉 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

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

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

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

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

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

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

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

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

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) ————– […]

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

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

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

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

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

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

What’s wrong with this code ? Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production With the Partitioning option JServer Release 9.2.0.8.0 – Production SQL> drop table lsc_t ; Table dropped. SQL> create table lsc_t (x varchar2(255), y date); Table created. SQL> begin 2 null; 3 exception when others then 4 insert into lsc_t […]

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun. But how do you get access to your traces without any dba ? One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it […]

http://www.oracle.com/sun I really enjoyed this news. It is both my favorite OS (Solaris) and my favorite database (Oracle) committing to a common future. I have been more than sceptical about Oracle Linux and Oracle Database Machine entering the Datacenter world, but now I feel relieved that Oracle is putting value in acquiring serious database platform […]

I just wanted to give to my readers a bit fun for Eastern… Using the well known EMP table, retrieve the name of the job that is the less common… The solution must be a one-liner SELECT statement (max 80 chars) 🙂 Have fun!

I just read a post from Barry Mc Gillin about apex on MacOsX. After having being using Vista for one year, I would really welcome a Mac as my next notebook. But what about Oracle Database on Mac OS X? According to otn, the latest release as of today is a deprecated 10gR1 for the […]

How do I count all objects in the current schema? SQL*Plus: Release 10.2.0.1.0 – Production on Wed Apr 1 09:58:46 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle 12g Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Expensive option SQL> select 2 user, 3 count(*) 4 from 5 user_objects 6 […]

Last week-end a new release went to production. This week we got plenty of ORA-2049. What in the hell went wrong? And why did not we find out this locking before? We just did not have that concurrency effect in test. How does locking work ? With regular tables: session 1 select * from lsc_t […]

In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function. I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic). To […]

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus. To quickly compare packages in Test and Production I used : select env,name,type,line,text from ( select env,name,type,line,text, count(distinct text) over(partition by name,type,line) […]

Iggy Fernandez posted a riddle to be solved without PLSQL http://www.amazon.com/gp/blog/post/PLNKI2MYB0YCYAUL I tend to second Steven Feuerstein argument : Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. In my book I mentionned techniques that enhance the basis […]

Tanel Poder directed me to Miladin Modrakovic blog, I will definitely add this to my prefered feed. You have to try this : SQL> alter session set events ‘immediate crash’; alter session set events ‘immediate crash’ * ERROR at line 1: ORA-03113: end-of-file on communication channel A very decent method for killing yourself 😈

Today I received an error which was totally new to me. And totally unexpected… For the purpose of this post, I reduced it to a minimum CREATE TABLE LSC_T1 ( x number); CREATE TABLE LSC_T2 ( x number); with c as (select nls_charset_decl_len(1, x) from lsc_t1 join lsc_t2 using (x)) select 1 from c union […]