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.

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

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

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.

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

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.

Monitoring RMAN I/O performance

When trying to cut rman backup times there are a lot of parameters to try to tune, database settings, storage subsystem settings, file system settings etc etc.
It can sometimes be hard to remember all the results you get, luckily Oracle keeps them for you. Just query the v$rman_backup_job_details view for the results.

Example:

column END_TIME format a16
column START_TIME format a16
column COMMAND_ID format a20

select 
 command_id, 
 to_char(start_time,'YYMMDD HH24:MI') start_time,
 to_char(end_time,'YYMMDD HH24:MI') end_time,
 round(input_bytes/1024/1024) backup_size,
 round(INPUT_BYTES_PER_SEC/1024/1024) in_mb_sec,
 round(OUTPUT_BYTES_PER_SEC/1024/1024) out_mb_sec 
from 
 v$rman_backup_job_details 
where
 start_time > sysdate - 7
order 
 by start_time
/

COMMAND_ID           START_TIME       END_TIME         BACKUP_SIZE  IN_MB_SEC OUT_MB_SEC
-------------------- ---------------- ---------------- ----------- ---------- ----------
2006-12-10T00:34:25  061210 00:35     061210 00:45           13722         22         10
2006-12-10T23:17:48  061210 23:17     061210 23:26           13784         26         12

Date range generation in Oracle

Another interesting question someone asked in the IRC #oracle chat.
-How do I create a table with all dates from 2000 until today in it?
It's rather simple in plain SQL, no PL/SQL or scripting required.

SQL> create table date1 (d date);

Table created.

SQL> insert 
  into date1(d) 
select 
  to_date('00-01-01','YY-DD-MM') + level 
from 
  dual 
where 
  (to_date('00-01-01','YY-DD-MM')+level) < sysdate 
connect 
  by level<=1000000
/

2530 rows created.

SQL> select min(d) s, max(d) e from date1;

S         E
--------- ---------
02-JAN-00 05-DEC-06

SQL>

Oracle trace event 10132

The new "in" Oracle trace event seems to be 10132, it's pretty cool. Most Oracle DBA's and developers have probably at least heard about trace levels 10046 and 10053.
10132 is a different from the classic two in the sense that it is is really easy for anyone to intepret, the 10132 trace event gives you a full execution plan and optimizer settings in the udump (user_dump_dest) directory.
It's an easy thing to throw in a script or even as a logon trigger when troubleshooting query problems. Think hibernate or other (weird) ORM tool.

Lets take a look:

SQL> alter session set events
  2  '10132 trace name context forever, level 12';

Session altered.

SQL> select e.ename,d.dname from dept d, emp e where
  2  d.deptno=e.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
...
MILLER     ACCOUNTING

14 rows selected.

SQL>

And the trace we get looks like this.

Oracle database links using full TNS entries

Database links can be most useful, say you want to extract data to a reporting system or something like that. This is a quite common scenario people have problems with and come to the Oracle support IRC channel and ask about, especially developer with just a user account on the remote database and plain user account in the target database. They want to create a database link but don't have access to tnsnames.ora file at the OS level on the server, they may not even have SSH access to the server.
No problem, you can use the full TNS entry pretty much everywhere you can use the TNS alias, just remember to single quote it.

Database link example:

[hlinden@whale dblink]$ rsqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 29 11:17:26 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select db_link from user_db_links;

no rows selected

SQL> CREATE DATABASE LINK prodsyslink
 CONNECT TO ro_user IDENTIFIED BY ro_user_pass USING
 '(DESCRIPTION=(ADDRESS_LIST=(
   ADDRESS=(PROTOCOL=TCP)(HOST=prodserver)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=prod_db)))'
/

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
PRODSYSLINK

SQL> select * from t@prodsyslink;

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

SQL>

Cool, now it's possible to create a nice materialized view or whatever is needed.

Oracle docs about CREATE DATABASE LINK.

Oracle merge-statement

The merge statement has been in Oracle since version 9, but is still not very widely used.
When looking at data loading and merging code I think the most common bit of PL/SQL I've seen is the "merge" statement written in pl with slow and boring if-statements to see check if an entity exists in a table and if it exists the pl-code will do an update, otherwise it will do an insert.
Two words, super slow.

A much better solution is to use the merge-statement which does exactly that, it's basically
"if exists then update else insert". Merge data from one data entity to another.
The basic concept is sort of a join between the loading entity and the destination table.
You can load from a normal table or from select query.

Example:

SQL> select * from emp_load;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30

SQL> MERGE INTO emp e USING emp_load l
  ON (e.empno=l.empno)
  WHEN MATCHED THEN
    UPDATE SET
      e.ename = l.ename,
      e.job = l.job,
      e.mgr = l.mgr,
      e.hiredate = l.hiredate,
      e.sal = l.sal,
      e.comm = l.comm,
      e.deptno = l.deptno
  WHEN NOT MATCHED THEN
    INSERT VALUES (
      l.empno, l.ename, l.job,
      l.mgr, l.hiredate,
      l.sal, l.comm, l.deptno
    )
/

5 rows merged.

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30
 7369 SMITH      CLERK      7902 17-DEC-80   800                  20
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600      300         30
 7521 WARD       SALESMAN   7698 22-FEB-81  1250      500         30
 7566 JONES      MANAGER    7839 02-APR-81  2975                  20
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850                  30
 7782 CLARK      MANAGER    7839 09-JUN-81  2450                  10
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7839 KING       PRESIDENT       17-NOV-81  5000                  10
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500        0         30
 7876 ADAMS      CLERK      7788 23-MAY-87  1100                  20
 7900 JAMES      CLERK      7698 03-DEC-81   950                  30
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7934 MILLER     CLERK      7782 23-JAN-82  1300                  10

16 rows selected.

If you want to load from a query you can use this syntax:

MERGE INTO emp e USING 
    (select * from emp_load 
      where hiredate > sysdate - 7) l
  ON (e.empno=l.empno)
...

Check out the merge docs .