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

Posted in Character sets

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

Posted in Character sets

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

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

Posted in Wordpress

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 GO Based on posts by Aaron Bertrand and David Seruyang.

Posted in Character sets, SQL server

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

Posted in Installs, Windows

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,

Posted in Installs, Windows

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

Posted in Performance tuning

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

Posted in Space

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

Posted in Performance tuning