How to delete an object with a special character in Oracle

There are some things in Oracle that are possible but shouldn't be possible.
One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it.
I.e.:

SQL> create table "My Fruit Table" (id number);

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
My Fruit Table                 TABLE

Horrible! And what's even more horrible is that people actually do this.

Now to the problem, a user created a table with a special character in the name. Not even sure what character but I need a way to drop it. PL/SQL to the rescue.
Example:

SQL> set serveroutput on
-- Lets create a dummy table with a bogus character in the name
SQL> declare
        a varchar2(500);
begin
        a:='create table "abctest'||chr(150)||'" (id number)';
        execute immediate a;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
abctest?                       TABLE

SQL> desc "abctest?";
ERROR:
ORA-04043: object "abctest?" does not exist

-- And a bit of PL/SQL to drop it
-- change the abctest% string to something matching your single table.
SQL> declare
        a varchar2(500);
        tname varchar2(50);
begin
        a:='select table_name from user_tables where table_name like ''abctest%''';
        execute immediate a into tname;
        dbms_output.put_line('Table name is: '||tname);
        execute immediate 'drop table "'||tname||'"';
end;
/

Table name is: abctest?

PL/SQL procedure successfully completed.

SQL> select * from tab;

no rows selected

Handy.

Buying marketshare

Ok, I'm way waay late on commenting on the two most recent IT company takeovers .

Sun buys MySQL
Tricky one, I have to honestly say I have a hard time seeing where MySQL fits in to Sun's portfolio.
Sun is one of Oracle biggest partners (the biggest after Dell?) and they are one of the biggest backers of PostgreSQL, then even employee a few of head lead Postgres developers.
Sun has got two options with MySQL, they can either invest a lot of RnD in MySQL and actually turn it in to a really good product. MySQL is without a doubt a product with incredible potential, but is today pretty much a over-sized way to store CSV index files (bit harsh perhaps but hey).
Or they can ride out the profits by selling LAMP-stack boxes and let MySQL sustain itself, I doubt they can make up for the billion dollars they payed for it though.
Oh, and what will Sun make of the (rather evil) closed source MySQL Enterprise Monitor administration product from MySQL. Sun having new closed source software? Errr!

Some advice for sun,
1) Add real constraint support. This is a show stopper for many, and the InnoDB approach isn't good enough. MySQL needs native solid from the ground up constraint support, independent of storage engine, version bla bla. If people don't want the "performance loss" of constraints, don't add the constraints!
2) Sort out proper I/O management, tablespaces, clean partitioning all that stuff. MySQL has improved a lot on this point over the last 12 months. But more work is needed, especially if they are planning on selling MySQL powered Thumpers.
3) 100% solid transactional support. MVCC would be fun. I've seen a few to many transactional problems in MySQL. Some sort of logging/undo/redo system is needed.

On top of that I wouldn't mind a clean hot-backup tool, requires proper transactional support (with something like SCN's etc), 3rd party scripting language support (PgPerl anyone?).

That's about 250.000 RnD hours, go get busy!

Oracle buys BEA
This is most likely very good news. I'm a big fan of the Oracle database (duh) and a fan of Weblogic Application Server.
What I'm not a big fan of is Oracle Application Server (oc4j).
I hope Oracle realize that Weblogic is the superior product and works with Weblogic as the front runner and port oc4j features to Weblogic and not the other way around. I doubt that will be the case though. If not, I hope they don't kill off Weblogic any time soon.

Hmm, that's all for now.

Converting MySQL "on update current_timestamp" to Oracle

Another short simple SQL for all out there in the process of converting old MySQL schemas to Oracle.

MySQL has got a built in feature to automatically update a column to the current timestamp whenever the row is updated, just by using the default-clause. The "on update current_timestamp" feature can be quite handy if you have lazy developers who can't be bothered writing full insert statements. :)

The MySQL create table statement would be something like this:

create table p (
 id int,
 a varchar(10),
 d timestamp DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP,
 constraint p_pk primary key (id)
);

Not difficult to do in Oracle either, but we need a trigger to assist.
Example:

SQL> alter session set nls_Date_format='HH24:MI:SS';

Session altered.

SQL> create table p (id number, a varchar2(10), d date default sysdate, 
       constraint p_pk primary key (id));

Table created.

SQL> insert into p(id,a) values(1,'test');

1 row created.

SQL> host cat p_test.sql
CREATE OR REPLACE TRIGGER p_d_trig
BEFORE UPDATE ON p
FOR EACH ROW
BEGIN
        select sysdate into :new.d from dual;
END p_d_trig;
/

SQL> @p_test

Trigger created.

SQL> select * from p;

        ID A          D
---------- ---------- --------
         1 test       21:15:05

SQL> update p set a='foo' where id=1;

1 row updated.

SQL> select * from p;

        ID A          D
---------- ---------- --------
         1 foo        21:16:44

SQL>

Oracle SQL to return a alphabetical subset

Yes, I know, I've been waaay bad at blogging lately. Been busy working on new projects at work, upgrading applications to Java1.5 containers (Websphere 6.1, Weblogic 9.2 etc). On the fun side we've got an Azul Java-acceleration box, that really needs a couple of blog entries!

Anyway, got asked if there was a way to return a resultset of data based on the leading character, the use case was to ignore all strings starting with a,b, or d and return e to z.

Fairly straight forward but a good SQL to have.
I simply grab the first character in the varchar and compare it's ASCII value to the ASCII value D.

SQL> select * from t;

A
----------
Atest
Btest
Etest
Htest
Wtest
Dtest
dtest
SQL> with data as (
  2  select ascii(upper(substr(a,1,1))) a_val,a from t
  3  )
  4  select * from data where a_val not between ascii('A') and ascii('D') order by a
  5  /

     A_VAL A
---------- ----------
        69 Etest
        72 Htest
        87 Wtest
SQL>

Oracle 11g: Archive log alert log output

Starting with Oracle 11g the archivelog process is no longer logged to the alert log by default. For good and bad in my opinion, nice to get rid of the log entries on smaller databases but it can be quite useful to see the details on larger systems with multiple log destinations.

The log level is simply a database parameter and can easily be changed, look at this page to figure out what level you want. I figured 79 would be a good starting point (64+8+4+2+1). Databases running dataguard should probably have 207 (add 128) to include FAL service details.

How to set the trace level:

[oracle@vm-rac1 ~]$ rsqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 19 02:35:23 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter log_archive_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace                    integer     0
SQL> alter system set log_archive_trace=79;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@vm-rac1 ~]$ talert # my shell alias to tail the current alert log
  Current log# 1 seq# 7 mem# 0: /u01/app/oracle/oradata/test11g/redo01.log
Sun Aug 19 02:03:14 2007
ARC3: Evaluating archive   log 3 thread 1 sequence 6
ARC3: Beginning to archive thread 1 sequence 6 (627362-628594) (test11g)
ARC3: Creating local archive destination LOG_ARCHIVE_DEST_10: 
'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_%u_.arc' (thread 1 sequence 6)
 (test11g)
ARC3: Creating local archive destination LOG_ARCHIVE_DEST_1: 
'/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf' (thread 1 sequence 6)
 (test11g)
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_10: 
'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_3dh5pld1_.arc'
 (test11g)
Committing creation of archivelog 
'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_3dh5pld1_.arc'
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: 
'/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf'
 (test11g)
Committing creation of archivelog '/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf'
ARC3: Completed archiving thread 1 sequence 6 (627362-628594) (test11g)

Read details about the log trace level here and general alert log related stuff here.

Oracle 11g: basic rundown

Ok, so 11g has been out some time but I've been to busy to really dig in to it until now. 11g is probably the first release that doesn't really many new features; no, before you send me angry e-mails hear me out :)
Take a look at the new features list. What do most of these features do? Easy, they just make the most out of the fairly old but totally outstanding REDO and UNDO management in Oracle. Why not use what we already got? Ok, some things are totally new features like the improved partitioning and new data types for the medical and life-sciences industry.

Partitioning and compression
The coolest things in 11g is probably the VLDB features for partitioning and compressions, can really reduce storage costs (at the same time as storage costs are plummeting with the competition from iSCSI). It is now possible to create "automatic" partitions, i.e. instead of adding a partition every month with DDL when doing a bulk load Oracle can automatically partition the table as per predefined rules. Pretty cool and saves a lot of time.

Direct NFS
Another pretty cool feature, at least for me as I'm using a lot of Netapp NFS storage, is the Direct NFS client in Oracle (Linux). Switch to the direct NFS lib and Oracle will do NFS ops outside the Linux kernel, which removes the NFS caching layer and kernel block device mapping etc. Gives a quite massive performance increase on some workloads. This is what I'm playing most with at the moment.

Data Guard
Lots of new cool stuff for Data Guard. Most significantly is probably the fact that you can have an open standby, available for real-time queries such as reporting applications and other read only operations. In fact, you can even open a Data Guard instance in read-write mode and mess about with it and later just flashback to a SCN where the db was in sync with the Data Guard master db and just roll forward to the current SCN using the normal FAL services. How sweet isn't that for DR testing and schema change testing. DR testing during live operations, that's a couple of late nights saved.
Other new data guard features include redo shipping compression, the normal improvements to the DG broker, sqlplus manageability etc.


Workload replay
Another thing that is really useful for deployment testing and change control is the database replay feature, one can "record" all transactions over a period of time and replay them after a change has happened to ensure performance and functionality has not degraded.


Improved ASM features.
You can now bring online a replaced drive and new writes will go directly to the drive while Oracle sync data from a redundant mirror in the background. Reduces resync time quite a fair bit.

PL/SQL
Couple of improvements to plsql;
Use of sequences in declares.
Real compound triggers.

New data types
Not really my thing but probably useful for a lot of people.
Spatial and multimedia support for medical imaging, life-sciences and other fairly odd things. :) Docs here.

A couple of minor things have changed as well, the alert log lives in a new place (with it's buddies in the new diag system), the logging levels in the alert log can now be adjusted as well (got a blog entry about that in a couple of days).

Lots of fun things to play with and to blog about.

Ah, almost forgot. Larry pulled a version hack on us again. As some of you may now there was never an Oracle Version 1 release, the first version was called 2 since Larry Ellison didn't think anyone would buy version 1. The first (public) 11g release is 11.1.0.6

Limit user sessions in Oracle

Developers sometimes enjoy configuring their JDBC datasources after their own likings, testing a performance issue by having a gazzillion threads to the database "just in case we need them".
Well in my opinion, if you need what is obviously too many threads, then there is a coding issue.
So to make people think twice before flooding a service with connections I've started limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration;

oracle@htpc:~$ rsqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 30 21:56:12 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 2;

Profile created.

SQL> create user fish identified by fish123
  2  default tablespace data profile sesslimit;

User created.

SQL> grant create session to fish;

Grant succeeded.

SQL> connect fish/fish123
Connected.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- I'll start two sessions in another terminal.
SQL> connect fish/fish123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


SQL>

Reclaiming LOB space in Oracle

Reclaiming space in Oracle can sometimes be a bit of a "problem", not really a problem it just works in a funny way. It's a quite common question I get and users are usually happy with a manual alter table table_name shrink space compact;, but what do we do for lobs?
We need a manual reclaim for the lob column.

A little demo:

(spinner1)oracle@spinner[~/lob_test]$ rsqlplus hlinden/hlinden

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 14 12:19:02 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Create a table and sequence to play with
HLINDEN@spinner1> create table lob_test (id number, data blob);

Table created.

HLINDEN@spinner1> create sequence lob_test_seq;

Sequence created.

-- Load 50 rows with 1.5Mb blobs (see code bellow)
HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

-- Find out what our lob segment is called
HLINDEN@spinner1> select object_name,object_type from user_objects where
  2  created>sysdate-interval '5' minute and object_type='LOB';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000199575C00002$$      LOB

-- Display the current size of the lob segment
HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
        75

-- Ok, let's delete those blobs and see what the size is after
HLINDEN@spinner1> delete from lob_test purge;

50 rows deleted.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
        75

-- Still 75Mb, hm, perhaps it recycled if we insert more data?

HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
       150

-- Nope, not recycled. We need to issue a shrink command to free up the 
-- space immediately
HLINDEN@spinner1> delete from lob_test;

50 rows deleted.

HLINDEN@spinner1> alter table lob_test modify lob (data) (shrink space);

Table altered.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
         0
-- All gone!

My simple blob loading code:

DECLARE
  src_file      BFILE := bfilename('TMP', 'data.dat');
  dst_file      BLOB;
  lgh_file      BINARY_INTEGER;
  cur_id        NUMBER(10);
BEGIN
  FOR i IN 1..50
  LOOP
    INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
          RETURNING id into cur_id;
    -- lock record
    SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
    lgh_file := dbms_lob.getlength(src_file);
    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
    dbms_lob.fileclose(src_file);
  END LOOP;
END;
/

Oracle 11g - one month to go

Yep, exciting times. July 11th, lots of new cool stuff.

Read about a couple of new things in Insider .

If you're lucky enough to be in New York, sign up for the launch event!

Run system commands from Oracle with PL/SQL

I friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.
He had full access to Oracle with sysdba/dba roles etc, but not SSH.
I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.

One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.
Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.
I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.
The temporary spool file handling in my example is quite poor, but works. :)
A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution.

Example of use:

oracle@htpc:~$ rsqlplus hlinden/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 30 21:55:06 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> @system_run

Procedure created.

SQL> exec system_run('ls -l /home/oracle/bin');
total 12
-rwxr-xr-x 1 oracle dba  797 Nov  5  2006 backup_controlfile.sh

PL/SQL procedure successfully completed.

SQL> exec system_run('uname -a');
Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux

PL/SQL procedure successfully completed.

SQL>

And here is the procedure code:

CREATE OR REPLACE PROCEDURE system_run(cmd IN varchar2)
IS
  script_file   varchar2(40) := 'my-temp-script.sh';
  script_data   varchar2(4000);
  MyFile        utl_file.file_type;
  d             varchar2(4000);
  dump_file     varchar2(40) := '/tmp/my-temp-file.dat';
  dump_type     utl_file.file_type;
BEGIN
  -- Open file
  MyFile := utl_file.fopen('TMP',script_file,'w');
  -- Write data to file
  script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;
  utl_file.put_line(MyFile, script_data, FALSE);
  -- Close file
  utl_file.fflush(MyFile);
  utl_file.fclose(MyFile);
  -- Purge old logs, no fun anyway
  dbms_scheduler.purge_log(JOB_NAME=>'TEST');
  -- Execute script
  -- The job is created as disabled as
  -- we execute it manually and will
  -- drop itself once executed.
  dbms_scheduler.create_job(
    job_name   => 'TEST',
    job_type   => 'EXECUTABLE',
    job_action => '/bin/bash',
    number_of_arguments => 1,
    start_date => SYSTIMESTAMP,
    enabled    => FALSE);
  dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);
  dbms_scheduler.enable('TEST');
  -- Wait for the job to be executed
  -- usually done within 1 second but
  -- I set it to 2 just in case.
  dbms_lock.sleep(2);
  -- Open the output file and
  -- print the result.
  dump_type := utl_file.fopen('TMP',dump_file,'r');
  loop
    begin
      utl_file.get_line(dump_type,d);
      dbms_output.put_line(d);
    exception
      when others then
        exit;
    end;
  end loop;
  utl_file.fclose(dump_type);
  -- Clean up our temp files
  utl_file.fremove('TMP', script_file);
  utl_file.fremove('TMP', dump_file);
END;
/

How to do an "insert ignore" in Oracle

Ok, I should start off with a disclaimer. This is not a good idea to do.
Using the "insert ignore" statement is a way to let MySQL insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.
Sounds like a great way to screw up data doesn't it? Should not be used unless you really know what is going on.

Ok, in MySQL we can do this.

mysql> insert ignore into a select * from b;
Query OK, 1 row affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

But can this be done in Oracle without to much fuss? After a discussion on IRC a guy asked why can't we simply use the merge statement?
Well we can and it is probably a quite good suggestion if you *really* want to ditch those dupe rows.

-- So what do we got?
SQL> select * from a;

         A          B
---------- ----------
         1          2
         2          3

SQL> select * from b;

         A          B
---------- ----------
         1          3
         3          4

SQL> merge into a using b on (a.a=b.a) 
  when not matched then insert values (b.a,b.b);

1 row merged.

SQL> select * from a;

         A          B
---------- ----------
         1          2
         2          3
         3          4

SQL>

-- Let's rollback that and have a look at the exeuction plan.

SQL> rollback;

Rollback complete.

SQL> set autotrace on
SQL> merge into a using b on (a.a=b.a) 
  when not matched then insert values (b.a,b.b);

1 row merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 1973318225

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |     2 |    64 |     7  (15)| 00:00:01 |
|   1 |  MERGE               | A    |       |       |            |          |
|   2 |   VIEW               |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     2 |    64 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| B    |     2 |    52 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| A    |     2 |    12 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."A"(+)="B"."A")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          5  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        819  bytes sent via SQL*Net to client
        778  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Looks ok.

Oracle under CentOS 5

I'm a bit late on this one, but ...
CentOS version 5 was released the other day. For those of you unfamiliar with CentOS it is RedHat Enterprise Linux recompiled and supported by the open source community.
Pretty much RHEL for free.
I can't say there is anything *really* exciting in the new version of RedHat, sure there is Xen support and the general updated versions of the applications included.
New version of Open Office, PostgreSQL etc.

One big difference is that everything has to be NTPL threaded. NPTL threading is the first real *good* threading implementation in Linux. The old Linux threads model is way old. Although NPTL has been available (and the default) in RHEL since version 3, it is not the only supported threading model.

-So what does this mean for Oracle?
No more LD_ASSUME_KERNEL hacks.
10g is fine with this, no problem at all. However 9i will have some issues with this, the old way to get Oracle running was simply to set LD_ASSUME_KERNEL to 2.4.19 and Linux would use the Linux threads model. No go in version 5.
Big question, will Oracle support 9i under RHEL5? I'm honestly not to bothered, by the time RHEL5 is mature and tested I really hope that people have switched to 10gR2.
9iR2 has been around for a lng time now and will be for some time. But new system design implementation on 9iR2?

I've done a quick test install of Centos 5 and 10gR2 (10.2.0.1 and patchset 10.2.0.2) and the install was very painless, pretty similar to Centos 4 with a few changes in the RPM's needed.
RedHat did add a few SHM parameters to sysctl.conf so that section needs reviewing though.

Understanding Oracle pseudo-columns

All the cool people I meet at Oracle events keep referring to "function based indexes" as pseudo-columns. I never really understood the whole thing about it, sure I figures one could consider it a pseudo-column as it was sort of a calculated value that was stored in the database. Never thought much about investigating it either.
However, when playing around with some composite indexes including functions today I finally got it. They are indeed pseudo-columns, or calculated columns as I think Oracle wants to call them in 11g.

As always,
an example:

SQL> create table d as select * from all_objects;

Table created.

SQL> insert into d select * from d;

66242 rows created.

SQL> update d set object_id=rownum;

132484 rows updated.

-- Ok, got a test table. Let's index it with some pseudo stuff.SQL> create index d_1 on d(upper(status));

Index created.

SQL> select index_name,column_name from user_ind_columns where table_name='D';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
D_1                            SYS_NC00014$

SQL> select index_name,column_expression from user_ind_expressions
  2  where table_name='D';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------
D_1                            UPPER("STATUS")

SQL> create index d_2 on d(object_id,upper(status),timestamp);

Index created.

SQL> select index_name,column_name from user_ind_columns where table_name='D';

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
D_1                            SYS_NC00014$
D_2                            OBJECT_ID
D_2                            SYS_NC00014$
D_2                            TIMESTAMP

SQL> select index_name,column_expression from user_ind_expressions
  2  where table_name='D';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------
D_1                            UPPER("STATUS")
D_2                            UPPER("STATUS")

-- Thats the index bit, we got two indexes with the same "function",
-- so Oracle will index the same pseudo column! Eureka!
-- Some tests then...SQL> set autotrace traceonly exp
SQL> select object_name from d where upper(status)='T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2452139598

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    21 |   462 |   165   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| D    |    21 |   462 |   165   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | D_1  |   418 |       |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("STATUS")='T')

Note
-----
   - dynamic sampling used for this statement

-- A column can go in the where clause, even pseudo-columns.SQL> select object_name from d where sys_nc00014$='T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2452139598

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    21 |   462 |   165   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| D    |    21 |   462 |   165   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | D_1  |   418 |       |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("STATUS")='T')

Note
-----
   - dynamic sampling used for this statement


-- Since it is a column we can obviously select it as well.SQL> select SYS_NC00014$ from d where object_id=500;

SYS_NC0
-------
VALID

Execution Plan
----------------------------------------------------------
Plan hash value: 2591304836

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    18 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| D_2  |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=500)

Note
-----
   - dynamic sampling used for this statement

-- You can even rename the pseudo-column.
-- Oracle did actually dump core after I did that so better not.SQL> alter table d rename column SYS_NC00014$ to dog;

Table altered.

SQL> alter table d rename column dog to SYS_NC00014$;

Table altered.

Oracle constraints in XML data

Oracle introduced pretty cool XML support in 9i, it's even cooler in 10g and I don't understand why people don't use it more often. I keep seeing XML data stored in CLOB's all the time. Why not store it as proper XML, it's possible to index, query and even update individual XML elements, attributes or nodes. Fast, simple, easy, no need for full text indexes. Performance of xpath queries is pretty good if indexed correctly, 25 000 XML documents 10k each is still in the sub second range when hitting an index.

One thing that can be quite nice to have in the XML store is constraints to avoid duplicate data, indexes on XML data are pretty much plain standard pseudo-column indexes (or "functional indexes" as some refer to them as).
We just use the basic extract() or extractValue() functions in Oracles XML feature set.

Here's an example on how to to create unique constraints (indexes) on XML elements (or attributes):

SQL> create table x (a xmltype);

Table created.

SQL> insert into x values('<type><name>dog</name></type>');

1 row created.

SQL> insert into x values('<type><name>cat</name></type>');

1 row created.

SQL> create unique index xui on x(extractValue(a, '/type/name'));

Index created.

SQL> insert into x values('<type><name>cat</name></type>');
insert into x values('<type><name>cat</name></type>')
*
ERROR at line 1:
ORA-00001: unique constraint (HLINDEN.XUI) violated


SQL> insert into x values('<type><name>fish</name></type>');

1 row created.

-- Lets try the constraint on an attribute.
-- attributes are handeled just like elements but need a @ sign prefix

SQL> drop index xui;

Index dropped.

SQL> truncate table x;

Table truncated.

SQL> insert into x(a) values('<type id="1"><name>sally</name></type>');

1 row created.

SQL> insert into x(a) values('<type id="2"><name>bob</name></type>');

1 row created.

SQL> create unique index xui on x(extractValue(a, '/type/@id'));

Index created.

SQL> insert into x(a) values('<type id="2"><name>carol</name></type>');
insert into x(a) values('<type id="2"><name>carol</name></type>')
*
ERROR at line 1:
ORA-00001: unique constraint (HLINDEN.XUI) violated


SQL> insert into x(a) values('<type id="3"><name>carol</name></type>');

1 row created.

-- Ok, lets see if we can have duplicate names.

SQL> insert into x(a) values('<type id="4"><name>carol</name></type>');

1 row created.

SQL>

And some docs to read.

Viewing bind variable values in 10g

Oracle 10g introduced a couple of new nice views to help tune queries that use bind variables.
One cool view is v$sql_bind_capture, this view hold the latest captured value for each bind variable in queries that has been run.
First have a look in v$sql to find the SQL query you are looking for, join the sql_id to v$sql_bind_capture and to view the bind variable values for that query.
Example:

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='f8pavn1bvsj7t'
/

SQL_TEXT                                    BIND_NAME  BIND_STRIN
------------------------------------------- ---------- ----------
select con#,obj#,rcon#,enabled,nvl(defer,0) :1         9110
 from cdef$ where robj#=:1

I found a pretty bad example here, an Oracle internal dictionary query, but it should show the point.

Flashback a user or schema in Oracle

Since 10g we've had the quite cool feature 'flashback', or rather we've had it since waay back. But now Oracle gave us an easy way to use it. We have the recyclebin as a new feature though. Cool but sometimes a bit confusing.

Flashback can be done at two levels, the whole database or for a single object. Why on earth didn't Oracle include a "flashback schema" feature. I would guess most users use flashback when doing testing, like schema upgrades and things like that. And if the test fails you want to restore the tables to before the scripts ran (ok workspaces comes to mind here but that's another post).
I took some time and wrote a script to flashback all tables and dependant objects from the recyclebin and to flashback existing tables to a previous version.
Since flashback require row movement to be enabled the script checks if it needs to enable that for the tables and will do so if needed.

-- Ok, let's create a little mess we can clear up.
SQL> drop table i;

Table dropped.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

SQL> update a set d=sysdate;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 21-JAN-07 11.29.53.000000 PM
         2 21-JAN-07 11.29.53.000000 PM

-- Commited and all! We sure screwed that up

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
T                              TABLE
D                              TABLE
BIN$J5ZNZK2dxdvgQKjAKF9ZFQ==$0 TABLE

4 rows selected.

-- Lets run the script to generate our flashback script
-- The script will prompt you for the number of minutes you want to go back
SQL> @flashback_user
How far back do you want to flashback (in minutes)?
Enter value for minute: 8
         8
testuser

Spooling flashback_user_testuser.sql

alter table T enable row movement;
flashback table A to timestamp sysdate - interval '8' minute;
flashback table T to timestamp sysdate - interval '8' minute;
flashback table D to timestamp sysdate - interval '8' minute;
flashback table I to before drop;

SQL> @flashback_user_testuser

Table altered.


Flashback complete.


Flashback complete.


Flashback complete.


Flashback complete.

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
D                              TABLE
T                              TABLE
I                              TABLE

4 rows selected.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

2 rows selected.
-- And we are back in business.

Download my script here: http://halisway.hifichoice.com/flashback_user.sql.

Read more about flashback here.

Balancing SGA in Oracle 9i

Now with 10g having sga_target for automatically managing your SGA memory size some people get lazy and just forget about SGA. But most of us still have 9i databases to maintain.

Time for a quick overview on how to reduce disk i/o with the help of db_buffers.

I've got a Sun v480 with 4Gb RAM running one instance, it's not under a lot of load but could do with some tuning.
Lets look how our current SGA settings looks:

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
sga_max_size                         big integer 2283246744

SQL> select * from v$sgastat order by pool,bytes;

POOL        NAME                            BYTES
----------- -------------------------- ----------
java pool   free memory                  67108864
shared pool trigger source                    152
shared pool fixed allocation callback         496
shared pool trigger defini                    576
shared pool trigger inform                    920
shared pool PLS non-lib hp                   2088
shared pool joxs heap init                   4240
shared pool KQR S SO                         5416
shared pool table definiti                  18984
shared pool PX subheap                      28096
shared pool session heap                    29560
shared pool KGK heap                        33368
shared pool DG Broker heap                  39200
shared pool MTTR advisory                  388024
shared pool errors                         390856
shared pool sessions                       905840
shared pool message pool freequeue         940944
shared pool sim memory hea                1014808
shared pool KSXR receive buffers          1034000
shared pool FileIdentificatonBlock        1791824
shared pool parameters                    1827072
shared pool PL/SQL DIANA                  1960192
shared pool 1M buffer                     2098176
shared pool Checkpoint queue              2622720
shared pool KQR M PO                      2885104
shared pool dictionary cache              3229952
shared pool KQR L PO                      3372488
shared pool event statistics per sess     3762720
shared pool KQR L SO                      5260312
shared pool KGLS heap                     5424464
shared pool KQR M SO                      6135256
shared pool FileOpenBlock                11813536
shared pool PL/SQL MPCODE                22044808
shared pool miscellaneous                25966760
shared pool library cache                66948552
shared pool sql area                    166511368
shared pool free memory                 248709688
            fixed_sga                      734360
            log_buffer                     787456
            buffer_cache               1073741824

40 rows selected.

Ok, we can see that we do have quite some free memory
in the shared pool, what should we do with that?
from looking at iostat and wait statistics I've noticed that the disk
subsystem (a simple Sun 3310 in this case) is getting a bit hammered sometimes.
Lets see how the db cache is doing.

SQL> select size_factor, size_for_estimate, estd_physical_read_factor
  2    from v$db_cache_advice order by size_factor;

SIZE_FACTOR SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR
----------- ----------------- -------------------------
      .0938                96                 4344.0852
      .1875               192                  163.0333
      .2813               288                    23.782
       .375               384                   22.5502
      .4688               480                   20.6444
      .5625               576                   10.4806
      .6563               672                    1.4957
        .75               768                    1.0948
      .8438               864                    1.0367
      .9375               960                    1.0102
          1              1024                         1
     1.0313              1056                     .9999
      1.125              1152                      .813
     1.2188              1248                     .7185
     1.3125              1344                     .5829
     1.4063              1440                      .536 <- Lets aim for this
        1.5              1536                     .5196
     1.5938              1632                     .5139
     1.6875              1728                     .5079
     1.7813              1824                     .5055
      1.875              1920                     .5043

21 rows selected.

Right, from looking in the v$db_chace_advice view we can determine that a few hundred Mb more of cache would be a good idea. There is quite a lot of free memory at the OS level, so we can probably increase the sga_max_size with 250Mb as well.

-- Reduce the shared pool with 150Mb.
SQL> alter system set shared_pool=379584512 scope=spfile

System altered.

-- Add max size with 250Mb
SQL> alter system set sga_max_size=2545390744 scope=spfile;

System altered.

-- Add the 250Mb we added + the 150Mb we reduced from the shared pool.
SQL> alter system set db_cache_size=1493172224 scope=spfile;

System altered.

-- Restart Oracle
SQL> shutdown immediate
...
SQL> startup

That should do the trick, lets see what the server thinks about things in a couple a weeks time or so.

Don't forget:
Please have more than one look at the statistics before you do anything, the free memory in the shared pool could be free today but needed all other days. Take a few snapshots over time and analyze the results before doing any changes. And whatever you do, don't over allocate SGA.
The last thing you want is the SGA to be swapped to disk or the system swapping out other things.

Cleaning up sqlplus cut and pastes in vim

Classical problem, you write a short piece of SQL in sqlplus, you then want to cut and paste it to vim edit it. But then you get the really annoying line numbers in front of each line.
Sure you can use save myquery in sqlplus and open the file myquery.sql in vim, but perhaps you want to run vim on another machine, perhaps you run gvim.
So ok, time to get rid of the line numbers.

Here's what we got in sqlplus:

SQL> list
  1  select
  2  e.ename,
  3  d.dname
  4  from
  5  dept d,
  6  emp e
  7  where
  8  d.deptno=e.deptno
  9* order by e.ename
SQL>

Lets cut n paste those numbered lines to a vim session.
We now need to delete the first 4 characters of each line. Easy.
Switch to visual mode, jump to the end of the file, step two rows right, hit delete char.
Or in vim command terms (in normal mode (esc-esc)).

ctrl-v G l l x

Breakdown:
ctrl-v to get in to visual mode
G to jump to the end of the file
l to move the cursor right
x to delete the selected area

Visual mode is quite powerful, you can use almost the same commands to comment out code.
Place the cursor in the first line you want to comment out, hit ctrl-v to do a visual mark, use j to move the cursor down to the last line you want to comment out, hit I to get insert, type two hyphens and hit escape. Done.
In vim command terms:

ctrl-v j j j I -- esc

Using Solaris projects with Oracle

In older versions of Solaris (9 and earlier) we used to set the system resource control in /etc/system, in Solaris 10 and later we do it in a neater way. No more reboots or mocking about, all online, even while Oracle is running.
It's all about projects now.
I've just finished installing 10gR2 under Solaris Express Community Release Build 54 (long name ey?) under VMware on my new laptop.
Example project:

root@solvm[~]$ projadd -U oracle -K \
"project.max-shm-memory=(priv,4g,deny);\
project.max-sem-nsems=(priv,256,deny);\
project.max-sem-ids=(priv,100,deny);\
project.max-shm-ids=(priv,100,deny)" oraproj

-- Lets review our project

root@solvm[~]$ su - oracle
Sun Microsystems Inc.   SunOS 5.11      snv_54  October 2007
oracle@solvm[~]$ projects
default oraproj
oracle@solvm[~]$ projects -l oraproj
oraproj
        projid : 101
        comment: ""
        users  : oracle
        groups : (none)
        attribs: project.max-sem-ids=(priv,100,deny)
                 project.max-sem-nsems=(priv,256,deny)
                 project.max-shm-ids=(priv,100,deny)
                 project.max-shm-memory=(priv,4294967296,deny)
oracle@solvm[~]$

It's described slightly different in the Oracle installation manual but I'd say my way is slightly better. prctl is legacy. You can use usermod -K project=oraproj oracle to set the new project as the users default project (not really necessary)

Manual operations in an automatic SGA database

Oracle 10g introduced the nifty feature of automatic SGA, simply set the parameter sga_target and sga_max_size to the maximum size you can afford and Oracle will do the rest. But we still have a few manual settings, say you want a tablespace with a non-default block size (also a new feature) than the database default and you create, say a 4k block size, tablespace in a 8k database and you allocate a db_4k_cache_size for that. How do Oracle allocate that memory?

Test:

SQL> select name,value from v$parameter
       where name like 'sga_target'
     /

NAME            VALUE
--------------- --------------------
sga_target      599785472

SQL> select name,round(bytes/1024/1024) M from v$sgainfo;

NAME                                              M
---------------------------------------- ----------
Fixed SGA Size                                    2
Redo Buffers                                      6
Buffer Cache Size                               416
Shared Pool Size                                140
Large Pool Size                                   4
Java Pool Size                                    4
Streams Pool Size                                 0
Granule Size                                      4
Maximum SGA Size                                572
Startup overhead in Shared Pool                  68
Free SGA Memory Available                         0

11 rows selected.

SQL> alter system set db_4k_cache_size=4M;

System altered.

SQL>  alter system set db_4k_cache_size=0;

System altered.

-- What actually happend here, from what 
-- did Oracle steal those 4Mb
-- Let's query the v$sga_resize_ops view to see that was done.

SQL> select parameter,oper_type,initial_size,target_size 
       from v$sga_resize_ops 
       where start_time>sysdate-1
     /

PARAMETER        OPER_TYPE     INITIAL_SIZE TARGET_SIZE
---------------- ------------- ------------ -----------
db_cache_size    SHRINK           436207616   427819008
db_4k_cache_size GROW                     0     8388608
db_4k_cache_size SHRINK             8388608           0
db_cache_size    GROW             427819008   436207616

SQL>

Ok, we can see that Oracle shrank the db_cache_size with the requested size and allowed the db_4k_cache_size to grow and when reset back to zero it freed the memory and grew the db_cache_size. Oracle could just as well have allocated the memory to the large_pool if the automatic SGA engine thought that would make more sense.
I've got the last 7 days worth for resize ops in my weekly database report that is e-mailed to me every Monday morning, it's definitely worth keeping an eye on whats going on. Sometimes it's just easier to configure the SGA manually.