Category Archives: Blogroll

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 Soon . . .

Soon=Forever+a few centuries?

According to Paul answer to my comment on his blog : we are developing a one-day upgrade exam for 10g OCM to 11g OCM and will include a streamlined path for 9i OCM to 11g OCM

Ok, I am a bit pessimistic about the future of OCM. As OCM Gregory Guillou posted on my previous blog post :
It doesn’t really make any sense to have an upgrade exam for the what ? 50 9i OCM ?

Since about last year there is a way more succesful exam suite. The Oracle Certified Expert. I did myself pass the rac and sql ones.

Ok, the news :
There is a new Oracle Certified Expert exam which is called : Oracle Database 11g Performance Tuning Certified Expert

The exam is in Beta now, good luck to you!

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              20                          4
FORD               20                          5
ADAMS              20                          6
SMITH              20                          7
SCOTT              20                          8
WARD               30                          9
TURNER             30                         10
ALLEN              30                         11
JAMES              30                         12
BLAKE              30                         13
MARTIN             30                         14

According to the doc, order by position is invalid. Actually, order by 1 is treated as order by ‘bananas’.
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

Thanks Tom for being so funny :)

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, which offer superior SQL performance and stability compared with stored outlines.


This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’.

SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.


SQL> create or replace outline o for category emp_scott on 
  select empno from emp where ename='SCOTT';

Outline created.

SQL> create unique index i on emp(ename) 
  tablespace my_slow_tape_drive;

Index created.

SQL> set timi on
SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Elapsed: 01:45:59.95

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
   2    1     INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1 

Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older ;) ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline

 
SQL> alter session set use_stored_outlines=emp_scott;

Session altered.

Elapsed: 00:00:00.00
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text 
  from v$sql 
  where sql_text like 'select%SCOTT%';
LAST_LOAD_TIME      OUTLINE_C SQL_TEXT
------------------- --------- ----------------------------
2008-10-16/09:42:30           select empno from emp where 
2008-10-16/09:46:50 EMP_SCOTT select empno from emp where 

The plan using the outline is now used

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 just to annoy more ;)


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
PROD.LAURENTSCHNEIDER.COM

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 from dual;

Table created.

SQL> select parent,child
  2  from lsc_t
  3  where level=50
  4  connect by parent=prior child
  5  and level<=50
  6  and prior dbms_random.value != dbms_random.value;

    PARENT      CHILD
---------- ----------
         5          6
         6          4
         4          5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose :mrgreen:

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 OpenWorld Unconference

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 columns.


/* Formatted on 2008/09/19 10:00 
(Formatter Plus v4.8.8) */
SELECT dummy
  FROM DUAL;

Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
                    *
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X

OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!


SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

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 Forums Update – Take Two
  • New OTN Forums – not great yet
  • OTN forums get a new look
  • OTN Forums Upgrade
  • Forums.oracle.comUpgrade: New Rewards System/Known Issues
  • In the last link, Justin explains the new ranking system. Aces are aces, and everyone else but Denes are newbies ;)

    Ow, I forget, they got smileys too

    Happy :)
    Sad :(
    Grin :D
    Love :x
    Mischief ;\
    Cool B-)
    Devil ]:)
    Silly :p
    Angry X-(
    Laugh :^0
    Wink ;)
    Blush :8}
    Cry :_|
    Confused ?:|
    Shocked :0
    Plain :|

    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 feature of Toad was “allowing” this.

    Let’s look at Toad.ini (or View – ToadOptions – Transaction)

    
    [SETTINGS]
    ALWAYSROLLBACK=1
    THREADQUERIES=1
    

    Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

    Let’s prove it :

    
    select count(*) from t where x=1;[F5]
    
      COUNT(*)
    ----------
             0
    
    1 row selected.
    

    :mrgreen:

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

    Oh no, not another post about COUNT(*) and COUNT(1) :mrgreen:

    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<10000;
    
    Table created.
    
    SQL> create index i on t(id);
    
    Index created.
    
    SQL> alter table t add primary key (id) 
      2    using index i disable novalidate;
    
    Table altered.
    
    SQL> exec dbms_stats.gather_table_stats(user,'T')
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_stats.gather_index_stats(user,'I')
    
    PL/SQL procedure successfully completed.
    
    SQL> set timi on
    SQL> select count(id) from t;
    
     COUNT(ID)
    ----------
          9999
    
    Elapsed: 00:00:00.01
    SQL> select count(*) from t;
    
      COUNT(*)
    ----------
          9999
    
    Elapsed: 00:00:01.43
    

    My count(*) just takes too long… Why that?

    I have no index on a not null column. The primary key is disabled.

    Of course the count(pk) does not count the rows with pk=null, but it is faster :)

    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 complete.
    

    Session 1:

    
    SQL> select sal from emp where ename='SCOTT';
    
           SAL
    ----------
          4000
    SQL> update emp set sal=3000 where ename='SCOTT';
    
    1 row updated.
    
    SQL> commit;
    Commit complete.
    

    When the session 1 reads the salary of Scott, it gets the value that is committed in the database.

    Another isolation level is SERIALIZABLE.

    Session 1:

    
    SQL> set transaction isolation level serializable;
    
    Transaction set.
    

    Session 2:

    
    SQL> update emp set sal=5000 where ename='SCOTT';
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    

    Session 1:

    
    SQL> select sal from emp where ename='SCOTT';
    
           SAL
    ----------
          3000
    
    SQL> update emp set sal=sal+1;
    update emp set sal=sal+1
    *
    ERROR at line 1:
    ORA-08177: can't serialize access for this transaction
    SQL> roll
    Rollback complete.
    SQL> select sal from emp where ename='SCOTT';
    
           SAL
    ----------
          5000
    
    SQL> update emp set sal=3000 where ename='SCOTT';
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    

    In session 1, the isolation level of the transaction is set to SERIALIZABLE. Session 2 update the salary of Scott to 5000 and commits. The session 1 therefore does not read committed data and any tentative to change the committed data will fail. Roll[back;] ends the transaction. The session 1 can then read committed data and update the salary to 3000.

    Ok, let’s imagine you have to interview an OCM and you want to ask him a very difficult question ;) :

    - Is it possible in Oracle to read uncommitted data from another session?

    Let’s try :)

    Session 1:

    
    SQL> var rc number
    SQL> set autop on
    SQL> select sal from emp where ename='SCOTT';
    
           SAL
    ----------
          3000
    
    SQL> exec :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1), 
      DBMS_XA.TMNOFLAGS)
    
    PL/SQL procedure successfully completed.
    
            RC
    ----------
             0
    
    SQL>
    SQL> UPDATE emp SET sal=6000 WHERE ename='SCOTT';
    
    1 row updated.
    
    SQL> exec :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), 
      DBMS_XA.TMSUSPEND)
    
    PL/SQL procedure successfully completed.
    
            RC
    ----------
             0
    

    Session 2:

    
    SQL> select sal from emp where ename='SCOTT';
    
           SAL
    ----------
          3000
    
    SQL> var sal number
    SQL> var rc number
    SQL> set autop on
    SQL> begin
      :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),DBMS_XA.TMRESUME);
      SELECT SAL INTO :SAL FROM EMP WHERE ENAME='SCOTT';
      :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), DBMS_XA.TMSUCCESS);
      :rc:=DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(1));
    end;
    /
    
    PL/SQL procedure successfully completed.
    
           SAL
    ----------
          6000
    
            RC
    ----------
             0
    
    SQL> select sal from emp where ename='SCOTT';
    
           SAL
    ----------
          3000
    

    So yes, you can read uncommitted data in a global transaction from another session. But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

    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 is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.

    SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
    TO_CHAR(SYSDATE,'JSP')
    ---------------------------------------------------------
    ---------------------
    TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED 
    EIGHTY-EIGHT
    

    again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.

    let’s try with spelling the nanoseconds :

    
    select to_char(current_timestamp,'FF9SP') from dual;
    TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
    -------------------------------------------------------
    -----------------------
    SEVENTY-FOUR MILLION
    

    here 78 is a really bad guess… the nanoseconds could be 100 character long !

    What happened then :

    
    select to_char(timestamp '0001-01-01 00:00:00.777777777',
      'FFSP') from dual;
    select to_char(timestamp '0001-01-01 00:00:00.777777777',
                   *
    ERROR at line 1:
    ORA-01877: string is too long for internal buffer
    

    The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!

    It simply means Oracle sets the width column to something too small to contain the result.

    By setting the column to something longer, the query should work…

    
    select cast(to_char(timestamp '0001-01-01 00:00:00.
      777777777','FFSP') as varchar2(100)) from dual;
    CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
    ------------------------------------------------------------
    ASVARCHAR2(100))
    ----------------------------------------
    SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
    SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN
    

    Ok, let’s do something perverse :mrgreen:

    Let set the CURSOR_SHARING to FORCE !

    Have a look :

    
    SQL> alter session set cursor_sharing='FORCE';
    
    Session altered.
    
    SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
    TO_CHAR(TO_DATE(1000000,'J'),'JSP')
    ----------------------------------------------------------
    -----------------
    ONE MILLION
    

    the width of the column is evaluated to 75. 75 however is not the maximum length!

    
    SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
    select to_char(to_date(1777777,'J'),'JSP') from dual
           *
    ERROR at line 1:
    ORA-01801: date format is too long for internal buffer
    

    Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.

    Let’s revert to EXACT for confirmation.

    SQL> alter session set cursor_sharing='EXACT';
    
    Session altered.
    
    SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
    TO_CHAR(TO_
    -----------
    ONE MILLION
    
    SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
    TO_CHAR(TO_DATE(1777777,'J'),'JSP')
    --------------------------------------------------------------
    --------------
    ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED 
    SEVENTY-SEVEN
    

    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 view v as select 
    '12345678910111213141516171819202' x 
    from dual;
    
    View created.
    
    SQL> set lin 80 long 20 longc 20
    SQL> select text from user_views where view_name='V';
    
    TEXT
    --------------------
    select '123456789101
    
    SQL> set lin 80 long 1000000000 longc 20
    SQL> select text from user_views where view_name='V';
    
    TEXT
    --------------------
    select '123456789101
    11213141516171819202
    ' x from dual
    
    SQL> set lin 80 long 1000000000 longc 1000000000
    SQL> select text from user_views where view_name='V';
    
    TEXT
    -----------------------------------------------------
    select '12345678910111213141516171819202' x from dual
    

    When I use a LONG setting smaller than the length of the TEXT column, I got it truncated. When I use a huge LONG setting but a LONGCHUNKSIZE setting smaller than the length of the TEXT column, I got it wrapped. When both are huge, it seems I am getting the expecting result. So why not setting SET LONG 2000000000 LONGC 2000000000 in your login.sql ?

    Have a look :

    
    SQL> SET LONG 10 LONGC 10 LIN 80 TIMI ON
    SQL> SELECT TO_CLOB('X') FROM DUAL;
    
    TO_CLOB('X
    ----------
    X
    
    Elapsed: 00:00:00.01
    SQL> SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
    SQL> SELECT TO_CLOB('X') FROM DUAL;
    
    TO_CLOB('X')
    ------------
    X
    
    Elapsed: 00:00:00.00
    SQL> SET LONG 1000000000 LONGC 1000000000 LIN 80 TIMI ON
    SQL> SELECT TO_CLOB('X') FROM DUAL;
    
    TO_CLOB('X')
    ------------
    X
    
    Elapsed: 00:00:02.06
    

    2 seconds to select 1 character from dual !

    I also tried to set it to 2000000000 but since I do not have 2Gb of free memory my notebook started swapping so badly I could not even move my mouse :(

    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 ?
    - When will be the 9i exams retired ?
    - ocm upgrade : 10g upgrade has not been finalized yet. Why ?
    - What is the expected date for 11g OCM ?

    Ok, I have add this blog to my feed and will start posting comments soon …

    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 use

     
    SELECT SAL 
    FROM (
      SELECT ROWNUM R,SAL FROM (
        SELECT SAL FROM EMP ORDER BY SAL DESC)) 
    WHERE R=2;
    

    Using ROW_NUMBER (or RANK or DENSE_RANK), I could use

    
    SELECT SAL 
    FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) R,SAL FROM EMP) 
    WHERE R=2;
    

    Another approach would be to create a SecondMax aggregate function, as the following example from the doc
    Example 11-12 How to Create and Use a User-Defined Aggregate Function

    SELECT SecondMax(sal)
    FROM emp;

    Ok, if I know I have 14 employees in EMP, retrieving the second highest is way easier :)

    
    SELECT PERCENTILE_DISC(2/14) WITHIN GROUP (ORDER BY SAL DESC) 
    FROM EMP;
    

    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. Way to much…

    I have googled quite a lot and found Vista setuid – How to elevate without prompting

    Ok, here we go :

    1) run mmc
    2) file – add snapin
    3) Task Scheduler Library – Create Task
    4) Name: startoracle
    Run with highest privileges
    Actions – New – Start a program – Program:net – Arguments: start OracleOraDb11g_home1TNSListener
    Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC01
    Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC02

    5) create a batch file that runs : schtasks /run /tn startoracle

    Same for stoporacle

    It seems to work fine. Do not play too much with mmc if you do not know it. It is a powerful tool :!:

    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 as language java name 
      'java.lang.System.currentTimeMillis() return int';
    / 
    select epoch from dual;
               EPOCH
    ----------------
       1214562599878
    

    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 user scott cascade;
    User dropped.
    SYS@lsc02> @?/rdbms/admin/utlsampl
    Disconnected from Oracle Database 10g Enterprise Edition
    
    C:> sqlplus scott/tiger
    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
    SCOTT@lsc02> select ename from emp;
    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    KING
    TURNER
    JAMES
    FORD
    MILLER
    
    12 rows selected.
    

    Hey Scott, I miss you!

    Let’s see if I can dig out something in the log file I created with spool scott.txt

    
    (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
      'dd-mm-rr')-85,3000,NULL,20)
    ERROR at line 2:
    ORA-01858: a non-numeric character was found 
      where a numeric was expected
    

    Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
    reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
    NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

    Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
    C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
    /v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
    Value NLS_LANG exists, overwrite(Yes/No)? yes
    The operation completed successfully.
    C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
    NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

    and retry

    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> @?/rdbms/admin/utlsampl
    Disconnected from Oracle Database 10g Enterprise Edition
    
    C:>sqlplus scott/tiger
    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
    SCOTT@lsc02> select ename from emp;
    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    
    14 rows selected.
    

    Welcome back Scott!

    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 to grant access to your desktop to Oracle Support and you can record what’s happening on your screen !

    Let’s have a look

    defaultsysdate.swf

    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; was suggested as an easy answer, but I commented that create user must re-grant quotas, roles, system privileges, table privileges. Create user must also set the correct security status of the account (password, case-sensitive password, lock status, expiration status, profile), and the password history and failed login history is lost. Also it must set the default and temporary tablespaces.

    It is also important to note that “drop user” requires dba privileges, when dropping the own object does not require privileges

    To drop all objects I tried with plsql and a dictionary loop.

    select object_type, count(*) 
    from user_objects 
    group by object_type;
    
    OBJECT_TYPE           COUNT(*)
    ------------------- ----------
    INDEX                        6
    TYPE BODY                    1
    INDEXTYPE                    1
    PROCEDURE                    1
    JAVA CLASS                   2
    JAVA RESOURCE                1
    JAVA SOURCE                  1
    FUNCTION                     1
    TABLE                       10
    TRIGGER                      1
    TYPE                         1
    MATERIALIZED VIEW            1
    DATABASE LINK                1
    PACKAGE BODY                 1
    CLUSTER                      1
    DIMENSION                    1
    OPERATOR                     1
    SEQUENCE                     1
    PACKAGE                      1
    
    19 rows selected.
    
    purge recyclebin;
    
    Purge successfully completed.
    

    begin
    for f in (
    select object_type, object_name from user_objects
    where object_type in (
    ‘MATERIALIZED VIEW’)) loop
    execute immediate
    ‘drop materialized view “‘||f.object_name||’” preserve table’;
    end loop;
    for f in (
    select table_name from user_tables) loop
    execute immediate
    ‘drop table “‘||f.table_name||’” cascade constraints’;
    end loop;
    for f in (
    select object_type, object_name from user_objects
    where object_type in (
    ‘DIMENSION’,’CLUSTER’,’SEQUENCE’,
    ‘VIEW’,’FUNCTION’,’PROCEDURE’,
    ‘PACKAGE’,’SYNONYM’,’DATABASE LINK’,
    ‘INDEXTYPE’)
    and object_name like ‘SYS_%$’) loop
    execute immediate ‘drop ‘||
    f.object_type||’ “‘||f.object_name||’”‘;
    end loop
    for f in (
    select object_type, object_name from user_objects
    where object_type in (
    ‘JAVA SOURCE’)) loop
    execute immediate ‘drop ‘||
    f.object_type||’ “‘||f.object_name||’”‘;
    end loop;
    for f in (
    select object_type, object_name from user_objects
    where object_type in (
    ‘JAVA RESOURCE’)) loop
    execute immediate ‘drop ‘||
    f.object_type||’ “‘||f.object_name||’”‘;
    end loop;
    for f in (
    select object_type, object_name from user_objects
    where object_type in (
    ‘JAVA CLASS’)) loop
    execute immediate ‘drop ‘||
    f.object_type||’ “‘||f.object_name||’”‘;
    end loop;
    for f in (
    select object_type, object_name from user_objects
    where object_type in (
    ‘TYPE’,’OPERATOR’)) loop
    execute immediate ‘drop ‘||
    f.object_type||’ “‘||
    f.object_name||’” force’;
    end loop;
    end;
    /

    PL/SQL procedure successfully completed.

    
    select * from user_objects
    
    no rows selected.
    

    All objects of the current schema disappeared, do not try this as sys, this script is destructive with no confirmation

    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 job;
    
    ERROR at line 1:
    ORA-22950: cannot ORDER objects without MAP or ORDER method
    

    the message is clear, i need a MAP or ORDER method. Ok, I can do this

    
    create type tt_n as table of number(2);
    /
    
    Type created.
    
    create type tt_n_ord as object(
      c tt_n,
      order member function 
        eq (tc tt_n_ord) return integer);
    /
    
    Type created.
    
    create type body tt_n_ord as
      order member function 
        eq (tc tt_n_ord) return integer
      is
      begin 
        if c=tc.c 
        then return 0; 
        else return 1; 
        end if;
      end;
    end;
    /
    
    Type body created.
    
    select distinct 
      tt_n_ord(cast(collect(distinct deptno) as tt_n))
    from emp group by job;
    
    TT_N_ORD(CAST(COLLECT(DISTINCTDEPTNO)ASTT_N))(C)
    ------------------------------------------------
    TT_N_ORD(TT_N(30))
    TT_N_ORD(TT_N(10))
    TT_N_ORD(TT_N(10, 20, 30))
    TT_N_ORD(TT_N(20))
    
    4 rows selected.

    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> GRANT SELECT ON SCOTT.EMP TO USER_A WITH GRANT OPTION;
    
    Grant succeeded.
    
    SYS@LSC01> CREATE VIEW USER_A.V AS SELECT ENAME FROM SCOTT.EMP 
    ORDER BY HIREDATE;
    
    View created.
    
    SYS@LSC01> GRANT CREATE SESSION TO USER_B IDENTIFIED BY B;
    
    Grant succeeded.
    
    SYS@LSC01> GRANT SELECT ON USER_A.V TO USER_B;
    
    Grant succeeded.
    
    SYS@LSC01> CONNECT USER_B/B
    Connected.
    USER_B@LSC01> SELECT ROWNUM, ENAME FROM USER_A.V WHERE ROWNUM<4;
        ROWNUM ENAME
    ---------- ----------
             1 SMITH
             2 ALLEN
             3 WARD
    

    To get top 3, that was easy 8)
    
    USER_B@LSC01> SELECT R, ENAME FROM
      2    (SELECT ROWNUM R,ENAME
      3    FROM USER_A.V
      4    ORDER BY ROWNUM DESC)
      5  WHERE ROWNUM<4 ORDER BY R;
             R ENAME
    ---------- ----------
            12 MILLER
            13 SCOTT
            14 ADAMS
    

    Here we have a sorted view. The OP had an external file. It could be an XML input, or any table or collection that is sorted but does not have a sort key

    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 := bfilename('ALERT','log.xml');
      3  begin
      4     dbms_lob.open(b,dbms_lob.file_readonly);
      5     dbms_lob.createtemporary(:c,true);
      6     dbms_lob.loadfromfile(:c,b,dbms_lob.lobmaxsize);
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select extractvalue(xmlparse(content :c),
      2    '/msg[@time="2008-03-30T01:01:13.704+01:00"]/txt/text()')
      3  from dual;
    EXTRACTVALUE(XMLPARSE(CONTENT:C),'/MSG[@TIME="2008-03-30T01:01:1
    ----------------------------------------------------------------
    
    Starting ORACLE instance (normal)
    

    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 to reveal tracefile name

    
    SQL> oradebug setorapid 47
    Unix process pid: 1372408, image: oracle@dbsrv01 (TNS V1-V3)
    SQL> oradebug tracefile_name
    /u01/app/oracle/admin/LSC01/udump/lsc01_ora_1372408.trc
    

    Read valuable information about oradebug on this site, amoung others

    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 enter dates like 01-AUG-08 or 31-DEC-11, both formats will be fine. For long time contracts like retirement saving plan I will enter 31-MAR-36, which is still fine, because I am old enough :mrgreen: Juniors in my company will enter dates like 30-JUN-52, which will be fine with YY and wrong with RR or RRRR.

    Ok, what is the recommended format then?

    DD-MM-FXYYYY is probably fine. FX forces you to enter 4-digits years.

    
    SQL> select
      2    to_date('31-JUL-1912','DD-MON-YY') YY1912,
      3    to_date('31-JUL-12','DD-MON-YY') YY12,
      4    to_date('31-JUL-12','DD-MON-RR') RR12
      5  from dual;
    
    YY1912     YY12       RR12
    ---------- ---------- ----------
    1912-07-31 2012-07-31 2012-07-31
    
    SQL> select
      2    to_date('31-MAR-2036','DD-MON-YY') YY2036,
      3    to_date('31-MAR-36','DD-MON-YY') YY36,
      4    to_date('31-MAR-36','DD-MON-RR') RR36
      5  from dual;
    
    YY2036     YY36       RR36
    ---------- ---------- ----------
    2036-03-31 2036-03-31 2036-03-31
    
    SQL> select
      2    to_date('30-JUN-2052','DD-MON-YY') YY2052,
      3    to_date('30-JUN-52','DD-MON-YY') YY52,
      4    to_date('30-JUN-52','DD-MON-RR') RR52
      5  from dual;
    
    YY2052     YY52       RR52
    ---------- ---------- ----------
    2052-06-30 2052-06-30 1952-06-30
    
    SQL> select
      2    to_date('8/4/2008','DD-MM-FXYYYY') FXYYYY2008
      3  from dual;
    
    FXYYYY2008
    ----------
    2008-04-08
    
    SQL> select
      2    to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
      3  from dual;
      to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
              *
    ERROR at line 2:
    ORA-01862: the numeric value does not match the length 
    of the format item
    
    SQL>