Author: Laurent Schneider

Did you ever think you could write something nobody can read ? Submit your query before April 1st to the Oracle SQL Obfuscation Contest… You can also vote there My query : http://www.oraclecommunity…7305

While Linux x86_64 just came out, Unix Releases like HPUX, AIX and SunSolaris will probably not be available before third week of April.

While Linux x86_64 just came out, Unix Releases like HPUX, AIX and SunSolaris will probably not be available before third week of April.

I wrote about dba_users changes in 11g . When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords. When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved. SQL> create user u identified by u; User […]

I will be speaking about SQL Model at Baton Rouge Oracle User Group, Thursday, March 20, 2008 from 11:30am to 1:30pm. Check http://www.broug.org for details

I just noticed today the Web 2.0 interface of metalink : The URL is http://csm.oracle.com, like Coftware Sonfiguration Manager (or maybe Configuration Software Manager). [edit: original name is: Configuration Support Manager] It seems nice, you can customize the portal with drag and drop, their is a blog too http://blogs.oracle.com/supportportal and a feedback button I am […]

I tried my chance at Le petit jeu des requêtes SQL and yesterday received my price at home. Thanks to the author for organizing the competition 🙂 Next milestone : oraclecommunity.net obfuscation contest

Imagine this script (10gR2) : set echo on select BINARY_DOUBLE_INFINITY – BINARY_DOUBLE_INFINITY from DUAL; Run it and you would will get an expected result! SQL> select BINARY_DOUBLE_INFINITY – > BINARY_DOUBLE_INFINITY from DUAL; BINARY_DOUBLE_INFINITY ———————- Inf The issue in sqlplus is that – at the end of line means “query continues next line”. The correct answer […]

18 is a nice age here. You can vote, you can watch movie or drink Gin-tonic. You also get married and go to jail ! Are you really 18 today? select add_months(date ‘1990-02-28’,18*12) from dual; ADD_MONTHS(DATE’199 ——————- 29.02.2008 00:00:00 come back tomorrow 😈 or check an old post of mine How old are you 😀

Sven’s Technik-Blog » Blog Archive » Oracle 10.2.0.4 für Linux x86 ist verfügbar… Patchset 6810189 is now available for Download on Metalink. select * from v$version; BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod PL/SQL Release 10.2.0.4.0 – Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 – Production NLSRTL Version 10.2.0.4.0 […]

I wrote it already : Laurent Schneider » Blog Archive » TO_CHAR(…, ‘D’) I thought the one I found out this night was fun select to_char(sysdate,’DAY’, ‘NLS_DATE_LANGUAGE=”numeric date language”’) from dual; T – 3 [edit]this feature is not documented, do not use in prod[/edit]

I first thought of adding Klingon. Well, finally I added Romansh, which is the fourth official language in my country. Ok, here we go : $ $ORACLE_HOME/nls/lbuilder/lbuilder & The Oracle Locale Builder tool is started. File –> New… –> Language You specify the language, the spelling for January, for Monday, etc… Most fields are mandatory. […]

In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one. $ tail -3 alert*.log Mon Feb 04 15:52:38 2008 ALTER SYSTEM SET recyclebin=’OFF’ SCOPE=SPFILE; ALTER SYSTEM SET recyclebin=’ON’ SCOPE=SPFILE; If I […]

using trunc and round I cannot get lower than -4800 nor higher than 10001 😈 SQL> select trunc(date ‘-4712-1-1′,’CC’) from dual; TRUNC(DATE’-4712-1-1′,’CC’) ———————————- Thursday 1st January 4800 B.C. SQL> select round(date ‘9999-01-01′,’CC’) from dual; ROUND(DATE’9999-01-01′,’CC’) ———————————- Monday 1st January 10001 A.D.

Is there any rule where you can use scalar subquery? You can use a scalar subquery expression in most syntax that calls for an expression (expr). Well, you cannot use it as the second argument of sys_connect_by_path select sys_connect_by_path(ename, (select ‘/’ from dual)) from emp connect by prior empno=mgr; * ERROR at line 1: ORA-30003: […]

I made a funny typo today 🙂 SQL> select * from t1; X 0 SQL> select 1.x from t1; X 1 Should not I get ORA-904 invalid identifier 😕 ?

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early […]

This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual… There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit]. 1) AUDIT_FILE_DEST is not writable $ env _=/usr/bin/env ORACLE_SID=FOO TERM=dtterm ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3 PWD=/u01/app/oracle/product/10.2.0/db_3 $ $ORACLE_HOME/bin/sqlplus -L “/ as sysdba” […]

I read a user question on forums.oracle.com this morning : As we know NOT EQUAL operations by pass indexes and cause full table scans in queries I did not know that. Do I need to use hints? set autotrace traceonly explain create table t as select sign(rownum-1) r, rpad(rownum,4000,’.’) t from dual connect by leveltrue) […]

Online Patching : you can apply or roll back online patches while the RDBMS instance is running 1) download an interim patch for 11g, f.ex. dummy patch 6198642 2) unzip p6198642_111060_LINUX.zip 3) cd 6198642 4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql Invoking OPatch 11.1.0.6.0 Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation. […]

If I need to generate 3 rows called AAA, BBB, CCC, I could use dual and union all. Another method is to use Extensibility Types select * from table (sys.ODCIVarchar2List(‘AAA’,’BBB’,’CCC’)); COLUMN_VALUE ———— AAA BBB CCC

« Ask Tom Live » European Tour 2008  City Date Price Register Madrid 28 – 29 January € 924 ** Click here London 25 – 26 February £1,101 * Click here Berlin 6 – 7 May € 1,474 ** Click here * This price is valid until 31 January 2008 only ** This price is valid […]

One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel. SQL> create bigfile tablespace big datafile size 1g; Tablespace created. RMAN> configure device type disk parallelism 4; using target database control file instead of recovery catalog old RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET; new […]

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage. One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time. Unfortunately, the feature has not been implemented in 11g Release […]

I blogged here that 10.2.0.4 was announced for 2007Q4. Well, I have rechecked metalink and the tentative date for Linux is now 2008Q1 😕 Lutz posted about some 10.2.0.4 Beta tests, I did not even know that some customers got beta version of the metalink patchsets 😈

I played the Chinese Chess Championship in Zurich today. It was a all-play-all tournament with Siegfried Huber (2nd with 1.5/3), Luca Rusconi (2nd with 1.5/3) and Beat Sprenger (4th with 0/3). I finished first with 3 wins out of 3 games. It was an intense day, thanks to Beat for the organization and to Luca […]

I read the ultimate excuse database and checked if I can use model to do generate random statements: with t as ( select ‘%E and %E are travelling to %L.’ fmt from dual union all select ‘About %N employees live in %L.’ from dual) select str from t model reference dept on ( select loc,count(*) […]

Is rownum=1 the first row returned? well, it depends : select rownum, ename from emp order by ename; ROWNUM ENAME ———- ———- 11 ADAMS 2 ALLEN 6 BLAKE 7 CLARK 13 FORD 12 JAMES 4 JONES 9 KING 5 MARTIN 14 MILLER 8 SCOTT 1 SMITH 10 TURNER 3 WARD The ROWNUM is evaluated before […]

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR 1010 (10) 1100 (12) 0110 (6) ========= 1110 (14) Let’s try with t as ( select 10 n from dual union all select 12 from dual union all select 6 from dual) select […]

I have enjoyed speaking at the unconference very much. I chose not to use anything else than a SQL file to demonstrate the capabilities. Thanks to the audience for coming. Here is the SQL file I used for the demo SQLMODEL.SQL

SQL Model – Oracle Wiki My unconference is Thursday at 2pm