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

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

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


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

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

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

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

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