Categories
Blogroll sql

add_years and years_between

click here

sorry, once again, I used the back button of my browser, when I was not supposed too…

Categories
Blogroll sql

add_years and years_between

I just wrote those two functions

add_years and years_between

they work similary to add_months and months_between, with the exception of leap years.

there is exactly 1 year between 28-feb-2003 and 28-feb-2004
there is 1.00273224 year (1+1/366) between 28-feb-2003 and 29-feb-2004
there is 0.99726776 year (1-1/366) between 29-feb-2004 and 28-feb-2005
there is exactly 1 year between 29-feb-2004 and 01-mar-2005

ok, here it is:

create or replace function add_years( d1 date, n number) return date is
d2 date;
begin
if ( n=0) then
return d1;
end if;
if ( d1 is null or n is null)
then
return null;
end if;
if ( to_char( d1, ‘MMDD’)=’0229′) then
if ( mod( n,1)=0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’);
elsif ( n>0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)+1,’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’)-
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’));
else
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’)-
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)-1,’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’));
end if;
else
if ( mod( n,1)=0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’);
elsif ( n>0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)+1,’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’) –
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’));
else
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’) –
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)-1,’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’));
end if;
end if;
return d2;
end;
/

create or replace function years_between( d1 date, d2 date) return number is
n number;
begin
if ( d1=d2) then
return 0;
end if;
if ( d1 is null or d2 is null) then
return null;
end if;
n:=trunc( ( to_char( d2,’SYYYYMMDDHH24MISS’)-to_char( d1, ‘SYYYYMMDDHH24MISS’))/10000000000);
if ( to_char( d1, ‘MMDD’)=’0229′) then
if ( d1<d2) then
if ( to_char( to_date( ( to_char( d1,’SYYYY’)+n)|| ‘060’,’SYYYYDDD’),’MMDD’)=’0229′)
then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’) +n)||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/366;
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/365;
end if;
else
if ( to_char( to_date( ( to_char( d1,’SYYYY’)+n-1)|| ‘060’,’SYYYYDDD’),’MMDD’)=’0229′)
then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’) +n)||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/366;
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/365;
end if;
end if;
else
if ( d1<d2) then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’))/
( to_date( ( to_char( d1,’SYYYY’)+n+1)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’) –
to_date( ( to_char( d1,’SYYYY’)+n)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’));
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’))/
( to_date( ( to_char( d1,’SYYYY’)+n)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’) –
to_date( ( to_char( d1,’SYYYY’)+n-1)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’));
end if;
end if;
return n;
end;
/

the logic is : if you are born 29-february, you will get your birthday the 60th day of the year. Otherwise, you will get your birthday on the same date as when you were born. The rest is to calculate fraction of year, and it “should” work with negatives, too.

Categories
Blogroll event

dinner with Tom Kyte in Zurich

Next wednesday, Dec 7th, starts the Tom Kyte workshop in Zurich.

In the evening we will have a dinner in Zurich.

Drop me a comment if you intend to come !

Categories
Blogroll sql

ansi literals

the first time I saw ansi literals was in June 2004 in a post on otn forum by alex goodmann. I just cannot stop using them, it is so handy!

Whenever I specify a date (or a timestamp or even a time), with Ansi Date, I do not rely on the NLS parameters, nor I do specify a format.

I simply use

date '2000-01-01'

I often use Jan 1st, 2000 as an anonymous date.
For avg(txndate), I can use

date '2000-01-01' +
avg(txndate-date '2000-01-01')

Other nice literals are timestamps

timestamp 
'2000-01-01 00:00:00.000000000 Etc/GMT+0'
timestamp '2000-01-01 00:00:00'

less usefull, because unsupported as oracle datatype

time '00:00:00.000000000 +00:00'
time '00:00:00'

also intervals

interval '1' day

one more I want to mention

q'[let's quote this]'

all this I found by reading the doc, more than once !

Categories
Blogroll event

Welcome to Switzerland

I will attend Tom Kyte 3-days session in Zurich in 2 weeks. I am immensely impatient to follow his workshop !

I hope palindnilap will be there too 😉 I invited him to come to my home to go to Tom Workshop, and to have a game of xiangqi on the brand new wooden board I received yesterday.

Categories
Blogroll dba

post from palindnilap

I just answered a question about the data dictionary on forums.oracle.com. It is all about the dictionary views.
How many tables should I know in the dictionary ?

SQL> select count(*) from dict;
1857

Well, that’s too much. But I can remove the GV$ view, which contain the instance for RAC, and the DBA_ ALL_ and USER_ have (almost) the same structure.

SQL> select count(*)
from dict
where table_name not like ‘GV$%’
and table_name not like ‘ALL%’
and table_name not like ‘DBA%’ ;
712

Anyway, who knows the 712 views by heart? Hopefully, there is one called DICTIONARY, which helps !

Coming back to the post, palindnilap wants to see which columns of a view are mapped to which column of a table. A quick look at ALL_VIEWS could do the trick, but than you will need to “understand” the query to see which view.column maps to which table.column. What’s more, ALL_VIEWS.TEXT is a long. Arghh!

if you have a view that contains all columns from a table, you could use ALL_DEPENDENCIES to see on which table it is based.

On my first answer, I pointed out that ALL_UPDATABLE_COLUMNS may reveal that a view column belongs to a table if the column is updatable.

My last try was to use the ACCESS_PREDICATES to get the column physically accessed.

SQL> select * from v02 where employee=123456;

no rows selected

SQL> select
max(substr(ACCESS_PREDICATES,1,instr(ACCESS_PREDICATES,’=’)-1))
from v$sql_plan
where ACCESS_PREDICATES like ‘%=123456’;
“EMPNO”

here we see EMPLOYEE is actually named “EMPNO” in the based table. It could be done with explain plan and PLAN_TABLE too.

Categories
Blogroll event

tom kyte in Zurich

Tom Kyte is coming to Zurich next month!
http://www.digicomp.ch/tomkyte

Categories
Blogroll dba security

oracle voyage worm

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant :

revoke CREATE DATABASE LINK from CONNECT;
revoke ALL on SYS.UTL_FILE from PUBLIC;
revoke ALL on SYS.UTL_HTTP from PUBLIC;
revoke ALL on SYS.UTL_SMTP from PUBLIC;
revoke ALL on SYS.UTL_TCP from PUBLIC;
grant EXECUTE on SYS.UTL_FILE to XDB;
grant EXECUTE on SYS.UTL_HTTP to MDSYS;
grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS;
@?/rdbms/admin/utlrp

Than, in OEM 10g, check for policy violations.

I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired

Categories
Add new tag Blogroll installation OEM

10.1.0.4 management agent for Unix

I just found in my RSS feed that a new agent has been released. 10.1.0.4 for AIX5L. Well, if you go to the OEM download page, you will see only 10.1.0.2 version for AIX, HPUX, Solaris Sparc. But by clicking on the link, for example
Grid Control (10.1.0.2)
for AIX5L Based Systems, you will find a link OEM 10g Grid Control Management Agent Release 1, from there I finally discovered that 10.1.0.4 agent download.

Well, I already had 10.1.0.4, but it was a 10.1.0.2 installation patched with 10.1.0.4. So I prefer installing directly 10.1.0.4 agent.

Not trivial to found, but hopefully, I subscribed to RSS feeds on Newest Downloads

Categories
Blogroll dba

10.2.0.1 hidden parameters

In order to get a clean database configuration, I add the following two hidden parameters in my 10.2.0.1 parameter file.
Do not hurl that loud, I hear you from here!

Well, as I said already about the _pga_max_size, I never recommend using hidden parameters when you can do the same without.

However, I am going to communicate those parameters, and the metalink notes referencing them.
You will need them to have a cleaner 10.2.0.1 installation

  • _kgl_large_heap_warning_threshold=33554432
    This parameter prevent Heap size 2800K exceeds notification threshold errors in the alert log and user trace dumps.
    Note: 330239.1 Bugs: 4286095, 4390265

  • __dg_broker_service_names=”
    In case you do not use dataguard but you do use local_listener parameter.
    this parameter prevents pmon from registering a <DB_NAME>_XPT.<DOMAIN_NAME> service in the listener.
    Thread: 611575.993 Bug: 4632635

    Probably all this will be fixed in 10.2.0.2

  • Categories
    Blogroll dba ldap

    no more tnsnames

    with netca, it is easy to configure your sqlnet.ora to use LDAP instead of tnsnames.ora. The ldap.ora and sqlnet.ora are updated… than it works, sqlplus user@db is correctly looking in the ldap oracle content

    Categories
    Blogroll installation ldap oid

    ldap day 2

    what can I do with LDAP?
    what is the difference between LDAP and Oracle Internet Directory?

    Well, there is quite a lot of interresting documents, pictures and faq on otn :

  • OTN Directory homepage
  • Directory Admin guide
  • Identity Management ReferenceLDAP is a directory server, the info are stored in an Oracle 10.1.0.4.2 database.

    When you download application server (about 2Gb), you get a fully functionnal 10.1.0.4.2 database preconfigured and an ldap server running.

    To start the admin tool, just type oidadmin in the command line.

    The password to use is the same as ias_admin and the username is orcladmin

  • Categories
    Blogroll installation ldap oid

    ldap server

    My ldap server is up and running on my notebook with SLES9.

    Next, next, next, install. That is it.

    Oracle Application Server creates a 10.1.0.4.2 database and start the Oracle Internet Directory – understand LDAP server – automatically.

    It can then be configured with the web interface.

    Categories
    Blogroll certification

    Details regarding the 10g DBA OCM requirements will be available in December

    Well, when 10g certification came out, it said : “will be available in late 2004”. In March 2005, I contacted Oracle University in Germany. They said : keep watching oracle.com/education. Later, it said : “will be available in mid 2005”.
    In Open World, I met the certification principal, who meant it will start in December and upgrade from 9i ocm will start in January and will be available in all oracle universities which give RAC course. Fine.
    When I came back I saw: “will be available in October”. Today I saw: “will be available in December”.

    Such a move like OCM is very intense, I want to plan at least 2-3 weeks full time to prepare for OCM. I need to go to my boss and say : “this year I will go for 10g ocm”. But what a pity if the certification is always delayed… planned for 2004, no info why it did not take place in 2005, I also fair that there is no guarantee for 2006 in switzerland, as they will surely start in the United States – but do not ask why…

    Keep cool, be patient 😉

    Categories
    game

    Chinese Chess

    In case you like this game and live in Zurich, I will be playing the swiss championship tomorrow.

    Categories
    Blogroll sql

    hierarchy

    what is a hierarchy?
    I enjoy reading the wikipedia definition :

    http://en.wikipedia.org/wiki/Hierarchy

    In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship.

    However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent.

    This is no longer a practical relation.

    Sterile variant :

    SQL> select * from dept connect by 1=2;
    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON

    Fertile variant :

    SQL> select * from dept connect by 1=1

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    10 ACCOUNTING NEW YORK
    ...

    the connect by does defines a true or a false connection. when true, everyone is your parent and everyone is your child. If false, you are the parent, but you have no child.

    connect by level

    Categories
    Blogroll sql

    Connect by to generate rows

    I just want to clarify something today : I do not like the connect by level<5 or connect by 1=1 where rownum, etc... I would prefer to receive ORA-01436: CONNECT BY loop in user data when trying such a hack. Nowadays, Tom Kyte uses this method in all his demonstrations. Which makes the users confident of using it. Still I do not feel this method to be safe. It just seems too much abstract to me. Also, it seems it could burn a lot of cpu time, depending on how the optimizer evaluate it. Let's try a few examples
    SQL> select * from dual connect by level<3; D - X X

    it works

    SQL> select * from dual connect by level<3 and dummy=prior dummy; select * from dual connect by level<3 and dummy=prior dummy * ERROR at line 1: ORA-01436: CONNECT BY loop in user data

    it fails. Can you tell me why? the first example is also doing a virtually infinite loop.

    Categories
    Add new tag Blogroll installation OEM

    10gR2 Grid Control is out!

    Oracle Enterprise Manager Downloads: oem download

    this is a great announcement!

    Categories
    Add new tag Blogroll installation

    linux 64bits 10gR2 is out

    one fresh download today oracle database 10g download

    I heard that grid control 10g release 2 may be available only in 2006. Quite annoying, since the repository cannot be on a 10gR2 db…

    just waiting

    Categories
    Blogroll dba

    restore to a new host : nid++

    Great challenge today: restore to a new host from a closed noarchivelog backup on tape library.

    In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because even the MAXDATAFILES and MAXINSTANCES could be changed. Change controlfile is bad. Once a collegue forget one file, once he noticed it, only months later, he wondered how to recover it. Create a controlfile is way to much sensible thing too do. It is almost as bad as changing the dictionary!

    Well. How to do than?

    Ok, I have a database called LSC67 on prod, I want to recover it to LSC66 in devl. I have NO ACCESS to production, but I have access to tapes (one may wonder if this is a good security practice…)

    Let’s start.

    First, if LSC66 already exists, shutdown abort. Remove datafiles. Remove controlfiles. Remove redo logs.

    Now I restore the data/control/redo files from prod:/dbms/oracle/LSC67 to devl in /dbms/oracle/LSC66.

    First I rename the files, some are called systemLSC67.dbf. I do not want that…

    find do the trick

    find /dbms/oracle/LSC66 -name “*LSC67*” |
    nawk ‘{printf “mv “$1” “; gsub(src,target);print}’ src=LSC67 target=LSC66 |
    sh

    I must just change the DB_NAME in my parameter file (which already exists at my site), to reflect the prod controlfile dbname

    startup quiet force nomount
    alter system set db_name=’LSC66′ scope=spfile;
    startup quiet force mount

    now I generate some statements for dynamically renaming the files

    set ver off emb on pages 0 newp 0 lin 9999 trims on head off feedb off termout off
    spool /tmp/rename_LSC67_to_LSC66.sql
    select ‘alter database rename file ”’||name||”’ to ”’||replace(name,’LSC67′,’LSC66′)||”’;’
    from (
    select name from v$datafile
    union all
    select member from v$logfile
    )
    where name like ‘%LSC67%’;
    spool off
    spool /tmp/drop_temp_LSC67_to_LSC66.sql
    select ‘alter database tempfile ”’||tf.name||”’ drop;’
    from v$tempfile tf
    where tf.name like ‘%LSC67%’;
    spool off
    spool /tmp/create_temp_LSC67_to_LSC66.sql
    select ‘alter tablespace “‘||ts.name||'” add tempfile ”’||
    replace(tf.name,’LSC67′,’LSC66′)||
    ”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
    from v$tablespace ts , v$tempfile tf
    where tf.name like ‘%LSC67%’ and tf.ts#=ts.ts#;
    spool off

    ok, now I am in mount, I do a rename datafile and drop tempfile. after I open database, and add tempfile. I am not taking care of the original size and autoextend clause of the original tempfiles, just 128M next 128M max 2G.

    set echo on termout on feedb 6
    @/tmp/rename_LSC67_to_LSC66.sql
    @/tmp/drop_temp_LSC67_to_LSC66.sql
    alter database open;
    @/tmp/create_temp_LSC67_to_LSC66.sql

    now I nid

    shutdown immediate
    startup quiet mount restrict

    nid dbname=LSC66 target=/

    and I change the db name and open resetlogs

    startup quiet force nomount
    alter system set db_name=’LSC66′ scope=spfile;
    startup quiet force mount
    alter database open resetlogs;

    Categories
    Blogroll

    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 for 9.2.0.7 and 10.2.0.1, but did not agree that it is a “desirable improvement”, imho it is a major lost of service.

    Just a few minutes (seconds?) after I finished writing my iTar, severity 2, I received a call from Oracle Support.

    Apparently a group of customers asked to prevent any non-dba user from using sqlplus on a database server (just on the client). This kind of answer I do not like. But we had a long talk, he finally showed me a workaround for 10.2.0.1 : doing

    chmod -R 755 $ORACLE_HOME

    before root.sh.

    Well, this should have made me hurl. But ok, I can do this.

    He also explained me, that I should escalate this bug if I want development starting working on it. Also there is another bug, 4533592

    My satisfaction with this tar is good (4/5). I have been contacted immediatly, informed about the bugs, and informed about the procedure to escalate this case when I will require too.

    My overall satisfaction with Metalink is about 3/5. But sometimes, it is really 0/5. The worst I have was :

    lsc: how do you remove headers in the middle of your result in sqlplus ? I have a bug with set emb on and set pages 9999.
    metalink: This is a bug in sqlplus. Workaround is to use Oracle Reports.

    I could just have kill her! The “good” solution is “set pages 0”, ref: set pages 50000

    One also like this :

    lsc: When I try to do an executable in scheduler I got an ora-27371 (AIX)
    metalink: switch to a new platfom
    lsc: @*#!!
    metalink: It will be fixed in 10.1.0.4
    lsc: no it is not fixed in 10.1.0.4
    metalink: it is an internal bug
    …months later…
    lsc: still not fix in 10.2.0.1
    metalink: development is working on it

    well, I have always known that AIX is not a strategic platform and many shell scripts function utterly wrong… still very frustrating one!

    Larry just told that woman : just drop me an email. Should I write Larry each time I do receive an idiot answer?

    Categories
    Blogroll dba security

    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
    Categories
    Blogroll dba security

    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

    Categories
    Blogroll sql

    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

    Categories
    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 part 1
    pivot table part 3

    Categories
    Blogroll event

    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 wine 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

    Categories
    Blogroll event

    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!

    Categories
    Blogroll event RAC

    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 🙂

    Categories
    Blogroll event RAC

    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

    Categories
    Blogroll event

    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 !

    Categories
    Blogroll certification

    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