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

pivot table part 2

One more try with 10gR2

select to_number(column_value) HIREDATE, count(decode(to_number(extract(year from hiredate)), to_number(column_value), 1)) COUNT
emp,xmltable(‘for $i in 1980 to 1990 return $i’ )
group by to_number(column_value)
order by to_number(column_value)
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 3

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.