FAILED_LOGIN_ATTEMPTS part 2

Ref: part 1
I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359

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';
USERNAME   PROFILE LIMIT 
---------- ------- ------------ 
SYSTEM     DEFAULT 10
SYS        DEFAULT 10
TSMSYS     DEFAULT 10
DIP        DEFAULT 10
DBSNMP     DEFAULT 10
OUTLN      DEFAULT 10

FAILED_LOGIN_ATTEMPTS default to 10 in 10gR2

I just noticed FAILED_LOGIN_ATTEMPTS now defaults to 10 in 10gR2. Not found in the doc. I will report it tomorrow in the documentation feedback on otn

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))
model
  dimension by (h)
  measures (c)
  rules(
    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
from
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 whine 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

home sweet home

I had a very long travel. the queue for checking was even longer (in time) than the one for larry ellison and this SFO airport is terribly badly organised...

The seats in the economy classes were ridiculously small, just impossible to sit correctly. I arrived on time in Atlanta. But then the troubles begin. Wrong plane waiting on the wrong gate, baggage container electric door defect on the new assigned airplane, which took about one hour to close in Atlanta, and one hour to open at sf. Not too mention the hot cup of tea the stewardess put on my jeans. In zurich, 2 hours late, I took the wrong train and finally exited in the middle of nowhere and took a taxi to come back home.

Well, completely tired, but so happy to see my family ! Well, it is 19:36 localtime, 10:36 pacific time, kids just do not want to sleep.

double party

What a show! I have been waiting about 1 hour to come in to show larry. I think it was a good one. But I am not a big fan of keynotes. He answered questions after his talk, a few of them were interressant :
- how do you license multicore cpu?
Larry just answered the way Oracle does licensing is not the correct way, he would prefer a license proportional to the size and the revenue of the company (will a very poor company get everything almost free or does oracle want to earn more from the big companies?). Well, the "processor" license question has not been answered
- how do you see your chance in the cup of america?
well, larry, say what you like, you have NO CHANCE against Switzerland !
- Why is oracle support is taking so long time to understand my iTar?
Larry proposed the woman to write him a note. The question was definitely tricky, and he did not too bad, but the problem remains...

Without pause, I went to Bryn session about PL/SQL. Impressed but too tired to understand everything.

I have been playing a few games in OTN. Than I went to Oreilly and Steven Party to drink a few beers and meet very interresting people. I talked and got great hints from Mark Gurry. I have played -and lost- five games of pool with the people there.

Later I went to the big party. I walked until market with Bryn, asked him to explain me some details about his session, than took a taxi to peer 32. The fireworks were beautifoul, it was very crowdy, and I had a nice talk with a fireworks engineer ;-) I took a bus which dropped me somewhere between the 7th and Market at midnight, I meet bruce lee with a flashing sun microsystems badge in the hotel lift, which did not appreciate me much, but I managed to come safe home.

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!

pizza

I had a nice monday. In the morning, after Oracle President and Intel Presentation I attend Steven Feuerstein session. Very Interresting indeed. I just realised I know nothing about PLSQL. Later in the afternoon, I had a few chessgames and reversi with Steven. Thanks god, I am better at chess than at plsql ;-)

I also meet Justin from OTN. I meet Mark from OTN, we had a deep talk about the feedback forum, I told him that my correction on OTN document feedback have been forwarded but never corrected and he is going to do something about that... good!

On otn night I meet APC and John. We had a few beers, or redwine :-)...

I participated to the DBA hot seat competition and I have totally humiliated to fail at the second question... I really needed a few more "pizzas" for consolation and I have a very strong headache this morning :-(

Ok, let's go to God, I have Tom Kyte session this morning :-))))

dinner

well, I just missed the appreciation dinner. I did not survive the flight + timeoffset + xtreme weekend. I sleeped from 4pm to 11pm...
Finally got a cheeseburger and three beers instead of a nice dinner and good wine :-(

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

rac day 1

This first RAC day was intense! My neighbour and I just installed clusterware, configure iscsi to use LUN in SUSE and started ASM and a clustered Database!

all doc are on
http://www.x-treme-lab.com

oow H-2

I just arrived in Moscone West. RAC Xtreme Session will start in about 2 hours. I have my bag, my t-shirt and my badge!

My hotel too, just shocked to pay 25$ to have a breakfast with no cheese, no croissant and no bread!!!

A bit suffering from the long journey of yesterday and the 9/hours time offset... everything will be alright!

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

select
    '' 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
from
( 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

agenda

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 oracle.com, 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
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

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
/

DEPTNO ENAMES
------ ------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,JONES,SCOTT,ADAMS,FORD
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
    40

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.