Wednesday, November 01, 2006

Check this out

I usually employ a logon trigger for most of my Oracle databases so I can grab certain identifying information about the session. Then I save this information in another table for later analysis.

I have started testing 9iR2 on a 64-bit Linux box and have come across a certain peculiarity. v$session is defined as:

SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(4)
SID NUMBER
...

I then create a table using the same type and try to insert a value:

SQL> create table jh1 (saddr raw(4));

Table created.

SQL> desc jh1
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(4)

SQL> insert into jh1 select saddr from v$session;
insert into jh1 select saddr from v$session
*
ERROR at line 1:
ORA-01401: inserted value too large for column

Hmmmf. So I do a CTAS:

SQL> drop table jh1;

Table dropped.

SQL> create table jh1 as select saddr from v$session;

Table created.

SQL> desc jh1
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)

...and look what size the column is!

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Linux: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

Update: 2006/11/01 16:09:
From Support:
I checked my Windows (32bit) database and v$session.saddr is a RAW(4).

OK, that explains it.

4 comments:

Yasin Baskan said...

That's interesting. 9.2.0.7 64-bit on Solaris has:

SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)

The database reference shows RAW(4|8) for v$session.saddr.

Noons said...

Holy cow! Thanks for bringing that up.

I got:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production

openview->desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)


Is there an end to these differences?

Anonymous said...

Are we going to compare?

HPUX 11.11 - 64-bit PA-RISC
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)

Anonymous said...

from AIX:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

SQL> desc v$session
Name Null? Type
SADDR RAW(8)