Laurent Schneider

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

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 …

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;

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

select 
  timestamp '2008-06-30 23:59:59.999999999 Europe/Zurich'-
  timestamp '1998-02-01 00:00:00 Europe/Zurich' 
from dual;
+000003802 22:59:59.999999999
select (
  timestamp '2008-06-30 23:59:59.999999999 Europe/Zurich'-
  timestamp '1998-02-01 00:00:00 Europe/Zurich' 
  ) year to month 
from dual;
+10-05

This is about the amount of time I spent by my former employer LC Systems. 10 years 5 months (d)

I will tune my book in July and start a new challenge in August

It is quite a while I have not posted about Linux. The reason is I have bought in new notebook three months ago with Vista and did not have a program to resize the partition…

Do I need a program?
NO, I do not !!!

Vista has disk management that can resize my online partition.

Run: compmgmt.msc /s
Or go to control panel.

And shrink/extend your volumes!

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

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

Since yesterday I cannot login to otn and to the forums

Probably I posted to much there

Since today I cannot login to Metalink

What should I expect for tomorrow?
No access to the internet?

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!

Word 2007 allows to write a blog post and I thought I must try

• I used Windows Live Writer to add this comment

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

I just noticed 10.2.0.4 is out. I started downloading 1,916,781 KB !

RTFM is not the best answer …

man crontab
SunOS 5.10          Last change: 10 Nov 2005

User Commands                                          crontab(1)
     A crontab file consists of lines of  six  fields  each.  The
     fields  are  separated by spaces or tabs. The first five are
     integer patterns that specify the following:

     minute (0-59),
     hour (0-23),
     day of the month (1-31),
     month of the year (1-12),
     day of the week (0-6 with 0=Sunday).

so far so good. But read this

     Each of these patterns can be either  an  asterisk  (meaning
     all legal values) or a list of elements separated by commas.
     An element is either a number or two numbers separated by  a
     minus sign (meaning an inclusive range). Time specified here
     is interpreted in the timezone of the cron(1M) daemon, which
     is  set system-wide in /etc/default/init. Entries do not use
     the invoking user's timezone. The specification of days  can
     be  made  by  two  fields  (day  of the month and day of the
     week). Both are adhered to if specified as a  list  of  ele-
     ments. See .

See .

Let’s imagine you want to run a job today, Thursday May 15, 2008 at 2:15pm
You write:
15 14 15 05 4 /tmp/run-my-job

The job will be run Today at 2:15, next year Friday May 15, 2009 at 2:15pm and next week Thursday May 22, 2008 at 2:15pm…

The correct method is to use
15 14 15 05 * /tmp/run-my-job

Specifying both day of week and day of month is not what I expected

warning: the script below is destructive and not 100% safe

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’)) 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

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.

Hey,
This plugin seems to do exactly what I am looking for, enable users to write code in comments.
Preserve Code Formatting

Please test here and give feedback

Warning: this changed the behavior of old posts too, so I have to reformat all older posts to no longer use < in code

Unix users will enjoy this : 10.2.0.4 is available on Solaris and HPUX
patchset 6810189

Still waiting for AIX…

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

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

I have decided to remove disqus comments. What I considered to be a bonus, that is CODE POSTING, appears to be worst than before, because the PRE tag does not work.

The main reason I switched back to last week behavior is that the COMMENT FEED does not work any longer. And I hate changes that annoy my readers.

I will keep looking for a comment plugins that allow posting of code

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)

I just replaced the default wordpress comments by Disqus comments. Disqus enable you to track your comments add picture and other fancy staff.

I have been looking for such a plugin for a very long time (and was too foul to develop it myself).

The amazing bonus with Disqus is that you can post code! Your x<1 will be code in code, not a pseudo html tag annoying more than one of my fellow reader.

Feel free to test it on this post.

Thanks to Yas for making me discover this on : Oracle Today

I hope I will not have have to many issues with this new plugin! Feedback welcome

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

blog.gralike.com

Marco is one of the top expert with XML and Database. Congrats Marco for receiving the Ace trophee

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

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’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like

connect by .1 < 1.
connect by .25 > rownum/100.

with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2″. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.

dburitype(
'/SYS/DBA_PROCEDURES/ROW[PROCEDURE_NAME=''GETKEY'']/OBJECT_NAME'
).getxml()

Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get

"NVL2"(Q'<'>',"UTL_RAW".CAST_TO_VARC
------------------------------------
Oracle Community_Obfuscation_Contest

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' FROM DUAL;
. Ok, let’s look for the string in trace files :

$ cd udump
$ grep LSC-11 *.trc
$
$ grep DUAL$ *.trc
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL

WTF! The tracing mechanism of the application is no longer usable

Another side effect is the length of the column and is described there : http://asktom…P11_QUESTION_ID:3696883368520

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

While Linux x86_64 just came out, Unix Releases like HPUX, AIX and SunSolaris will probably not be available before third week of April.

While Linux x86_64 just came out, Unix Releases like HPUX, AIX and SunSolaris will probably not be available before third week of April.

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 created.
SQL> grant create session to u;
Grant succeeded.
SQL> connect u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect u/u
Connected.

Per default only the proper case works

SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> connect u/U
Connected.
SQL> conn u/u
Connected.

When sec_case_sensitive_logon=false, both uppercase and lowercase passwords work (10g behavior).

When issuing a create user identified by values, you must chose if you want to have both passwords, only the case insensitive or only the case sensitive.

SQL> select password,spare4 from user$ where name='U';

PASSWORD
------------------------------
SPARE4
--------------------------------------------------------------
18FE58AECB6217DB
S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4

The hashes are in USER$.

SQL> alter user u identified by values '18FE58AECB6217DB';
User altered.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4';
User altered.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4;18FE58AECB6217DB';
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When using both hashes, switching back and forth to 11g mechanism is possible.

I will be speaking about SQL Model at Baton Rouge Oracle User Group, Thursday, March 20, 2008 from 11:30am to 1:30pm.

Check http://www.broug.org for details

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 going to use right now!

Enjoy the new interface to read and update your SR

I tried my chance at Le petit jeu des requêtes SQL and yesterday received my price at home.

Thanks to the author for organizing the competition

Next milestone : oraclecommunity.net obfuscation contest

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 of Inf-Inf is Nan.

SQL> select BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
---------------------------------------------
                                          Nan