difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);.

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

SQL> set autot trace exp
SQL> delete emp;

14 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> set autot off
SQL> select count(*) from emp;
0
SQL> roll
Rollback complete.

but explain plan does not
SQL> select count(*) from emp;
14

SQL> explain plan for delete emp;

Explained.

SQL> select * from table (dbms_xplan.display);
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
14

change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change “automatically”

Demo

SQL> CREATE DATABASE
MAXDATAFILES 5
MAXINSTANCES 1
MAXLOGFILES 2
MAXLOGMEMBERS 1
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1;

Database created.

SQL> create tablespace t1;

Tablespace created.

SQL> create tablespace t2;

Tablespace created.

SQL> select count(*) from v$datafile;
COUNT(*)
----------
6

datafiles exceeded, but no error!


SQL> alter database add logfile
('/dbms/oracle/LSC75/redo/f1.sql',
'/dbms/oracle/LSC75/redo/f2.sql') size 16M;

Database altered.

SQL> select group#, members from v$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 2

logfiles and logmembers exceeded, but no error!


SQL> alter database add logfile instance 'I2';

Database altered.

SQL> alter database add logfile instance 'I2';

Database altered.

SQL> alter database enable instance 'I2';

Database altered.

SQL> select count(*) from v$INSTANCE_LOG_GROUP;
COUNT(*)
----------
2

instances exceeded, but no error!

so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?

select * from test where my_long like ‘%toto%’

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on developpez.com (french forum)


SQL> create table test ( my_long long);

Table created.

SQL> insert into test values ('hello toto !');

1 row created.

SQL> exec for r in ( select my_long from test )
loop if (r.my_long like '%toto%') then
dbms_output.put_line(r.my_long); end if;
end loop
hello toto !

oem grid control 10gR2 on Suse10

Last friday I installed RAC on my Suse10. Today, I installed Grid Control.

Of course it is not supported.

The packages I neeeded additionaly are db1 and openmotif.

It did not work 100%. I finally started the iasconsole (emctl start iasconsole), and within the iasconsole, I restarted the failed elements.

I created the repository manually (sysman/admin/emdrep/bin/RepManager)

Suse10 10gR2 Laptop RAC

Hi,
[EDIT]
I did read Dizwell article about Linux 10g Laptop RAC. The article is no longer available
[/EDIT]

I have just installed RAC 10gR2 on Suse10.

The document above guided me thru the steps.

Specific Suse10 :
– raw devices are configured by editing /etc/raw with values like raw1:loop1 and must be activated with rcraw start (+ chkconfig -a raw for persitence)

To take care :
– read point 8 to survive reboot…

Specific 10gR2 :
In 10gR2, MAXINSTANCES must not be specified by create controlfiles. In 10gR2, the controlfile can grow dynamically. Even if I created my controlfile with maxinstances=1, I can do alter database add logfile instance 'RAC2' ; alter database add logfile instance 'RAC2' ; alter database enable instance 'RAC2';, which is simply great! CREATE CONTROLFILE just belongs to the past!

Installation:
I had to run vipca manually as root at the end of the root script of the crs installation. I also removed my existing oracle_homes and rebooted before installing database software again. The VIP interface must be configured in /etc/hosts and DOWN (ifconfig down eth0:2).

Conclusion:
The doc written by Amit Poddar on Howad site is usefull. Enjoy rac’ing on your pocket desk calculator!

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
from (
select *
from V$SYSTEM_EVENT
where WAIT_CLASS#!=6
order by TIME_WAITED_MICRO desc)
where rownum<6 ; WAIT_CLASS EVENT ------------- ---------------------------------------- System I/O log file parallel write Configuration log file switch (checkpoint incomplete) Configuration log file switch completion System I/O db file parallel write System I/O control file parallel write

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

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

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.

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 !

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.

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.