Andrew Fraser DBA

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:

SQL> 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:

SQL> 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:

SQL> alter table mytable modify lob (mycolumn) (cache) ;

To set it back to being uncached:

SQL> alter table mytable modify lob (mycolumn) (nocache) ;

And to see what the current setting is:

SQL> 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 accomodate 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.

Note the order of maxsize and next, has to be specified in that order, a little annoyingly:

SQL> alter database datafile ‘FILENAME’ autoextend on next 1024m maxsize 3072m ;

SQL> alter tablespace TSNAME add datafile ‘FILENAME’ size 1024m autoextend on next 1024m maxsize 3072m ;

From 10gR1 and above you can specify sizes in g. With 9i and below, you are stuck with sizes in m, like the above.