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 […]
Month: September 2005
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 […]
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 […]
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 […]
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 […]
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 […]
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
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 […]
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 […]
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 […]
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 […]
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 […]