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 […]
Category Archives: Blogroll
Speaking in Stockholm
It’s a great honor for me to be invited to speak in Sweden in December 10-11 ! I am optimistic in getting a few copies of my book to give away 🙂
Flying toasters and dense_rank
Have fun with this caps-lock user question : asktom:Logic behind the DENSE_RANK This is one more statement on how to not use order by like in select ename, deptno, row_number() over (order by 1) from emp order by 2; ENAME DEPTNO ROW_NUMBER()OVER(ORDERBY1) ———- ———- ————————– CLARK 10 1 KING 10 2 MILLER 10 3 JONES […]
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, […]
Oracle SQL Developer Data Modeling
I just downloaded and installed osdm Have a look at my first screen of the ERD generated from my Scott schema
where is my database link listed?
$ sqlplus scott/tiger@DEVL SQL> select * from all_db_links; no rows selected SQL> select * from dual@PROD; D – X Hey, why does this work??? Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is […]
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 […]
Oracle Streams
If you have a datawarehouse and the data are getting to big for a full duplicate or tablespace transport, if you want to experience more about Streams or simply if you are in San Francisco and wants some distraction on Thursday after at 1pm, do not miss Chen session Oracle Streams – Live Demo Oracle […]
About case sensitivity
SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase. SQL> SELECT DUMMY FROM DUAL; D – X SQL> select dummy from dual; D – X Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and […]
Hey, we have a journeyman !
Congrats to Denes Kubicek for being the first journeyman on OTN. OTN stars Justin and Nicolas (18954 and 15321 posts) do not get this title, as we Oracle Ace are sticked to our Ace logos. OTN upgraded the forums recently, many posts about this : OTN Forums – Change the look and feel yourself OTN […]
puzzled by Toad
one colleague just showed me how to insert duplicate rows in a table that has a primary key. create table t(x number primary key);[F5] Table created. insert into t values (1);[F5] 1 row created. insert into t values (1);[F5] 1 row created. Hey, what happened? It took me quite a while to figure out which […]
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 […]
SET LONGCHUNKSIZE
I noticed a side effect of SET LONGC today… The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80. So why not setting it to the maximum? Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE SQL> create […]
certification blog
I just notice a new blog on blogs.oracle.com : Oracle Certification Blog This is probably the right place to ask questions or get news about certification. Out of the many questions I may ask here are just a few I am preparing. – When will the OCM upgrade for 9i OCM be available ? – […]
secondmax
How do I retrieve the second highest salary of emp? There is no right or wrong treatment of duplicates, there are only buggy specifications… There are plenty of ways to do this. An Oracle 7 approach would be SELECT MAX(SAL) FROM EMP, ( SELECT MAX(SAL) MAXSAL FROM EMP) WHERE SAL>MAXSAL; Using TOP-n queries I could […]
Start Oracle in Vista with one click
I have been using Vista for about 3 months and finally found a way to start my databases with a single click 🙂 The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. […]
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 […]
My book is available on amazon.com
One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz. It will ship in December 2008. http://amazon.com/Advanced-Oracle-SQL-Programming-Focus/dp/0977671585
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 […]
DITO
I never used Oracle Web Conference OWC, for security reason. Today I created one SR and read first time about Demo It To Oracle (DITO). This is basically a link CamStudio.org, which is a tool that records AVI file and converts AVI to flash SWF file. This is nice to have because you do need […]
10.2.0.4 aix
I just noticed 10.2.0.4 is out. I started downloading 1,916,781 KB !
drop all objects
warning: the script below is destructive and not 100% safe update: it is unsafe to drop SYS_ objects, check for instance note 579399.1 A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; […]
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 […]
10.2.0.4/Unix
Unix users will enjoy this : 10.2.0.4 is available on Solaris and HPUX patchset 6810189 Still waiting for AIX…
Select bottom rows
Today I answered a question on otn regarding order by. Let’s build a different test case. You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column. SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A; Grant succeeded. SYS@LSC01> […]
alert log in xml format
The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way : SQL> create or replace directory alert as 2 ‘/app/oracle/diag/rdbms/lsc01/lsc01/alert’; Directory created. SQL> var c clob SQL> declare 2 b bfile […]
oradebug tracefile_name
I have enabled tracing in a session and now I want to retrieve the name of the tracefile. Ex: my session has sid 335. How do I retrieve trace file name from sqlplus ? select pid from v$process where addr in (select paddr from v$session where sid=335); PID ———- 47 Now I can use oradebug […]
Welcome Oracle Ace Marco Gralike
blog.gralike.com Marco is one of the top expert with XML and Database. Congrats Marco for receiving the Ace trophee 🙂
To RR or to YY ?
What is worst? To use DD-MON-RR or to use DD-MON-YY? When entering the birthday of my grandfather, I will write it as 31-JUL-1912, so both formats will be fine. If I enter it 31-JUL-12, both formats will be wrong. Ok, which date will I enter now and in the future? For short-time contracts I will […]