one more OCE certification

I have passed my Oracle 9i Certified Master exam in 2004. Since then the 10g exam has been in preparation. Well, according to dba10gocm_upgrade the OCM Upgrade exam content has not been finalized. There is also an OCM Member restricted website. In case you have the password, you can read : More Great Benefits Coming […]

Stored outlines

Note: Performance Tuning Guide Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, […]

Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i SQL> create table lsc_t as 2 select 1 parent, 2 child from dual 3 union all select 2,3 from dual 4 union all select 4,5 from dual 5 union all select 5,6 from dual 6 union all select 6,4 […]

what is faster, select count(*) or select count(pk) ?

Oh no, not another post about COUNT(*) and COUNT(1) Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow. SQL> create table t as select rownum id, 2 lpad(‘a’,4000,’a’) a, 3 lpad(‘b’,4000,’b’) b, 4 lpad(‘c’,4000,’c’) c, 5 lpad(‘d’,4000,’d’) d 6 from dual connect by level create […]

read uncommitted

the default isolation level is READ COMMITTED. It means, a session read the committed data. Session 1: SQL> set transaction isolation level read committed; Transaction set. Session 2: SQL> update emp set sal=4000 where ename=’SCOTT’; 1 row updated. Session 1: SQL> select sal from emp where ename=’SCOTT’; SAL ———- 3000 Session 2: SQL> commit; Commit […]

return size of to_char

The width of a columns is known before execution. Well, in most cases… SELECT TO_CHAR(SYSDATE,’DAY’,’NLS_DATE_LANGUAGE=FRENCH’) FROM DUAL; TO_CHAR( ——– SAMEDI The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8. SELECT TO_CHAR(SYSDATE,’YEAR’) FROM DUAL; TO_CHAR(SYSDATE,’YEAR’) —————————————— TWO THOUSAND EIGHT Oracle thinks the length is at most 42 characters. This […]

Epoch

How do I get the current number of milliseconds since 1970-01-01. I still have no access to oracle.com so I created a new account to answer this question. Either you use the difference between timestamp ‘1970-01-01 00:00:00 +00:00’ and current_timestamp, or you can use java, which is more portable. YMMV create function epoch return number […]

Hey Scott, where have you been ?

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone… Ok, let’s recreate the scott schema. C:> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 – Production Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 SYS@lsc02> spool scott.txt SYS@lsc02> drop […]

select distinct collect

I answered a question on otn today about distinct. Reprased, how to select distinct collection? select job, collect(distinct deptno) deptnos from emp group by job; JOB DEPTNOS ——— ——————————————- ANALYST SYSTPTJCzBffh0AjgQ59n0o3QCA==(20) CLERK SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) MANAGER SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10) SALESMAN SYSTPTJCzBffh0AjgQ59n0o3QCA==(30) 5 rows selected. select distinct collect(distinct deptno) deptnos from emp group by […]