Monthly Archives: March 2009

Oracle Application Server sets NLS_LANG by default

Posted on 27 March 2009 by Andrew Fraser

If you don’t specify NLS_LANG in your shell when starting OAS, OAS goes and sets it for you. Fix is to specify NLS_LANG in any OAS startup scripts you use, or edit these files: $ grep -i NLS_LANG $ORACLE_HOME/Apache/Apache/bin/apachectl NLS_LANG=${NLS_LANG=”ENGLISH_UNITED … Continue reading →

Posted in Character sets | Leave a comment

Logon Trigger to Capture Session NLS_Territory

Posted on 27 March 2009 by Andrew Fraser

You can see your own sessions nls settings select * from nls_session_parameters ; But for other users’ sessions, that information is stored in their own UGA, not accessible outside their session. So if you need to know what their nls … Continue reading →

Posted in Character sets | Leave a comment

Introduction to PL/SQL By Example

Posted on 19 March 2009 by Andrew Fraser

Here are online course notes, with code examples, for a simple introduction to Oracle’s PL/SQL language. Was originally aimed especially at Infrastructure DBAs, but would be of use to anyone learning PL/SQL.

Posted in PL/SQL | Leave a comment

No www for WordPress

Posted on 15 March 2009 by Andrew Fraser

For Apache webservers displaying normal html, adding these lines to file “.htaccess” is a good idea: RewriteEngine On RewriteCond %{HTTP_HOST} ^www\.(.+)$ [NC] RewriteRule ^(.*)$ http://%1/$1 [R=301,L] Because it will strip out the “www.” from URLs. As recommended at http://no-www.org/ But … Continue reading →

Posted in Wordpress | Leave a comment

Howto remove carriage return line feed from SQL Server for displaying in Excel

Posted on 13 March 2009 by Andrew Fraser

Use this SQL to remove carriage return line feed from SQL server for displaying in excel: select REPLACE(column_name, CHAR(13) + CHAR(10), ‘, ‘) from table_name ; Based on posts by Aaron Bertrand and David Seruyang.

Posted in Character sets, SQL server | 3 Comments

Oracle Installer on Windows crashes without adequate TEMP space

Posted on 13 March 2009 by Andrew Fraser

Oracle installer on Windows can fail to run, crashing out without a useful error message. I found fix was to change the temp directories in a command window set temp=d:\junk set tmp=d:\junk and then run the installer (setup.exe) from that … Continue reading →

Posted in Installs, Windows | Leave a comment

Fix for windows ftp filling up c: drive space

Posted on 13 March 2009 by Andrew Fraser

Windows ftp can fill up C: drive. This happens either putting or getting files, even if you try putting/getting them to a drive other than C:. This happens because the ftp file is written to temporary area first by ftp, … Continue reading →

Posted in Installs, Windows | 2 Comments

Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records

Posted on 13 March 2009 by Andrew Fraser

Here is a demo example. 1) Set up example tables: CREATE TABLE parent ( parent_id number(10) not null, CONSTRAINT parent_pk PRIMARY KEY (parent_id) ); CREATE TABLE child ( child_id        numeric(10)     not null, parent_id       numeric(10)     not null, CONSTRAINT fk_child FOREIGN KEY … Continue reading →

Posted in Performance tuning | Leave a comment

extent management local autoallocate sizes

Posted on 13 March 2009 by Andrew Fraser

Here is the algorithim for calculating next extent sizes with a system managed/autoallocate exten management local tablespace. The dependency is with the current segment size. Segment Size Next Extent Size less than 1m 64k 1m to 63m 1m 64m to … Continue reading →

Posted in Space | Leave a comment

Cache LOBs for Better Performance

Posted on 6 March 2009 by Andrew Fraser

LOBs generally(*) perform better if cached – that is, stored in the database buffer cache. However, that is not switched on by default. To change a lob to be cached: alter table mytable modify lob (mycolumn) (cache) ; To set … Continue reading →

Posted in Performance tuning | Leave a comment