Author: Laurent Schneider

metalink

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 […]

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 […]

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 […]

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 […]

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

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; TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG ——————– ——— ——— ——— ———- ——— —— ———– — SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO looks fine. but what if I try that […]

dynamic number of columns

probably one of the most frequently asked question, you want to have a table like             ACCOUNTING OPERATIONS RESEARCH SALES 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 […]

10gR2

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 […]

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 YEAR COU —– — 1980   1 1981  10 1982   1 1987   2 how do we get the years without hiredate with 0? 1) […]

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;     NUM ——-     400  220000     310 Well, group by is “sorting”, but how? this seems crazy. […]

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 […]

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 […]

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; TUESDAY 2 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; 2 How do I […]

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 trunc((to_char(sysdate,’YYYYMMDD’)-to_char(birthdate,’YYYYMMDD’))/10000) 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) […]

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 […]