schema moves by the magic of partition exchange

Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace. Process for doing partition exchange is like this: […]

Read More schema moves by the magic of partition exchange
April 17, 2009

One Comment

Use ftp in shell scripts with password in .netrc

ftp can be used in shell scripts by specifying the ftp password in a .netrc file. On the source server create/edit this file: $ vi $HOME/.netrc Add in a line with the username password details: machine targetservername login targetusername password targetpassword Make that file secure (the ftp actually fails if you don’t): $ chmod 600 […]

Read More Use ftp in shell scripts with password in .netrc
April 17, 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 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

8 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