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… Continue reading FAILED_LOGIN_ATTEMPTS part 2

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… Continue reading pivot table part 3

Categorized as Blogroll, sql

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… Continue reading pivot table part 2

Categorized as Blogroll, sql

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… Continue reading Last day

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… Continue reading tom session + blogger dinner

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… Continue reading rac again

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

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… Continue reading timetable for tomorrow

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… Continue reading 10g OCP

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… Continue reading undocumented parameter

Categorized as Blogroll, dba


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… Continue reading agenda

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 / DEPTNO ENAMES —— ——————————————     10 CLARK,KING,MILLER     20 SMITH,JONES,SCOTT,ADAMS,FORD     30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES     40

Categorized as Blogroll, sql

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… Continue reading migrate database with imp exp

Categorized as Blogroll, dba


Just updated my photo ! Thanks to photograph:-)

Categorized as blog

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… Continue reading select column only if it exists

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… Continue reading dynamic number of columns

Categorized as Blogroll, sql


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… Continue reading 10gR2

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)… Continue reading pivot table

Categorized as Blogroll, sql

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.… Continue reading group by does not sort

Categorized as Blogroll, sql

Radio Free Tooting

Radio Free Tooting is apc blog… much better written than mine! wtg apc

Categorized as 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… Continue reading return code

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… Continue reading OpenWorld SanFrancisco

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… Continue reading TO_CHAR(…, ‘D’)


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… Continue reading deterministic

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)… Continue reading How old are you?

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… Continue reading exotic constraints

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… Continue reading unexpected results !

set pages 50000

I have often see set pagesize 50000 or set pages 9999, just to avoid page break. I wonder how many line break will appears when the select grow over 50000… A cool solution is to use embedded with pagesize 0 SQL> set emb on pages 0 newp 0

The time has gone

Sure we know date. Oracle always store date and time in an internal format. It could be understood as a number of seconds since Jan 1st, -4712. It must be smaller than Jan 1st, 10000. Let’s say we do not care of the Y10K nowadays… It works well and it is easy to add days… Continue reading The time has gone

order by to_number ?

to_number is often subject to generate ORA-01722: invalid number. When I deal with integers, I prefer lpad. ex: sort pseudo-numeric select * from t order by lpad(col,20); 1 2 10 It performs well, and do not generate ora-01722. ora-01722 does not mean that I have bad data. select name,value from v$parameter where name like ‘%pool_size’… Continue reading order by to_number ?