Archive for April, 2007

And on the seventh day, God fiddled with His NLS settings

Wednesday, April 25th, 2007

This would seem to be pretty authoritative:

“And on the seventh day God ended his work which He had made; and he rested on the seventh day from all his work which He had made. And God blessed the seventh day, and sanctified it; because that in it he had rested from all his work which God created and made” (Genesis 2:2-3).

But, only in America.*

In many other NLS territories, Saturday is the sixth day of the week, not the seventh:

SQL> alter session set NLS_TERRITORY = 'AMERICA';

Session altered.

SQL> select to_char(sysdate+3,'D Dy') from dual;

TO_CH
-----
7 Sat

SQL> alter session set NLS_TERRITORY='UNITED KINGDOM';

Session altered.

SQL>select to_char(sysdate+3,'D Dy') from dual;

TO_CH
-----
6 Sat

Now that’s a real pain, because different Windows PCs will inevitably end up having different registry/environment variable settings for NLS_LANG (NLS_TERRITORY forms the middle part of the NLS_LANG variable). And NLS_LANG also gets set a lot in unix .profile’s. And those all override the databases settings.

So this week when I had users reporting different results from different PCs, I had initially assumed tnsnames.ora differences were pointing them off to different databases. But really it was because they were running code like this:

select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,'D') = 6 ;

The fix is to name the day of the week rather than count to it:

select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,'Dy') = 'Sat' ;

Essentially this issue makes the “D” date format unusable.

If code like the above absolutely couldn’t be changed, then a logon trigger would be the only way to set NLS_TERRITORY the same for all users:

create or replace trigger my_logon_trigger
after logon on database
begin
execute immediate 'alter session set nls_territory = ''UNITED KINGDOM'' ' ;
end ;
/

See Tom Kyte on NLS Date Format and asktom “language issue”

(more…)

What SQL and sessions are running?

Friday, April 20th, 2007

GUIs like TOAD and Enterprise Manager can show this better, but they’re not always to hand. Every session active now or within the last minute is shown.

set pages 9999 lines 132
col username form a20
col osuser form a20
col program form a50
col minutes form 999
alter session set nls_date_format = 'Dy DD-Mon-YYYY HH24:MI:SS'
/
select distinct
s.sid, s.username, s.osuser, s.program||' '||s.module program,
s.status, s.last_call_Et/60 minutes, s.logon_time, w.state, w.event, w.seconds_in_wait ,
q.sql_text
from v$session s, v$sql q, v$session_wait w
where ( s.status = 'ACTIVE' or last_call_et < 60 )
and s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = w.sid
order by s.sid
/
set pages 9999 lines 80 head on feed 6

Data block corruption cleared with alter system flush buffer_cache

Thursday, April 19th, 2007

So I had:
ORA-08103: object no longer exists
being reported on SQL affecting one application table. But that table existed ok in dba_tables, could be described ok, and selects restricted to its indexed columns returned data ok.

Suspicion was some sort of data block corruption. Rman backup logs had not reported any corrpution, but dbverify did find some corrupt blocks. Those blocks were not associated with any record in dba_extents though.

All that is curious enough, but it is the fix which was the real surprise. Running:
SQL> alter system flush buffer_cache ;
Fixed the problem. Implication of that is that there is intermittent data block corruption, and that table was corrupt at the time it was first read into cache, and clean again (on disk) by the time I came along to look at it.

Dbverify later on also switched over to giving the datafiles a clean bill of health.

What’s the root cause? Don’t know (yet), but intermittent disk or memory hardware failure seems the most likely. Nothing reported in server logs though.

This on Oracle 10.1.0.4.0 Enterprise Edition on Microsoft Windows Server 2003 Standard Edition Service Pack 1 on Intel Xeon 3.2GHz.

The dbverify syntax I used was like:
dbv file=D:\ORACLE\ORADATA\SYSTEM01.DBF logfile=4.log
I didn’t have to specify block size because database was at the default block size for that version of dbv (seen with dbv help=y).

Free X windows emulator for running Oracle Installer

Thursday, April 19th, 2007

It’s often a pain getting X windows GUIs like the Oracle Installer and DBCA to run on Micrsoft Windows PC clients. Emulators like Reflection-X, KEA-Term, and Exceed work ok, but they cost money to licence and require source media and local admin rights to install. Those things can take an age to organise in large bureaucratic organisations.

An easy way round this is to download and run “DSL (Damn Small Linux) embedded”. It’s like having a linux desktop, but runs as a program inside Microsoft Windows. It’s free, and works as well or better than the commercial emulators for me – except on one old laptop without much memory where mouse control back inside Windows became erratic.

“DSL embedded” can be downloaded from here (look for dsl-[VERSION]-embedded.zip). Unzip that and run “dsl-base.bat” to start it up under Microsoft Windows. While using it, “Ctrl+Alt” switches back to Microsoft Windows applications. To close it, right click its desktop and select “power down”.

More details at damnsmalllinux.org