Monthly Archives: March 2009

Oracle Application Server sets NLS_LANG by default

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

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

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

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

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 | 4 Comments

Oracle Installer on Windows crashes without adequate TEMP space

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

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 | 4 Comments

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

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

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

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 | 1 Comment