Andrew Fraser DBA

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 Oracle Application Server Globalization Support Guide, Chapter 5.

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 sysdba
drop table af_nls
/
create table af_nls (
  af_sid number ,
  af_program varchar2(48) ,
  af_nls_territory varchar2(40) )
tablespace users
/
create or replace trigger sys.logon_af_nls
after logon on database
when ( user != 'SYS' )
declare
  v_sid number ;
  v_program varchar2(48) ;
  v_nls_territory varchar2(40) ;
begin
  select m.sid, s.program into v_sid, v_program
    from v$session s , v$mystat m
    where m.sid = s.sid and rownum < 2 ;
  select value into v_nls_territory from nls_session_parameters
    where parameter = 'NLS_TERRITORY' ;
  insert into af_nls values
    ( v_sid , v_program , v_nls_territory  ) ;
  commit ;
end logon_af_nls ;
/
sho err

Handy if you’ve got this problem.

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.

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 Site Admin screen, choose

• “Settings”, then
• “General”, and then
• edit the “WordPress Address” URL to remove the “www.” part.

You can do both changes – .htaccess and WordPress – if you want. That would be worthwhile if your site had a mix of wordpress and standard html pages. But make sure to do it in the order of (1) WordPress change first, then (2) .htaccess change afterwards. Otherwise the two changes will conflict and the WordPress pages will not be displayed at all.

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.

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 unpacks a lot of temporary files in there as it runs. Limitation in space quota each user can have in documents and settings I believe caused the installer to bomb out.

This happens with the 10g installer (including patchset 9208) which has more temporary files to unpack than earlier versions.

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 temp directories to another drive which has a lot of free space, e.g.:

set TEMP=D:\junk
set TMP=D:\junk

If running ftp from command prompt and getting large files, then just run those commands in the command window before starting ftp.

Otherwise set those as control panel environment variables.

A similar issue can happen with the oracle installer in windows, where it uses TEMP and TMP to unpack a significant amount of temporary installer files. Same fix in that case.

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) ;

2) By default, deletes from the parent will not work until child records have been removed:

delete from parent where parent_id = 1 ;
delete from parent where parent_id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (FK_CHILD) violated - child record found

3) But we can make the constraint have the “on delete cascade” option. That requires dropping and recreating the constraint (or it could have been specified that way at original creation time):

alter table child drop constraint fk_child ;
alter table child add constraint fk_child FOREIGN KEY (parent_id) REFERENCES   parent(parent_id) on delete cascade enable novalidate ;
alter table child modify constraint fk_child validate ;

Now we can delete from the parent even with child records in place:

delete from parent where parent_id = 1 ;

1 row deleted

And the child rows have been automagically deleted also.

The big advantage of this is that it is faster, especially for mass deletes.

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.

That is for 10gR2.

Note that the above table does not apply to tablespaces which have been converted from dictionary managed in the past.

Also, the next extent size is enforced. If you want to add 1 row into a 1g table which has no free space left in its current extents, then you will need to have the full 64mb of contiguous free space in its tablespace. Otherwise you will get a space error like:

ORA-01653: unable to extend table TNAME by SIZE in tablespace TSNAME

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 see what the current setting is:

select cache from dba_lobs where table_name = 'MYTABLE' ;

* The exception to this, when caching LOBs can go bad, is where there is so much LOB data, being accessed so often, that the rest of the database data is pushed out of the buffer cache. That is unusual in my experience, but as always, testing is worthwhile. Increasing the SGA/buffer cache to accommodate cached LOBs might turn out to be beneficial at the same time.

A 2004 Oracle White Paper, LOB Performance Guidelines (pdf), says:

Recommendation
Enable caching except for cases where caching LOBs would severely impact performance for other online users, by forcing these users to perform disk reads rather than cache hits.