Obfuscation contest

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen Ok, I give a try. To make the query unreadable and unformatable I used no space, no new line, and I started by q’

Side effect of cursor sharing

Cursor sharing transform strings in bind variable. So if you do SELECT * FROM EMP WHERE ENAME=’SCOTT’; it will be transformed in SELECT * FROM EMP WHERE ENAME=:sys_b0; This sometimes improved performance of application that do not use binds, for instance. Now let’s imagine your application generates trace files. The application generates a SELECT ‘LSC-11.1011.000.1110.1.0100.000.110’… Continue reading Side effect of cursor sharing

Oracle SQL Obfuscation Contest

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

alter user identified by values in 11g

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… Continue reading alter user identified by values in 11g

Software Configuration Manager

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… Continue reading Software Configuration Manager

take care of minus !

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… Continue reading take care of minus !

Are you really 18 years old today?

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 😀

Oracle 10.2.0.4

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… Continue reading Oracle 10.2.0.4

how to get monday=1, tuesday=2…

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]

Published
Categorized as Blogroll, sql

add a new language to Oracle

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.… Continue reading add a new language to Oracle

milliseconds in alert log

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… Continue reading milliseconds in alert log

What is the lowest and highest possible date in Oracle?

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.

Published
Categorized as Blogroll, sql

Why cannot I use subquery there?

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:… Continue reading Why cannot I use subquery there?

Published
Categorized as Blogroll, sql

select 1.x from t1

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 😕 ?

Published
Categorized as Blogroll, sql

How to resolve ORA-09925 ?

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”… Continue reading How to resolve ORA-09925 ?

my query is not using my index

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)… Continue reading my query is not using my index

Published
Categorized as Blogroll, sql

Oracle 11g Hot patching

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.… Continue reading Oracle 11g Hot patching

predefined collections

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

Published
Categorized as Blogroll, sql

backup bigfile tablespace

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… Continue reading backup bigfile tablespace

Published
Categorized as 11g, Blogroll

Create edition

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… Continue reading Create edition

10.2.0.4 probably not available before 2008

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 😈

random statements

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(*)… Continue reading random statements

Published
Categorized as Blogroll, sql

What is ROWNUM=1 ?

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… Continue reading What is ROWNUM=1 ?

Published
Categorized as Blogroll, sql

OR aggregate

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… Continue reading OR aggregate

Published
Categorized as Blogroll, sql

SQL Model Unconference

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