All posts by Laurent Schneider

Oracle Certified Master

restore to a new host : nid++

Great challenge today: restore to a new host from a closed noarchivelog backup on tape library.

In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because even the MAXDATAFILES and MAXINSTANCES could be changed. Change controlfile is bad. Once a collegue forget one file, once he noticed it, only months later, he wondered how to recover it. Create a controlfile is way to much sensible thing too do. It is almost as bad as changing the dictionary!

Well. How to do than?

Ok, I have a database called LSC67 on prod, I want to recover it to LSC66 in devl. I have NO ACCESS to production, but I have access to tapes (one may wonder if this is a good security practice…)

Let’s start.

First, if LSC66 already exists, shutdown abort. Remove datafiles. Remove controlfiles. Remove redo logs.

Now I restore the data/control/redo files from prod:/dbms/oracle/LSC67 to devl in /dbms/oracle/LSC66.

First I rename the files, some are called systemLSC67.dbf. I do not want that…

find do the trick

find /dbms/oracle/LSC66 -name “*LSC67*” |
nawk ‘{printf “mv “$1″ “; gsub(src,target);print}’ src=LSC67 target=LSC66 |

I must just change the DB_NAME in my parameter file (which already exists at my site), to reflect the prod controlfile dbname

startup quiet force nomount
alter system set db_name=’LSC66′ scope=spfile;
startup quiet force mount

now I generate some statements for dynamically renaming the files

set ver off emb on pages 0 newp 0 lin 9999 trims on head off feedb off termout off
spool /tmp/rename_LSC67_to_LSC66.sql
select ‘alter database rename file ”’||name||”’ to ”’||replace(name,’LSC67′,’LSC66′)||”’;’
from (
select name from v$datafile
union all
select member from v$logfile
where name like ‘%LSC67%’;
spool off
spool /tmp/drop_temp_LSC67_to_LSC66.sql
select ‘alter database tempfile ”’||||”’ drop;’
from v$tempfile tf
where like ‘%LSC67%’;
spool off
spool /tmp/create_temp_LSC67_to_LSC66.sql
select ‘alter tablespace “‘||||’” add tempfile ”’||
”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
from v$tablespace ts , v$tempfile tf
where like ‘%LSC67%’ and tf.ts#=ts.ts#;
spool off

ok, now I am in mount, I do a rename datafile and drop tempfile. after I open database, and add tempfile. I am not taking care of the original size and autoextend clause of the original tempfiles, just 128M next 128M max 2G.

set echo on termout on feedb 6
alter database open;

now I nid

shutdown immediate
startup quiet mount restrict

nid dbname=LSC66 target=/

and I change the db name and open resetlogs

startup quiet force nomount
alter system set db_name=’LSC66′ scope=spfile;
startup quiet force mount
alter database open resetlogs;


I just remembered the woman who asked Lawrence Ellison why you need at least three days to get a qualified support representative when you open an iTar.

Since a few days, I have been wondering why a non-dba user was not able to do sqlplus in 10gR2. Well, I saw the bug 4516865 on metalink for and, but did not agree that it is a “desirable improvement”, imho it is a major lost of service.

Just a few minutes (seconds?) after I finished writing my iTar, severity 2, I received a call from Oracle Support.

Apparently a group of customers asked to prevent any non-dba user from using sqlplus on a database server (just on the client). This kind of answer I do not like. But we had a long talk, he finally showed me a workaround for : doing

chmod -R 755 $ORACLE_HOME


Well, this should have made me hurl. But ok, I can do this.

He also explained me, that I should escalate this bug if I want development starting working on it. Also there is another bug, 4533592

My satisfaction with this tar is good (4/5). I have been contacted immediatly, informed about the bugs, and informed about the procedure to escalate this case when I will require too.

My overall satisfaction with Metalink is about 3/5. But sometimes, it is really 0/5. The worst I have was :

lsc: how do you remove headers in the middle of your result in sqlplus ? I have a bug with set emb on and set pages 9999.
metalink: This is a bug in sqlplus. Workaround is to use Oracle Reports.

I could just have kill her! The “good” solution is “set pages 0″, ref: set pages 50000

One also like this :

lsc: When I try to do an executable in scheduler I got an ora-27371 (AIX)
metalink: switch to a new platfom
lsc: @*#!!
metalink: It will be fixed in
lsc: no it is not fixed in
metalink: it is an internal bug
…months later…
lsc: still not fix in
metalink: development is working on it

well, I have always known that AIX is not a strategic platform and many shell scripts function utterly wrong… still very frustrating one!

Larry just told that woman : just drop me an email. Should I write Larry each time I do receive an idiot answer?


Ref: part 1
I reported this lack of documentation on

Here is my test case (take care, it will create a new db!) :

SQL> startup force quiet nomount;
ORACLE instance started.
SQL> create database controlfile reuse extent management
local default tablespace users default temporary tablespace temp
undo tablespace undotbs1;

Database created.
SQL> @?/rdbms/admin/catalog
SQL> @?/rdbms/admin/catproc
SQL> col username for a10
SQL> col PROFILE for a7
SQL> col LIMIT for a12
SQL> select username, profile, limit from dba_users join 
dba_profiles using (profile) 
where resource_name='FAILED_LOGIN_ATTEMPTS';
---------- ------- ------------ 
SYS        DEFAULT 10
DIP        DEFAULT 10

pivot table part 3

one more try with model, available 10gR1

select * from 
  (select extract(year from hiredate) h, count(*) c 
    from emp 
    group by extract(year from hiredate))
  dimension by (h)
  measures (c)
    c[FOR h FROM 1980 to 1990 INCREMENT 1] = 
    case when c[CV()] is present then c[CV()] else 0 end)
order by h;

         H          C
---------- ----------
      1980          1
      1981         10
      1982          1
      1983          0
      1984          0
      1985          0
      1986          0
      1987          2
      1988          0
      1989          0
      1990          0

pivot table part 1
pivot table part 2

Last day

Very intense last day. In the morning I waked up late, and just went to OTN lounge. Well, I meet Puschitz, which speaks german too because he is austrian, and also Wim. I then ran to Moscone South for my XQuery session. Very interresting indeed…

In the afternoon, pure DBA staff :
1) shared memory
very interresting, difficult staff
2) dss
a lot of nice improvement, but highly soporific presentation
3) storage
great great great presentation from Amit Ganesh, Storage Director at oracle

Than I had a talk with Joel and Arup and a glass of wine in the garden. Than I came in the hotel, I have been kindly invited for a glass of red wine by Barbara and Lisa, two lawyers residing also in the argent hotels, thanks if you read me!

Well, I had to pack, reserve my shuttle, organise wake-up service for 05:00am

tom session + blogger dinner

What a queue for Tom Kyte session! 1000 persons have attended, and apparently a lot more had to wait outside. Very interresting. I am especially seduced by the Online Transportable Tablespace, it was SO BAD to make the tablespaces readonly before transport. I got my signed copy of his book :-)
In the afternoon, I was too much dead to assist some session, so I visited the exhibitors and will come home with plenty of gifts for Dora and Loïc – what a pain to type the ï on an american keyboard, but alt-139 does the trick.
Than OTN/lounge, with a dozen of fans we had the opportunity to ask questions to tom.
On the evening, we had the blogger dinner. I particullary appreciated! Even if I have a terrible french accent, I have been able to chat with my neighbours. The food was good, apart from the sorbet, but the way I pronounced “sorbet” was apparently very amusing!

rac again

I remembered the time in 9iR2 when load/balancing was just dicing between the instance. In 10gR2, I noticed that load balancing is much better. I did a test, start 28 sessions from an external client, 12 went to node 1, 12 to node 2. Very impressive.

In 10gR2 RAC, the listener gets statistics collected by MMON, and balances load much more efficiently…

The Failover is also working correctly. I just powered off Doug PC (we work in team in this lab), and after second try it worked. The TNSNAMES must be configured correctly, with FAILOVER and virtual ip.

I also tried to install RAC/Linux/vmware on my notebook, but I stupidely remove the linux grub boot partition and I cannot boot anymore… I need a doctor soon :-)

timetable for tomorrow

I did my timetable in sqlplus. Quite surprised I have to travel over 15 hours to go there!!! what a trip! I have never made such a long flight in one day!

set head off feedb off
col z for a14
col y for a17
col x for a17 fold_a
alter session set nls_timestamp_tz_format=’DD. HH24:MI TZH:TZM’;

    ” z,
 ‘zurich-sf’ y, ‘sf-zurich’ x,
    ‘flight’ z,
 substr(b-a+d-c,12,5) y,substr(f-e+h-g,12,5) x,
    ‘travel’ z,
 substr(d-a,12,5) y,substr(h-f,12,5) x,
    ” z, ” y, ” x,
    ” z, ‘localtime’ y, ‘zurichtime’ x,
    ‘zurich dep. ‘ z,
 a y, a at time zone ‘Europe/Zurich’ x,
    ‘atlanta arr.’ z,
 b y, b at time zone ‘Europe/Zurich’ x,
    ‘atlanta dep.’ z,
 c y, c at time zone ‘Europe/Zurich’ x,
    ‘SF arr.     ‘ z,
 d y, d at time zone ‘Europe/Zurich’ x,
    ‘SF dep.     ‘ z,
 e y, e at time zone ‘Europe/Zurich’ x,
    ‘atlanta arr.’ z,
 f y, f at time zone ‘Europe/Zurich’ x,
    ‘atlanta dep.’ z,
 g y, g at time zone ‘Europe/Zurich’ x,
    ‘zurich arr. ‘ z,
 h y, h at time zone ‘Europe/Zurich’ x
( select
    timestamp ’2005-02-16 10:30:00 Europe/Zurich’ a,
    timestamp ’2005-02-16 14:55:00 US/East-Indiana’ b,
    timestamp ’2005-02-16 16:40:00 US/East-Indiana’ c,
    timestamp ’2005-02-16 18:29:00 US/Pacific-New’ d,
    timestamp ’2005-02-23 08:21:00 US/Pacific-New’ e,
    timestamp ’2005-02-23 15:44:00 US/East-Indiana’ f,
    timestamp ’2005-02-23 17:25:00 US/East-Indiana’ g,
    timestamp ’2005-02-24 08:30:00 Europe/Zurich’ h
  from dual)

               zurich-sf         sf-zurich
flight         15:14             13:28
travel         16:59             15:09

               localtime         zurichtime
zurich dep.    16. 10:30 +01:00  16. 10:30 +01:00
atlanta arr.   16. 14:55 -05:00  16. 20:55 +01:00
atlanta dep.   16. 16:40 -05:00  16. 22:40 +01:00
SF arr.        16. 18:29 -08:00  17. 03:29 +01:00
SF dep.        23. 08:21 -08:00  23. 17:21 +01:00
atlanta arr.   23. 15:44 -05:00  23. 21:44 +01:00
atlanta dep.   23. 17:25 -05:00  23. 23:25 +01:00
zurich arr.    24. 08:30 +01:00  24. 08:30 +01:00

on my return trip on leave sf at 8:21am and arrive at 8:30am in zurich !

10g OCP

I just passed the OCP 10g new features exam this morning. The prometrics server was down at the beginning, after half an hour, I tried to logon but it was complaining the display support only 0 colors (well, how does a display with 0 color looks like?)

After the support team there changed the resolution from 32bits to 256 colors, it worked.

I did answer correctly to 51 questions

undocumented parameter

Just in case you read my success story on Don Burleson webpage about undocumented parameters.

out of metalink thread 460157.996 :

“I set appropriate values for pga_aggregate_target and _pga_max_size…

alter system set pga_aggregate_target=6G;
alter system set “_pga_max_size”=2000000000;

…and I gave the query some hints “NOREWRITE FULL USE_HASH ORDERED”. As a result, it boosted my query performance from 12 hours to 1.5 hour.”

a few lines below I mentioned :
this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons

I think last sentence is quite interresting, too.

Well, I must say that I finally opted for a more maintenable solution :
no more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 cpus server.

As discussed in the iTar, a supported way to increased the maximum pga memory per single sql query is to increase the degree of parallelism.

As a rule of dumb, if you can avoid hidden parameters, avoid them!

see you soon @ SF


What time is it? I just hope it is soon friday :-)
Friday, I am flying to San Francisco for Oracle OpenWorld
I received this mail for the appreciation dinner : Please let me know if you are available to attend by Monday, September 12. well, I said “fine, see you on monday”. But of course it is on sunday, today is just the deadline, not the dinner.
Also on Mark Rittman Blog, I wrote the otn night is on tuesday, as written on, Tuesday, December 7, 6:00 p.m. – 10:00 p.m. Marriott Hotel, Yerba Buena Level. Of course, it was on December 7 last year, and the link is somewhat misleading… thanks for the reminder Mark, and I will attend to your dinner on tuesday.
Well, I almost had my appreciation dinner on monday, my otn night on tuesday and my blogger dinner on any other day… I definitely need to write down my appointements before going there !

stragg in 10gR2

well, you all know string aggregration

here is a suggestion with xquery in 10gR2

select deptno,
  replace( replace( replace(
    XMLQUERY(‘for $cc in ora:view(“emp”) let $ename:=$cc/ROW/ENAME/text()     where $cc/ROW/DEPTNO/number()=$deptno/d/number() return <e>{$ename}</e>’
    passing by value xmltype(‘<d>’||deptno||’</d>’) as “deptno”
    returning content
  ),’</e><e>’, ‘,’),’<e>’),’</e>’) enames
from dept

—— ——————————————

migrate database with imp exp

I prefer to use exp/imp to migrate databases.

I first create a fresh new database with a new spfile, a new system tablespace, a new undo, locally managed tablespace, automatic segment space management.

I do not do a full exp. I prefer a schema export. It only exports the schema that I want, not WMSYS or PERFSTAT… I do not want to have old stuff in my system tablespace neither.

What is missing by schema import ? profiles, roles, tablespaces, users, public synonym, public database link, privileges

1) generate create statement profiles, roles, tablespaces, users, public synonym, public database link, privileges

for example with toad or with dbms_metadata. I am using sql + spool. Also possible is PLSQL.

2) export database with OWNER=user1,user2,… so all your users but not SYS, SYSTEM, PERFSTAT, DBSNMP, WMSYS, TSMSYS. Only your own users, not the one created by oracle

3) create a new db

4) create profiles, roles, tablespaces, users on the new db

5) grant dba to public !!! yes. a bit creasy, but it is convenient to do the import without warning/errors.

6) import

7) create the public synonym, public database link, privileges

8) revoke dba from public (!)

9) recompile the db

Well, I have written all that in a script, so migrating a db is no longer a problem to me :-) I can do 7.3 –> 10.2 migration. And I am sure my db is clean. I have undo and temporary tablespace. I can have Java or ASM. I have only 10.2 system objects in my 10.2 database. Since I am using exp/imp, it is no problem to change os/server/domain/bitwordsize.

select column only if it exists

i need to display tablespace attributes

SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces;
——————– ——— ——— ——— ———- ——— —— ———– —

looks fine. but what if I try that on my oracle7 database? I will get ORA-00904: invalid column name

ok, I will then select only the columns that exist! than select from dba_tablespaces

set termout off
def logging=””
def extent_management=””
def allocation_type=””
def segment_space_management=””
def retention=””
def bigfile=””
col logging new_v logging
col extent_management new_v extent_management
col allocation_type new_v allocation_type
col segment_space_management new_v segment_space_management
col retention new_v retention
col bigfile new_v bigfile
select ‘,logging’ logging from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’LOGGING’;
select ‘,extent_management’ extent_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’EXTENT_MANAGEMENT’;
select ‘,allocation_type’ allocation_type from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’ALLOCATION_TYPE’;
select ‘,segment_space_management’ segment_space_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’SEGMENT_SPACE_MANAGEMENT’;
select ‘,retention’ retention from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’RETENTION’;
select ‘,bigfile’ bigfile from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’BIGFILE’;
set termout on ver off
select tablespace_name, status, contents &logging &extent_management &allocation_type &segment_space_management &retention &bigfile from dba_tablespaces;

let’s try

SQL> @t
——————– ——— ———

SQL> @t
——————– ——— ——— ——— ———- ———

SQL> @t
——————– ——— ——— ——— ———- ——— ——

SQL> @t
——————– ——— ——— ——— ———- ——— —— ———– —

one script for any version

dynamic number of columns

probably one of the most frequently asked question, you want to have a table like

ANALYST              0          0        2     0
CLERK                1          0        2     1
MANAGER              0          0        1     1
PRESIDENT            1          0        0     0
SALESMAN             0          0        0     4

but the number and name of columns must be dynamic.

typically, I answer : this is not possible in plain sql. you need to use plsql.

well. It is not too much beautifoul, but I have a suggestion

step one:
generate the select

spool crosstab.sql
select text from (
  select 1 i, ‘select job’ text from dual
  union all
  select 2+rownum, ‘, count(decode(deptno,’||deptno||’,deptno)) ‘||dname from
    (select deptno,dname from dept order by dname)
  union all
  select 1e125, ‘ from emp group by job order by job;’ from dual
order by i
spool off

step two:
execute the generated query
——— ———- ———- ———- ———-
ANALYST            0          0          2          0
CLERK              1          0          2          1
MANAGER            1          0          1          1
PRESIDENT          1          0          0          0
SALESMAN           0          0          0          4



Well, the virus scanner did not let me download it 10gR2 Aix on monday. I called the IT-support. Finally, they allowed me to bypass the virus scanner and I managed to download the software + the doc within 10 minutes. Not bad!

Ok, the installer complained my maintenance level to be 5200-03. I upgraded it to 5200-06.

The oracle installer is not my best friend. I have many installations per servers, 7.3.4, 8.0.6, 8.1.7 32bits, 8.1.7 64bits (AIX4), 9.2, 10.1, 10.2… with different patch levels, and I consider installing and deinstalling with runinstaller to be a nightmare.

I did not find the “remove software” functionnality on the 10gR2 installer. Strange!

I created a new db. Got a warning that SQL_TRACE is deprecated, so I removed that parameter and my db is up and running.

pivot table

big mission today : explain the mess with pivot table.

let’s look the following query
SQL> select extract(year from hiredate) year, count(*) from emp group by extract(year from hiredate) order by year
—– —
1980   1
1981  10
1982   1
1987   2

how do we get the years without hiredate with 0?

1) with a table of years
it is fast, and easy to undestand. But it require to create a table

create table year(year number);
insert into year values (1980);
insert into year values (1981);
insert into year values (1982);
insert into year values (1983);
insert into year values (1984);
insert into year values (1985);
insert into year values (1986);
insert into year values (1987);
insert into year values (1988);
insert into year values (1989);

SQL> select year, count(hiredate) COU from emp right join year on (extract(year from hiredate)=year) group by year order by year
—– —
1980   1
1981  10
1982   1
1983   0
1984   0
1985   0
1986   0
1987   2
1988   0
1989   0

2) I have a few artifice to select from dual
A. union
SQL> select year, count(hiredate) COU from emp right join
(select 1980 year from dual
union all select 1981 from dual
union all select 1982 from dual
union all select 1983 from dual
union all select 1984 from dual
union all select 1985 from dual
union all select 1986 from dual
union all select 1987 from dual
union all select 1988 from dual
union all select 1989 from dual)
on (extract(year from hiredate)=year) group by year order by year;

ok for 10 rows, but not for 1000!

B. cube
a trick, which may not work in all versions
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from
(select null from dual group by cube (null,null,null,null))
where rownum

not ok if the number of rows to be generated is volatile, sometimes 1, sometimes 1000000

C. connect by
even more dirty tricky, no guarantee that it will work

SQL> select year, count(hiredate) COU from emp right join
(select 1980+level year from dual connect by level

3) use rownum and all_objects
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from all_objects where rownum

not scalable, you do not know how many rows are in all_objects (500,5000,50000?), and not performant at all, because all_objects is a complex view

4) PL/SQL pipelined table
perfectly scalable, quite fast, requires to create a few objects

SQL> create type t_year as TABLE OF number;

Type created.

SQL> create or replace function f_year(n1 number, n2 number) return t_year pipelined is
begin for i in n1..n2 loop pipe row(i); end loop; return; end;

SQL> select COLUMN_VALUE year, count(hiredate)
from emp right join
table(f_year((select min(extract(year from hiredate)) from emp), (select max(extract(year from hiredate)) from emp)))
on ( COLUMN_VALUE = extract(year from hiredate) )
—– —
1980   1
1981  10
1982   1
1983   0
1984   0
1985   0
1986   0
1987   2

I like the last one, because you can return 1 or 1000000000 rows, the query will not change. It will be fast for 1 row, and slow but will still work for 1000000000 rows.

pivot table part 2
pivot table part 3

group by does not sort

An user just posted an interresting question today. Why the Group By is crazy?

I summarize his example

SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;

Well, group by is “sorting”, but how? this seems crazy. Oracle use the sort algorythm he wants. He can ascending-sort, descending-sort, hash-sort, or any other internal algorythm.

Here, 220000 is before 310 because it is smaller in bytes.

Have a look

SELECT num,dump(num) FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
       NUM DUMP(NUM)
———- ————————-
       400 Typ=2 Len=2: 194,5
    220000 Typ=2 Len=2: 195,23
       310 Typ=2 Len=3: 194,4,11

Well, if you need to sort, use order by and read tom blog

return code

there is a myth of using sql.sqlcode in sqlplus

whenever sqlerror exit sql.sqlcode

this not ok. you should prefer whenever sqlerror exit failure or exit 1

Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88.

Let’s try it

$ sqlplus “/ as sysdba”
SQL> create user gaston identified by lagaffe quota 1k on users default tablespace users;

User created.

SQL> grant create table to gaston;

Grant succeeded.

SQL> whenever sqlerror exit sql.sqlcode
SQL> create table gaston.x as select * from all_objects;
create table gaston.x as select * from all_objects
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’

Disconnected from …
$ echo $?

1536 in binary is 11000000000, so the 8 leftmost bits gives 0, which is definitely not fine in your unix code!

well, there is also a return code from the os, but it stopped working in 10g. it is the _RC defined variable

SQL> host ls /xxx
ls: 0653-341 The file /xxx does not exist.

SQL> def _rc

many users asked “how to get the returned code of a procedure”. Well, this is a faq, a procedure is returning nothing. but you could have an out variable, then exit that code.

SQL> create or replace procedure p(o out number) is begin o:=1; end;
2 /

Procedure created.

SQL> var rc number
SQL> exec p(:rc)

PL/SQL procedure successfully completed.

SQL> exit :rc
Disconnected …
$ echo $?

OpenWorld SanFrancisco

Well, next month I am going to SF for OOW 2K5. I am invited by Oracle to participate to the Meet the expert session (otn underground). I cannot stand waiting to meet my oracle forums pals alive! I am also taking part to the xtreme sessions and I expect them to be xtreme!

It almost 20 years I have not been in the states. I am very excited !

I already installed DB 10gR2 on my notebook. It has been very easy. I first installed as a virtual machine (vmware) RedHat Entreprise Linux 3 update 3. Then Oracle. My collegues did a little bit complain about the 5 giga download, because the whole network was slow. Only 1 cd is necessary for DB installation. Rest is companion, client, clusterware, grid, etc…

I hope I can get a working 10gR2 grid in SF ! Even a beta release would be great :-)

TO_CHAR(…, ‘D’)

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?

With to_char()

alter session set nls_territory=germany;
select to_char(sysdate,’DAY D’) from dual;

With decode()

select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ’1′, ‘TUESDAY’, ’2′, ‘…’)) from dual;

With mod()
As a reference, I take monday Jan 1st, 1000.
select mod(trunc(sysdate)-date ’1000-01-01′,7)+1 from dual;

How do I trunc date to current monday?
trunc(date, ‘D’) or here is with my solution with 1000-01-01:
select trunc((sysdate-date ’1000-01-01′)/7)*7+date ’1000-01-01′ from dual;

thanks to jan-marcel idea, I found that one
trunc(date,’IW’) for current monday and date-trunc(date,’IW’)+1 for day number


if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic.

f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always.

f(y) = sysdate+y is non-deterministic. For a given y, the return values may vary.

OK, we cannot use sysdate in mviews. What else should I say?

Well, some are less evident to find out!

Some limit cases:
to_date(’2000′,’YYYY’) is non deterministic, it returns the 1st of current month, 2000, f. ex. 2000-07-01 or 2000-08-01
to_char(date ’2000-01-01′,’DAY’) is non deterministic, it can deliver SATURDAY or SAMSTAG
to_char(date ’2000-01-01′,’DAY’,’NLS_DATE_LANGUAGE=american’) is deterministic
to_char(date ’2000-01-01′,’D’) is non deterministic, it can deliver 6,7,1 or 2, depending what your territory is (in Bangladesh, the week starts on friday)
timestamp ’2005-10-30 02:30:00 Europe/Zurich’ is per default non deterministic, if you try it in summer, you will get time offset +02:00, and in winter you will get +01:00, because the period 02:00-02:59, Oct 30th, exists in both time zones. This is called time boundaries. I can make it deterministic by setting ERROR_ON_OVERLAP_TIME in the session, in which case boundaries will be rejected.

I can always define a function as deterministic, at my own risk…

For exemple if I have t(id,gender) {1,male;2,female}, I could have a function

f(id)=select gender from t where;

and I could define it as deterministic. However, if I decide to set id=2 for female later, I am prepared to have corrupted data, wrong results, inconsistencies and even ORA-600 errors.

How old are you?

I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age


it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months) to be 18 if I were born Feb 28th, 1990.

exotic constraints

Today I read a post on metalink where the user wanted a unique constraint for not-null values…

Sounds easy, because Oracle never indexes null in btree index.

If I have only one column, I simply index it, it will work.

SQL> create table t66 ( n number);

Table created.

SQL> create unique index i66 on t66(n);

Index created.

SQL> insert into t66 values (null);

1 row created.

SQL> insert into t66 values (null);

1 row created.

The nulls are not indexed. In that post today, the user is using a two column index, and do not want to enforce that constraint when one of the column is null. No problem, we can use FBI to enforce this.

create index i on t(decode(col2,null,null,col1), decode(col1,null,null,col2));

so the index will contain only entries were both columns are not null.

Yesterday a user on wanted to have a not-different constraint, that is was only accepting entries [p;r] if [p;s] does not exist. It is quite hard to solved. I have read an interresting solution using ON-COMMIT-REFRESH materialized view with aggregates and constraints.

A long time ago, one user wanted a constraint “table should contain only one row”.

create unique index i on t(col*0);
would ensure at most one row, with col NOT NULL

I think I can do better.

Imagine the user wants always exactly one row in STATUS(code number)

create table STATUS_MAXONE(code number, n number default 0 primary key);
create view STATUS as select code from STATUS_MAXONE;
insert into STATUS values (null);
create table STATUS_MINONE(n number references STATUS_MAXONE(n));
insert into STATUS_MINONE values (0);

Now the user can update the view STATUS, but neither delete nor insert…

Sometimes, you can also have circular foreign key constraints, for example, a PERSON can only marry with someone who exists in PERSON, that is a PERSON.SPOUSE => PERSON.ID relation. This is also quite special… Actually, a person can only marry his spouse, that means if I am your SPOUSE, you are my SPOUSE! But there is no way to reference a column that may be null (we must reference a primary key).

Using unique index on FBI has the limitation of FBI: function must be deterministic. Same with triggers. complex constraints needs additional tables to enforce your business rules.

Like the p;r; accepting p;r; and q;s; but not p;s;
we could simply have a table containing p;s; with p as primary key…

unexpected results !

It makes you cry! It makes you claim you have found a bug! but it is working as specified!

1) subquery refers to a column of the main query
select * from emp where ename in (select ename from dept where deptno=10);

the query does not complain that column does not exist in dept. It is perfectly legal to specify a non-prefixed column of the main query in the subquery. This could be like

select * from tab where 'foo' in (select 'foo' from dict);

so the “in” clause is always true

2) “not in” does not deliver result

select sysdate from dual where 400 not in (select comm from emp);

this is because 400!=null is UNKOWN. Check the 3 state booleans operations!

it could be rewritten with a not exists, or in 10g with LNNVL

select sysdate from dual where lnnvl(400 in (select comm from emp));

3) rows are not delivered in the correct order
it is a wrong assumption to think that the rows will be delivered in a specific order if you do not specify order by.

4) table not found

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
GreatTable                     TABLE

SQL> desc GreatTable
ORA-04043: object GreatTable does not exist

Whoever it is, someone created a case sensitive table name.

SQL> desc "GreatTable"
 Name                    Null?    Type
 ----------------------- -------- ----------------
 MagicColumn                      NUMBER(38,2)