MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for April, 2008

The IS OF operator for object type comparisons

without comments

You can do base type and subtype comparisons with the IS OF or IS NOT OF operators. They work like the typeof operator in Java. They also eliminate null values when you use them in a WHERE clause or an IF block. They work against all SQL user-defined object types. The prototypes are:

object_variable IS OF (object_type1 [, object_type2, [object_type(n+1)]])
object_variable IS NOT OF (object_type1 [, object_type2, [object_type(n+1)]])

The left operand should be an object column or element of a collection. The IS OF returns true when the object_variable matches the object type or a member of the list of object types. The IS NOT OF returns true when the object_variable doesn’t match, and both return false when the object_variable is a null value.

Written by maclochlainn

April 28th, 2008 at 3:01 am

Oracle Database LOBs In-depth

without comments

Last month I presented the Oracle Database LOBs In-depth at the Utah Oracle User’s Group (UTOUG) Training Days. You can find the slides and sample programs here. The presentation illustrates how the transaction context works and how to access, maintain, and manage LOBs.

Written by maclochlainn

April 22nd, 2008 at 3:37 am

Cleaning up wasted LOB space

with 10 comments

After replacing the contents of a BLOB or CLOB column, you will see the size grow because old indexes and segments aren’t deleted or removed from the index. The only way to get rid of the old information is to perform some simple table maintenance. The following provides an example of dumping redundant or obsolete space and indexes.

You should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. Such extremes as the regular expression in that query aren’t needed when DBAs ensure that LOBs are created with named segments. In this test case, this is the starting point:

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416

You create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.

ALTER TABLE item ADD (item_temp CLOB);
UPDATE item SET item_temp = item_desc;

When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.

ALTER TABLE item ADD (item_desc CLOB);
UPDATE item SET item_desc = item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the temporary column after making the change.


You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

Matching LOB Indexes and Segments

with 7 comments

I enjoyed Tom Kyte’s example of how you find matching a LOB Index to Segment (Expert Oracle Database Architecture, pp. 542). I’ve noticed variations of it posted in various locations. While it works well for sample scheme that have only one LOB, the following works for any number of LOBs in any schema. This simplifies working with system- and user-defined segment names. The first CASE statement ensures that joins between user-named segment names are possible. The second CASE statement ensures two things: (a) Joins between system generated segment names don’t throw an error when matching unrelated system generated return values found in the DBA_SEGEMENTS view; and (b) Joins between named segments are possible and don’t throw an error.

COL owner         FORMAT A5  HEADING "Owner"
COL column_name   FORMAT A10 HEADING "Column|Name"
COL segment_name  FORMAT A26 HEADING "Segment Name"
COL segment_type  FORMAT A10 HEADING "Segment|Type"
COL bytes                    HEADING "Segment|Bytes"
SELECT   l.owner
,        l.table_name
,        l.column_name
,        s.segment_name
,        s.segment_type
,        s.bytes
FROM     dba_lobs l
,        dba_segments s
WHERE    REGEXP_SUBSTR(l.segment_name,'([[:alnum:]]|[[:punct:]])+'
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END) =
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
AND      l.table_name = UPPER('&table_name')
AND      l.owner = UPPER('&owner')
ORDER BY l.column_name, s.segment_name;

It produces the following type of output:

Table  Column                               Segment      Segment
Name   Name       Segment Name              Type           Bytes
------ ---------- ------------------------- ---------- ---------
PLSQL  ITEM_BLOB  SYS_IL0000074435C00007$$  LOBINDEX      65,536
PLSQL  ITEM_BLOB  SYS_LOB0000074435C00007$$ LOBSEGMENT 2,097,152
PLSQL  ITEM_DESC  SYS_IL0000074435C00006$$  LOBINDEX      65,536
PLSQL  ITEM_DESC  SYS_LOB0000074435C00006$$ LOBSEGMENT   720,896

This should help you monitor growth of LOBs in your database. The maximum CHUNK size is 32.768. If you elect a value greater than that you raise an ORA-22851 error.

Written by maclochlainn

April 17th, 2008 at 6:16 pm

Oracle Database 11g PL/SQL New Features

with 2 comments

Last month I presented the Oracle PL/SQL 11g New Features at the Utah Oracle User’s Group (UTOUG) Training Days. You can find the slides here. My favorite new feature is that you no longer have to select a sequence into a variable to use it in a SQL statement in a PL/SQL block. 

Written by maclochlainn

April 17th, 2008 at 4:59 pm

Posted in Oracle

Tagged with ,

The Beginning

without comments

Two guys I respect, Justin Kestelyn and Chris Jones at Oracle finally convinced me to maintain a blog. Hopefully, it’ll be interesting and valuable to others experimenting with Oracle technology.

Written by maclochlainn

April 17th, 2008 at 4:39 pm

Posted in Generic