Laurent Schneider

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

18 is a nice age here. You can vote, you can watch adult 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

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.

I wrote it already :
Laurent Schneider » Blog Archive » TO_CHAR(…, ‘D’)

I thought the one I found out this night was fun

select to_char(sysdate,'DAY',
  'NLS_DATE_LANGUAGE=''numeric date language''') 
from dual;
T
-
3

[edit]this feature is not documented, do not use in prod[/edit]

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. If you do not know about one field, like EBCDIC, just find one in the “Show existing definition…” Dialog

File –> Save as…

You save your nlt file

File –> Tools –> Generate NLB

Three NLB files are generated.

$ ls -l *.nl*
-rw-r--r-- 1 lsc users   934 2008-02-19 18:17 lx003eb.nlb
-rw-r--r-- 1 lsc users  3843 2008-02-19 18:17 lx003eb.nlt
-rw-r--r-- 1 lsc users   128 2008-02-19 18:17 lx0boot.nlb
-rw-r--r-- 1 lsc users   428 2008-02-19 18:17 lx0boot.nlt
-rw-r--r-- 1 lsc users 22528 2008-02-19 18:17 lx1boot.nlb

Copy all your the NLB files to your ORACLE_HOME/data/nls.

Restart your instance.

Let’s test it

select 
  to_char(
    sysdate, 
    'fmday dd month yyyy','NLS_DATE_LANGUAGE=romansch'
  ) 
from dual;
TO_CHAR(SYSDATE,'FMDAYDDMONTHYY
-------------------------------
mardis 19 favrer 2008

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 check in the xml file, I get more info about the execution time, which contains milliseconds

$ adrci exec="show alert -term"|tail -4
2008-02-04 15:52:38.366000 +01:00
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
2008-02-04 15:52:42.101000 +01:00
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;

This timestamp could be really usefull !

There is also a command -tail for adrci, but it outputed the whole log for some unkown reason

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.

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: illegal parameter in SYS_CONNECT_BY_PATH 
function

You cannot use it in the ITERATE or in the RULES clause of model

SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
rules iterate ( (select 1 from dual) ) (y[0]=0);
*
ERROR at line 1:
ORA-32607: invalid ITERATE value in MODEL clause
SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
(y[0]=(select 1 from dual));
*
ERROR at line 1:
ORA-32620: illegal subquery within MODEL rules

Also impossible is in the DATAOBJ_TO_PARTITION function that is used in System Partitioning :

SQL> insert into t partition (
  dataobj_to_partition("T",
    (select :partition_id  from dual) )) 
values ('x') ;
*
ERROR at line 1: 
ORA-14198: rowid column must refer to table 
specified in 1st parameter 

Another documented limitation is the GROUP BY clause :

SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT ;
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT (SELECT COUNT(*) FROM EMP), COUNT(*) 
  FROM DEPT GROUP BY (SELECT COUNT(*) FROM EMP);
*
ERROR at line 1:
ORA-22818: subquery expressions not allowed here

SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT GROUP BY ();

(SELECTCOUNT(*)FROMEMP)   COUNT(*)
----------------------- ----------
                     14          4

I made a funny typo today
SQL> select * from t1;

X
0

SQL> select 1.x from t1;

X
1

Should not I get ORA-904 invalid identifier ?

Eddie Awad just tagged me.

1) I was born in 1971 and have a younger brother who will get married this year
2) I was for the first time in San Francisco in 1986
3) In 1990 I visited India
4) In 1991 I had a motorbike accident where I was quite lucky to break only two arms and one leg.
5) I have learnt to play Chinese chess in Vietnam in 2000. I am able to speak some Vietnamese
6) My family-in-law live in the Bakossi mountains in Cameroon
7) My kids are 4 and 5 years old and speak Swiss German better than me
8) My grand-parents live in their house in Jura. My grand-father is 95 and my grand-mother is 92, I love my grand-mother very much and we just opened a bottle of Champagne last Sunday

My turn to tag - in alphabetic order descending
Chen Shapira
Alan Nolan Davies
Tom Kyte
Kamus
Lutz Hartmann
Marco Gralike
Nicolas Gasparotto
Steven Feuerstein

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification

The 11g OCM is planned for a later date (whatever that means). Well, I am no longer going to wait for the 10g OCM upgrade for 9i OCM… I am not going to do a certification for an older release since 11g exams are available. I will rather do the 11g ocp this year. As I wrote earlier, the 10g OCM was planned for late 2004…

I wish all my readers a very successful 2008

Thanks to Kamus for allowing Chinese readers to access my blog thru his site.

http://fon.gs/ocm ==> http://streams.wisky.org/index.php?q=…

圣诞快乐

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"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -      
root's Password:
# mkdir /bar 
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.

2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable

$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump  
drwxr-xr-x   2 root   dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:29 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump 
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:48 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> quit

3) $ORACLE_HOME/rdbms/audit is not writable

$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:48:09 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit 
drwxr-x---   2 root     dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:49:12 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> quit

I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries

I did not know that. Do I need to use hints?

set autotrace traceonly explain
create table t as 
select 
  sign(rownum-1) r, rpad(rownum,4000,'.') t 
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

select /*+ INDEX(T,I) */ r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787

--------------------------------------------------------------
----------------------

| Id  | Operation                   | Name | Rows  | Bytes | C
ost (%CPU)| Time     |

--------------------------------------------------------------
----------------------

|   0 | SELECT STATEMENT            |      |     1 |  4004 |
1979   (2)| 00:00:24 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |  4004 |
1979   (2)| 00:00:24 |

|*  2 |   INDEX FULL SCAN           | I    |     1 |       |
1979   (2)| 00:00:24 |

--------------------------------------------------------------
----------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<>1)

SQL> select r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------
------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------
------------

|   0 | SELECT STATEMENT  |      |     1 |  4004 |   271K  (1)
| 00:54:18 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   271K  (1)
| 00:54:18 |

--------------------------------------------------------------
------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<>1)