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 KINGDOM.WE8ISO8859P1″}; export NLS_LANG $ grep -i NLS_LANG $ORACLE_HOME/opmn/bin/opmnctl NLS_LANG=${NLS_LANG=”ENGLISH_UNITED KINGDOM.WE8ISO8859P1″}; export NLS_LANG $ Reference 10gR2 […]

Read More Oracle Application Server sets NLS_LANG by default
March 27, 2009

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 settings are, a logon trigger is needed to record that. Like this: conn / as […]

Read More Logon Trigger to Capture Session NLS_Territory
March 27, 2009

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.

Read More Introduction to PL/SQL By Example
March 19, 2009

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 if your website runs WordPress, a different (and simpler) method is required: In the WordPress […]

Read More No www for WordPress
March 15, 2009

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. Update 2015: Carriage return (chr13) + line feed (chr10) is the default line terminator for […]

Read More Howto remove carriage return line feed from SQL Server for displaying in Excel
March 13, 2009

6 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 same command window. TEMP was set to use c:\documents and settings before, and the installer […]

Read More Oracle Installer on Windows crashes without adequate TEMP space
March 13, 2009

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, and moved to the final location at the very end. The solution is to set […]

Read More Fix for windows ftp filling up c: drive space
March 13, 2009

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 (parent_id) REFERENCES parent(parent_id) ); insert into parent values (1) ; insert into child values (1,1) […]

Read More Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records
March 13, 2009

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 1023m 8m 1g or more 64m That is for 10gR2. Note that the above table […]

Read More extent management local autoallocate sizes
March 13, 2009

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 it back to being uncached: alter table mytable modify lob (mycolumn) (nocache) ; And to […]

Read More Cache LOBs for Better Performance
March 6, 2009