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

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

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

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

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

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

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

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.

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

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

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

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