Do you shu or do you spo?

When I quit ftp command line, I do not type quit, nor bye, because it is too long. I rather type "by".

Does it sound strange and meaningless to abbreviate "bye" in "by" ? Well, I have a few favorites

SQL> set lin 999
SQL> rollb
SQL> spo f
SQL> shu
SQL> spo off

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION.

Except SYS, SYSTEM and DBSNMP

To minimize this security problem, I implemented the following strategy on my test system.

1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE
2) alter user SYSTEM account lock;
3a) in 10gR2 :
alter user dbsnmp quota 1T on sysaux;
create role secure_oem_role;
grant advisor, analyze any, analyze any dictionary, create job, create procedure, create session, create table, manage any queue, select any dictionary to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_AQ" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_AQADM" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_DRS" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_MONITOR" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_SERVER_ALERT" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_SYSTEM" to secure_oem_role;
grant EXECUTE on "SYS"."DBMS_WORKLOAD_REPOSITORY" to secure_oem_role;
exec SYS.DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','ALERT_QUE','SECURE_OEM_ROLE')
revoke EXECUTE ON "SYS"."DBMS_SERVER_ALERT" from dbsnmp;
revoke EXECUTE ON "SYS"."DBMS_SYSTEM" from dbsnmp;
revoke UNLIMITED TABLESPACE from dbsnmp;
revoke SELECT ANY DICTIONARY from dbsnmp;
revoke CREATE PROCEDURE from dbsnmp;
revoke CREATE TABLE from dbsnmp;
revoke OEM_MONITOR from dbsnmp;
grant secure_oem_role to dbsnmp;
3b) in other versions, you probably can remove more and grant less, I think only in 10g it is necessary to have "quota". In my other databases, dbsnmp have 0 segments.

Check what system privileges are potentially dangerous to the system :

select path
from
(
select
grantee,
sys_connect_by_path(privilege, ':')||':'||grantee path
from (select grantee, privilege, 0 role from dba_sys_privs union all select grantee, granted_role, 1 role from dba_role_privs)
connect by privilege=prior grantee
start with role=0
)
where
grantee in (
select username from dba_users
where lock_date is null
and password != 'EXTERNAL'
and username != 'SYS')
or grantee='PUBLIC'
/
:ADVISOR:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP
:CREATE JOB:SECURE_OEM_ROLE:DBSNMP
:CREATE PROCEDURE:SECURE_OEM_ROLE:DBSNMP
:CREATE SESSION:USER1
:CREATE SESSION:USER2
:CREATE SESSION:SECURE_OEM_ROLE:DBSNMP
:CREATE TABLE:SECURE_OEM_ROLE:DBSNMP
:MANAGE ANY QUEUE:SECURE_OEM_ROLE:DBSNMP
:SELECT ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP

it sounds better...

the forums I use

on otn : forums.oracle.com
1) SQL and PL/SQL
2) Database General
3) iSQL*Plus
4) Documentation Feedback

when otn is down or too slow, I read the metalink forums. Especially SQL*Plus and Oracle PL/SQL. If necessary, I post questions on the DBA Administration forum.

to post questions/comments specific to security, I go to Pete Finnigan's Oracle Security Forum.

Sometimes I post comments on the asktom site. About leap years, number to octal, number to words...

Occasionaly, I visit the Dizwell Forum, just to grab some interesting post...

When it is nice weather, I go to usenet comp.databases.oracle.server

And of course, I use google a lot to find new places to go !

idle events in 10gR2

I just noticed this morning that idle events are very easily identifiable by a new column called wait_class in 10gR2

To ignore idle event, I just wrote

select WAIT_CLASS, event, TOTAL_WAITS, TOTAL_TIMEOUTS from
(select * from V$SYSTEM_EVENT where WAIT_CLASS#!=6 order by TIME_WAITED_MICRO desc)
where rownum<6;
WAIT_CLASS    EVENT                                    TOTAL_WAITS TOTAL_TIMEOUTS
------------- ---------------------------------------- ----------- --------------
System I/O    log file parallel write                        79713              0
Configuration log file switch (checkpoint incomplete)          210            100
Configuration log file switch completion                       165             52
System I/O    db file parallel write                           645              0
System I/O    control file parallel write                     2276              0

webcomment

Interesting indeed! I wonder how much spam and irrelevant comment will appear, where the author probably have no way to remove "web" comments.Best wishesLaurent

Joyeux Noël

I wish all my readers and their family a merry christmas !

Yesterday I was by my grandparents, with all their 4 children, all their 4 grandchildren and all their 4 grandgrandchildren.

It was a wonderful Christmas Eve, a beautiful tree my grandmother planted a few years ago, christmas songs, a prayer, a Father Christmas and lots of gifts for the children... and a few for me too ;-)

oemgc 10 release 2

solaris version now available
Entreprise Manager download (Solaris 32bits)

new metalink interface

I just discovered that new interface today.

Hopefully, my old bookmarks still work.

I did not find the "Tar" button. Well, it is now named "Service Request".

I tried the ORA-600 lookup tool, Doc id 153788.1
but it does not work today, both Firefox and Explorer failed.

MS Explorer reports a Java Script error.

document.forms.0.tool_type.value is null or is not an object

Just too sad... it is a very important tool. Anyway, I could search by using "ora-600 4883" to find out what I am looking for.

sys_connect_by_path in 8i or the danger to use undocumented parameters...

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production.

as it simpliest form

select sys_connect_by_path(dummy,':') from dual connect by 1=2;

well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked fine. In production, it just refused to work on 8i.

Well, I have an other bitset of 8i on my test environment, and an other operating system version. So I supposed it must be related to the old production 64bits AIX4 os.

After reading the docs again, I discovered that SYS_CONNECT_BY_PATH is not documented in 8i. So why did this work on my test system then? Because I have _new_connect_by_enabled = true in my init.ora.

Ok, on the one hand, I found out an (unsupported) way to let my query run in 8i

alter session set "_new_connect_by_enabled"=TRUE;

On the other hand, I have been tricked by my own parameter file, setting one hidden parameter ages ago, forgetting about it, and the query "unhopefully succeeded" in my test environment... revealing the bug only once distributed !

oracle10gR2 on suse10

I prefer and recommend using Oracle on a supported version, like Suse Entreprise 9, because the installation is fair. The Installer does complain on SLES9 what is missing.

Ok, I just received a brand new notebook, I decided to go to Suse 10, and, Ô Miracle, the installation was pretty easy! I surely installed C++ development tools (how can I survive without a C compiler), but I missed the libaio and libaio-devl. Well, I installed it afterwards and my create database statement succeeded.

Sincerly, installing Oracle 10gR2 on Suse 10 is no longer a pain as it was before with non-supported versions.

I am so happy ;)

Well, my gnome evolution ms-exchange connector is still not working, but this I will fix asap

row generators performance

I wrote a few generators, and listed some existant in
http://laurentschneider.blogspot.com/2005/08/pivot-table.html

I decided to test them

note that this is not a "good" test, it is simply an overview. I am doing a count(*), another operation may be better in one or worst in another one.

all_objects is so slow (6 seconds for 50K rows) that I did not include it in my test

generate 1000 rows : all method are fast, except xquery...

generate 1000000 rows : model and cube never ends, xquery is very slow, connect and union are slow, plsql is ok, simple heap table is very fast (!)

I would like to point out that "generating rows" is rarely a business requirement. If you need to outer join with every day of the year, than create a table and insert every day of the year in it. It will be very fast, it is maintenable, extensible, supported, and self-documented...

Ok, here is my test

create type t_number as TABLE OF number;
/

create function f_number(n number) return t_number pipelined is
begin for i in 1..n loop pipe row(i); end loop; return; end;
/

create table t1000 as select 0 n from xmltable('for $i in 1 to 1000 return $i' );
create table t1000000 as select 0 n from xmltable('for $i in 1 to 1000000 return $i' );

set timi on feedb off echo off head off

prompt model 1000
select count(*)
from (
select null
from dual
model
dimension by (1 h)
measures (1 c)
rules( c[FOR h FROM 1 to 1000 INCREMENT 1] = 1)
)
/

prompt xquery 1000
select count(*)
from
xmltable('for $i in 1 to 1000 return $i' )
/

prompt xquery 1000000
select count(*)
from
xmltable('for $i in 1 to 1000000 return $i' )
/

prompt union 2*2*... 1K
with s as (select null from dual union all select null from dual)
select count(*)
from s,s,s,s,s,s,s,s,s,s
/

prompt union 1M
with s as (select null from dual union all select null from dual)
select count(*)
from
s,s,s,s,s,s,s,s,s,s,
s,s,s,s,s,s,s,s,s,s
/

prompt cube 1K
select count(*)
from (
select 1,2,3,4,5,6,7,8,9,10
from dual
group by cube(1,2,3,4,5,6,7,8,9,10)
)
/

prompt table 1000
select count(*)
from t1000
/

prompt table 1000000
select count(*)
from t1000000
/

set termout off
create type t_number as TABLE OF number;
/

create function f_number(n number) return t_number pipelined is
begin for i in 1..n loop pipe row(i); end loop; return; end;
/
set termout on

prompt plsql 1000
select count(*)
from table(f_number(1000))
/

prompt plsql 1000000
select count(*)
from table(f_number(1000000))
/

prompt all_objects
select count(*)
from all_objects
/

prompt connect 1000
select count(*)
from (
select level
from dual
connect by level<1001
)
/

prompt connect 1000000
select count(*)
from (
select level
from dual
connect by level<1000001
)
/



===============================================

model 1000
1000
Elapsed: 00:00:00.01
xquery 1000
1000
Elapsed: 00:00:00.30
xquery 1000000
1000000
Elapsed: 00:00:20.63
union 2*2*... 1K
1024
Elapsed: 00:00:00.02
union 1M
1048576
Elapsed: 00:00:01.32
cube 1K
1024
Elapsed: 00:00:00.00
table 1000
1000
Elapsed: 00:00:00.00
table 1000000
1000000
Elapsed: 00:00:00.21
plsql 1000
1000
Elapsed: 00:00:00.01
plsql 1000000
1000000
Elapsed: 00:00:00.91
all_objects
46709
Elapsed: 00:00:05.36
connect 1000
1000
Elapsed: 00:00:00.01
connect 1000000
1000000
Elapsed: 00:00:01.40

Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite

Day 3 concludes the Tom workshop in Switzerland. We started with read consistency and write consistency, with an interesting example were a single row update could make a big job run thrice slower, because of the write consistency.

Once again, Tom insist on saying that you must understand Oracle to write applications.

Tom demonstrates a few 10g features. Even if I already attended his 10gR2 new features in OpenWorld, I learnt a few interesting stuff about 10gR1, and the demo on his notebook were impressing.

Ok, let's reveal some of those ;-)

You have surely heard of flashback database, but did you ever heard of flashforward ??? Well, if you flashback, open readonly, than you can flashback to the future afterwards !

Did you know about Case Insensitive? Did you try it? By writing the correct index, setting the correct parameters (NLS_COMP=ANSI, NLS_SORT=binary_ci), you will be using a regular index where doing where ENAME='Scott', the execution will actually reveal an index RANGE scan.

A life-saving package I have never heard of is DBMS_ADVANCED_REWRITE. This is simply a great way of rewriting a call you cannot rewrite in the application. Each call to the server could be rewritting by the dba. A great tuning capability. Maybe I will never use that, sounds a bit too much frightening to me, but I am so glad to know about it !

Finally, the TDE (transparent data encryption) demo was educative. On the one hand, if you do not have the wallet password, even if you are the DBA, than you cannot select an encrypted column!

Does it mean that you can hide data from the dba? Tom says NO WAY. The dba can always look your bind variable, look your package functions, and catch all your datas. What he think (and I do think that too), is that you can have a non-powerfull dba, which can reset your password, create a new user, drop a datafile, but which does not need to have the DBA role. Kind of pseudo-dba.

I believe that some systems contain data that no one is able to access. Not even the database administrator. In bank, it is not the case, the DBA is trusted, he signed some contracts to not reveal datas, etc... But in justice for example, I could imagine that the dba does not have the power to select any table (so will not be granted the dba role). I have been postings on forums quite a lot of time about it. For example thread 499144.995 in metalink in 2003. But I have no real experience about "secret" data which are hidded from the dba. Just thoughts.

One more great feature I learnt and will try on monday is datapump compression. We have always been compression regular export dumps with named pipe and "compress" in unix, there is finally a way to compress datapump exports.

Tom said: "I learn something about Oracle every single day". Well, in Tom workshop, I learnt 42 new things in 3 days.

Tom Kyte Day 2

Today I asked : "Is it the responsability of the developper to create the table structure?"

The answer was something like that :
"You have four kind of persons.
- You have the Oracle6 DBA, who says always NO
- You have the Developer, who does not care about database
- You have the DBA/Developer, who understand the logical structures like IOT/Hash Cluster
- You have the Developer/DBA, who understand the database
In a perfect world, the Developer/DBA choose the table at design time and the DBA/Developer inform the developer about the database capabilities.
If you have a developer and a dba/developer, the dba/developer can reorganize the table as a tuning action."

After, Tom talked about sql technics, show a few examples about "CONNECT BY" without prior generating rows, a pipelined table. Very interesting examples, with a few keyword like FIRST_VALUE and IGNORE NULLS that I have never used before.

Later in the afternoon, he starts talking about binding. Difficult topic, I was almost going to sys.dbms_lock.sleep at the end of the day.

We are a bit late on the program, probably too many questions, palindnilap and myself feel a bit bored about one person always asking a lot of question mostly off topic about experiences he had but nobody cares of...

Well, I am impatient about day three, it is going to be intense, read-write consistency among others themas.

tbc

Tom Kyte Day 1

Day one was quite interesting! We learnt a lot of staff about tuning approach, I have got confirmation that most of the "WE KNOW THAT, IT HAS ALWAYS BEEN SO" were maybe one day true, but are no longer, for example "separate index and tables", or, delightfull, "you must periodically reorganise your tables". I also learnt about DBMS_APPLICATION_INFO, which I may use in my dba-scripts too.

I also feel relaxed that designing a table to be IOT or Hash-Clustered is NOT a dba task, but a developer task. Wow, finally, the dba have to look at the database not at the table structure to gain performance...

In the evening, we had a dinner with my friend Fabrice, my ZKB-collegue Roman, my future ex-lc collegue Marc, a reader of tom blog and mine one called Leo, and also Lutz, who is working for Oracle University, and of course Tom, who enjoyed the fishes ;-)

soon day 2, so i have to go to bed right now

RECOVERY_CATALOG_OWNER

I just tried today to limit power of rman :

REVOKE ALTER SESSION, CREATE DATABASE LINK FROM RECOVERY_CATALOG_OWNER;

It seems I can still do a backup... probably those privilege are not needed by rman, maybe just inherited from Connect in an older released !?

encrypted listener password

There a few major changes in the database administration and the database security between 9i and 10g.

In 9i, I used to grep in the listener.ora to find out the password.

LISTENER_LSC61 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200))
))
PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF

this 64bit encrypted string can be used in 9i to stop the listener

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production on 05-DEC-2005 14:33:51

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener listener_lsc61
Current Listener is listener_lsc61
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)))
The command completed successfully

As a dba, it is quite handy, because you can use grep (or awk) to find out the password out of the listener.ora. As a security admin, you should make sure the listener.ora is not readable. Note that the default, when created by netmgr, is to be world-readable :-(

However, this does no longer work in 10g

LISTENER_LSC62 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC62 = 1234567890ABCDEF

the encrypted string can no longer be used

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production on 05-DEC-2005 14:37:24

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener listener_lsc62
Current Listener is listener_lsc62
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200)))
TNS-01169: The listener has not recognized the password
TNS-01189: The listener could not authenticate the user


As a security admin, you would think it is better so. But, how are you going to stop the listener in your script? Well, in 10g, we can use local authentification (default). So if the script is started as oracle, we would not need to use password

LISTENER_LSC63 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC63 = 1234567890ABCDEF

$ whoami
oracle
$ hostname
dbsrv85a.ex.zkb.ch
$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Production on 05-DEC-2005 14:43:33

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener LISTENER_LSC63
Current Listener is LISTENER_LSC63
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200)))
The command completed successfully


I read in an Alex Kornbrust post on Pete Finnigan forum, that a LOCAL_OS_AUTHENTICATION "undocumented" parameter could be used to "avoid" local authentication, but in that case, it is going to be a nightmare to "stop" the listener in an automated script, well, we can still use "kill", but it is not very beautifoul.

the sqlplus settings I like

It is monday, I am going to give a list of settings I like in sqlplus

set lin 32767 trimsp on tab off
extends the linesize and avoid line breaks. I use it before SPOoling. But it is annoying before DESCribing. Trimspool is necessary to avoid spaces at the end of the line. Set tab off makes sure sqlplus does not use "tab" for formatting, but spaces.

set emb on pages 0 newp none
this avoid page breaks. there is one header in the top, than no more, and no ^L. newp none is not working in version 7, there you must use newp 0.

set head on
set head off
show or hide column headers

set feedb 6
set feedb off
report result of query, set feedb 6 do not give feedback if a select returned 1 to 5 lines, because it is too easy to count... Set feedback off removes feedback

set ver off
I am never interrested in the translation of my defined variables

set termout on
set termout off
Off avoids screen output. Warning, this does not avoid spool output. Works only in scripts, not in command mode. Note that a command piped thru sqlplus is still a command more.

set echo on
set echo off
Display executed command. Works only in scripts, not in command mode.

sqlplus / <<EOF
set echo on
set termout off
select * from dual;
EOF
the echo on and termout off will have no effect, because it is not a sql script (called with @).


def _editor=vi
set editf /tmp/lscfile.sql
Use vi (instead of ed) as editor, and use a file in /tmp (instead of afiedt.buf in working directory) as temp file

set long 1000000000 longc 60000
do not truncate longs nor long chunks. Very usefull with clob in sqlplus.

set serverout on size 1000000
set serverout on size unlimited
allows dbms_output to print to current terminal. Unlimited is a 10gR2 enhancement

set sqlp "_USER @ _CONNECT_IDENTIFIER> "
change the prompt to contain a dynamic user and connection string.

thursday I am having dinner with tom kyte, drop me a comment there if you want to come

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.

add_years and years_between

click here

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