Fetch 'em all

Sometimes it's useful to have access to Exadata cells metrics directly from your session. One can query the v$cell% family of views and using standard way of accessing the XML document content via XMLType/XMLTable fetch the exposed metrics/statistics or cells' configuration. However not all cell metrics are exposed via v$cell%. One of the alternatives would be to call dcli out and ask for more :-) It's costly though to call it very often so it would make sense to have a GTT as an intermediate place to put the data in and do extra queries when/if needed.

Anyway, one can quickly get an idea of what's going on out there and fetch cells biometrics without leaving our lovely sqlplus :-)

SQL> CREATE OR REPLACE DIRECTORY AM_BIN AS '/home/oracle/bin';

Directory created.

SQL>
SQL> CREATE TABLE cell$ (
  2    cellname                         VARCHAR2(64)
  3  , name                             VARCHAR2(64)
  4  , alertState                       VARCHAR2(16)
  5  , collectionTime                   VARCHAR2(26)
  6  , objectType                       VARCHAR2(64)
  7  , persistencePolicy                VARCHAR2(64)
  8  , unit                             VARCHAR2(64)
  9  )
 10  ORGANIZATION EXTERNAL (
 11    TYPE ORACLE_LOADER
 12    DEFAULT DIRECTORY am_bin
 13    ACCESS PARAMETERS (
 14      RECORDS DELIMITED BY NEWLINE
 15      NOBADFILE
 16      NOLOGFILE
 17      NODISCARDFILE
 18      PREPROCESSOR am_bin:'fetch_metrics.sh'
 19      FIELDS TERMINATED BY 0x'09'
 20      LRTRIM
 21      MISSING FIELD VALUES ARE NULL
 22      (
 23        cellname                     CHAR(64)
 24      , name                         CHAR(64)
 25      , alertState                   CHAR(16)
 26      , collectionTime               CHAR(26)
 27      , objectType                   CHAR(64)
 28      , persistencePolicy            CHAR(64)
 29      , unit                         CHAR(64)
 30      )
 31    )
 32    LOCATION (
 33      am_bin:'fetch_metrics.sh'
 34    )
 35  )
 36  REJECT LIMIT UNLIMITED
 37  /

Table created.

SQL> SET TIMING ON ECHO ON
SQL> COLUMN cellname FORMAT A30
SQL> COLUMN run_queue FORMAT A10
SQL> COLUMN cpu_time FORMAT A10
SQL> SELECT *
  2    FROM (
  3         SELECT cellname
  4              , name
  5              , unit
  6           FROM cell$
  7          WHERE name IN (
  8                  'CL_RUNQ'
  9                , 'CL_CPUT'
 10                )
 11         )
 12   PIVOT (
 13     MAX(unit)
 14     FOR
 15     name IN (
 16       'CL_RUNQ' AS run_queue
 17     , 'CL_CPUT' AS cpu_time
 18     )
 19   )
 20  /
CELLNAME                       RUN_QUEUE  CPU_TIME
------------------------------ ---------- ----------
cel11                          0.1        1.4 %
cel12                          0.1        1.7 %
cel14                          0.1        1.3 %
cel13                          0.3        1.3 %

Elapsed: 00:00:04.52
SQL> 
SQL> !cat /home/oracle/bin/fetch_metrics.sh
#!/bin/sh
# NB: ":" is replaced by tab
PATH=/bin:/usr/bin:/usr/local/bin
dcli -g /home/oracle/cell_group \
  "cellcli -e \"list metriccurrent attributes all where objectType = 'CELL'\"" | \
  sed -e 's/:/\t/'

SQL> 

lateral

документированная функциональность радует.

technical

On Wednesday last week I led a workshop at Oracle office in Burlington, MA. The goal was to outline how to develop on Oracle technology stack and avoid issues. We started at 8:30am east cost time (5:30am pacific), that was quite an early start of the day for me. Also, I had plenty of the material to present. The agenda was set for eight hours but it so happened that we spent two more doing extra presentation and having discussions. I still have to learn how to manage time doing big presentations. I guess I never had a chance :-) to talk for ten hours having anybody listening to me carefully. But the audience was great and eager to learn from my experience as well as folks wanted to share theirs. Good, down to earth questions and real problems and issues to discuss and think about solutions. Some aspects were covered in details, some we had to skim -- lack of time. I aimed folks to have 2-5% increase in their knowledge base comparing to what they had before the workshop. After we finished some stated that it's more than 5%! At least it was not time spent for nothing then -- good increase in performance! :-) There is a Russian saying: "first pancake comes as a lump" (="you must spoil before you spin") and it looks like even there were lumps here and there the pancakes came out tasty. :-)

Below is a snippet from the series of slides about the index growth and leaf blocks splits. Hope one would be interested to think how to hit the limit. We were using Oracle 11.2.

07/07/2011:
So, here is my very artificial example illustrating how fast an index can grow. The script below does perform a set of aggressive index blocks splits and forces that growth-on-steroids behavior. In a matter of seconds we can observe the ORA-00600 [6051].

Nowadays, it would be a rare case to see 2K block size used as a default block size of the database. So at first, I used a small block size for the index segment and for that a special tablespace is created. If you want to run the script make sure that your instance has a proper value for db_2k_cache_size, e.g.:

SQL> show parameter db_2k_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_2k_cache_size                     big integer 16M

CREATE TABLESPACE ora600_6051
  DATAFILE '/tmp/ora600_6051.dbf'
  SIZE 75M
  BLOCKSIZE 2048
/

Second, each index leaf block has one key value only. I am using a function-based index padding the original numeric value to 1469 bytes. FBI is used for the sake of clarity, since I did not want to insert long strings into the table.

CREATE TABLE ora600_6051 (
  v                                NUMBER
)
TABLESPACE ora600_6051
/
CREATE INDEX ora600_6051$i
  ON ora600_6051(SUBSTRB(LPAD(TO_CHAR(v, 'FM0000009'), 1469, '0'), 1, 1469))
  TABLESPACE ora600_6051
/

Third, and that's very cunning, I'm using a tricky way of populating the table. The order of inserted value is based on power of 2, but each set of values is reversed:

RANGE_BEGIN  RANGE_END
----------- ----------
          0          0
          1          1
          3          2
          7          4
         15          8
         31         16
         63         32
        127         64
        255        128
        511        256
       1023        512
       2047       1024
       4095       2048
       8191       4097

SET LINES 1000 TRIMSPOOL ON TERMOUT ON TIMING ON SERVEROUTPUT ON TIMING ON
BEGIN
  FOR b IN 0 .. 13
  LOOP
    FOR i IN REVERSE TRUNC(POWER(2, b - 1)) .. POWER(2, b) - 1
    LOOP
      INSERT INTO ora600_6051 VALUES(i);
      COMMIT;
    END LOOP;
  END LOOP;
END;
/

BEGIN
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [6051], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at line 6

Elapsed: 00:00:07.77
SQL> SET TIMING OFF
SQL> SELECT COUNT(*) FROM ora600_6051;

  COUNT(*)
----------
      8191

SQL> ANALYZE INDEX ora600_6051$i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_rows
  2       , lf_blks
  3       , br_rows
  4       , br_blks
  5       , height
  6       , height - 1 blevel
  7    FROM index_stats
  8  /

   LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS     HEIGHT     BLEVEL
---------- ---------- ---------- ---------- ---------- ----------
      8191       8191       8190      24445         24         23

As you can see an insertion of the very last value — 4096 — fails with ORA-600 i.e. we reached the limit. The primary purpose of this post is to illustrate the concept of index growth in Oracle RDBMS. Avoid using this approach to test how fast an index of your production application can grow... your DBA won't be happy to see ORA-600 here and there.

units.sql

Put it here.

Unit conversion script [pathetically :-) mimicking units (1)].

Usage: units.sql [n] <from time unit> [[to] <to time unit>]
Example:
  @units.sql sec to us
  1 second(s) = 1000000 microsecond(s)

  @units.sql 15 milliseconds second
  15 millisecond(s) = .015 second(s)

  @units.sql 30 seconds to day
  30 second(s) = .000347222 day(s) [*.00001157407]

Supported units: us, ms, cs, ss, mi, hh, dd

фразочка

какой OLTP не мечтает стать DW
старые грабли -- новые лбы

all you need is...

... sqlplus :-)

тюнили, тюнили, вытюнили...

Есть запрос для проверки прав доступа, время выполнения:

было cpu=0.000145s, elapsed=0.000168s
стало cpu=0.000135s, elapsed=0.000155s
в среднем 14.1 buffer gets

думаю улучить время-на-CPU до 0.000125-0.000127s [0.125ms]. Выполняется он почти сто миллионов раз за время восьмичасового стресс теста, смысл тюнить был. :) Интересно улучшать производительность, чтобы работало так, что и глазом не успел моргнуть!

this is nice

PARSING IN CURSOR #1 len=48 dep=0 uid=0 oct=3 lid=0 tim=1275528151753957 hv=3285711733 ad='50739908' sqlid='797w17b1xgyvp'
SELECT XMLQUERY('0' RETURNING CONTENT) FROM dual
END OF STMT
PARSE #1:c=30000,e=101615,p=0,cr=40,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1275528151753956
EXEC #1:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1275528151754077
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151754152
WAIT #1: nam='SQL*Net message from client' ela= 244 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151754466
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151754539
WAIT #0: nam='SQL*Net message from client' ela= 146 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151755204
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151755247
WAIT #0: nam='SQL*Net message from client' ela= 436 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151755712
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151755951
FETCH #1:c=0,e=314,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1275528151756057
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)'
WAIT #1: nam='SQL*Net message from client' ela= 506 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151756693
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151756736
LOBREAD: c=0,e=29,p=0,cr=0,cu=0,tim=1275528151756757
WAIT #0: nam='SQL*Net message from client' ela= 59 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151756840
LOBREAD: c=0,e=3,p=0,cr=0,cu=0,tim=1275528151756870
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151756890
WAIT #0: nam='SQL*Net message from client' ela= 31 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151756938
LOBREAD: c=0,e=2,p=0,cr=0,cu=0,tim=1275528151756966
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528151756987

*** 2010-06-02 18:22:34.315
WAIT #0: nam='SQL*Net message from client' ela= 2558422 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1275528154315425
LOBARRTMPFRE: c=0,e=23,p=0,cr=0,cu=0,tim=1275528154315542
XCTEND rlbk=0, rd_only=1, tim=1275528154315594
CLOSE #3:c=0,e=20,dep=0,type=0,tim=1275528154315664
CLOSE #1:c=0,e=9,dep=0,type=0,tim=127552815431569

Custom Treedump (part 2)

05/18/2010: Due to the bug#9036013 - result of DECODE() may be incorrectly interpreted as NULL; the script may not work correctly on un-patched 11.2.0.1.0 environments. Consider applying the fix anyway cause it may break some business logic of your application. To fix the script you can change these lines:

    || TO_CHAR(dbms_rowid.rowid_relative_fno(NVL2('^^td_____y', rid, NULL)), 'FM0009')
    || '.'
    || TO_CHAR(dbms_rowid.rowid_block_number(NVL2('^^td_____y', rid, NULL)), 'FM00000009')
    || '.'
    || TO_CHAR(dbms_rowid.rowid_row_number(NVL2('^^td_____y', rid, NULL)), 'FM00009')

05/17/2010: There was an issue with the syntax highlighter, some of XML tags were removed when the highlighter renders the page. It's addressed now. Thanks to Timur Akhmadeev for reporting the issue.

Almost five years ago I published a post about custom treedump script. Since the original script had to be polished and I did not have time (or desire) to return back to that subject I kinda put it all aside. But recently I was asked to get it published. I decided to clean things up, get rid of PL/SQL logic and implement it all as a standalone SQL script. I did not spend to much time of testing it though; hopefully it does not have significant errors. If anything strange or abnormal pops up I guess I would have to fix, extend the functionality or error handling. Some primitive things are done:

SQL> SHOW USER 
USER is "SYS"
SQL> ALTER SESSION SET CURRENT_SCHEMA=SCOTT;

Session altered.

SQL> @td
Usage: td index_name [owner_name]

SQL> @td blah
td: index "SCOTT"."BLAH" does not exist

SQL> @td i_user2 sys


                 Treedump of I_USER2 [47] (NORMAL) on
                              SYS."USER$"

  index                                        table
file block              dba         -> file block    row  (keys/block)       USER#      TYPE#     SPARE1     SPARE2
----------------------------------------------------------------------- ---------- ---------- ---------- ----------
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00001 (00001/0078)           0          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00002 (00002/0078)           1          0          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00003 (00003/0078)           2          0          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00004 (00004/0078)           3          0          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00005 (00005/0078)           4          0          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00006 (00006/0078)           5          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00007 (00007/0078)           6          0          0
...
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00011 (00072/0078)          71          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00012 (00073/0078)          72          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00013 (00074/0078)          76          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00014 (00075/0078)          77          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00015 (00076/0078)          78          1          0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00000 (00077/0078)          79          0          0
0001.00000116 (0x00400074/4194420) -> 0001.00008152.00000 (00078/0078)  2147483638          1          0

But if you have a case depicting a misbehavior of the script let me know.

.
REM
REM The script simulates treedump event behaviour rendering
REM the leaf block -> table structure.
REM
REM It is provided for illustrative purpose. The script is not
REM meant to be used to dump structures of huge indexes or be
REM part of any production infrastructure.
REM
REM It is provided "AS IS"; NO WARRANTY is implied.
REM
REM Usage: td index_name [owner_name]
REM
REM Author: Vladimir Begun (http://vbegun.blogspot.com/)
REM Version 0.2
REM

SET TERMOUT OFF
REM Saving the original SQL*Plus environment settings
STORE SET .td REPLACE

REM Settings
SET DEFINE "^" PAGES 0 TRIMSPOOL ON LINESIZE 32767 TAB OFF
SET HEADING OFF FEEDBACK OFF ARRAYSIZE 1 LONG 1024000 ECHO OFF
SET VERIFY OFF SERVEROUTPUT ON SIZE 100000 FORMAT TRUNCATED TERMOUT ON

REM Columns and substitution variables
COLUMN td_____i NEW_VALUE td_____i NOPRINT
COLUMN td_____o FORMAT A30 NEW_VALUE td_____o NOPRINT
COLUMN td_____t FORMAT A30 NEW_VALUE td_____t NOPRINT
COLUMN td_____x FORMAT A30 NEW_VALUE td_____x NOPRINT
COLUMN td_____f FORMAT A1 NEW_VALUE td_____f NOPRINT
COLUMN td_____c FORMAT A1000 NEW_VALUE td_____c NOPRINT
COLUMN td_____v FORMAT A1000 NEW_VALUE td_____v NOPRINT
COLUMN td_____fn FORMAT A10 NEW_VALUE td_____fn NOPRINT
COLUMN td_____y FORMAT A30 NEW_VALUE td_____y NOPRINT
COLUMN td_____y1 FORMAT A30 NEW_VALUE td_____y1 NOPRINT
COLUMN td_____banner FORMAT A60 HEADING OFF
COLUMN td_____irid NOPRINT
BREAK ON td_____irid NODUP SKIP 1

DEFINE td_____c = NULL
DEFINE td_____v = NULL
DEFINE td_____i = NULL
DEFINE td_____fn = SYS_OP_LBID

REM Command line handling
SET TERMOUT OFF
COLUMN 1 NEW_VALUE 1
COLUMN 2 NEW_VALUE 2
SELECT '' AS "1", '' AS "2"
  FROM dual
 WHERE ROWNUM = 0
/
SELECT UPPER('^^1') td_____x
     , UPPER(NVL('^^2', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))) td_____o
  FROM dual
/
SET TERMOUT ON
SELECT 'Usage: td index_name [owner_name]' td_____banner
  FROM dual
 WHERE '^^1' IS NULL
/
REM Gathering of index/table information from the data dictionary.
REM It limits the number of indexes which are subject of dumping.
SELECT TO_CHAR(o.object_id, 'FM9999999') td_____i
     , dbms_assert.schema_name(i.table_owner) td_____o
     , dbms_assert.enquote_name(i.table_name) td_____t
     , RTRIM(
         LTRIM(
           REPLACE(
             dbms_xmlgen.convert(
               XMLTYPE.GetStringVal(
                 XMLAGG(
                   XMLELEMENT(
                     "c"
                   , dbms_assert.enquote_name(column_name)
                  || ' '
                  || descend
                   )
                 ORDER BY column_position
                 )
               )
             , 1
             )
           , '</c><c>'
           , ','
           )
         , '</c>'
         )
       , '</c>'
       ) td_____c
     , RTRIM(
         LTRIM(
           REPLACE(
             dbms_xmlgen.convert(
               XMLTYPE.GetStringVal(
                 XMLAGG(
                   XMLELEMENT(
                     "c"
                   , dbms_assert.enquote_name(column_name)
                   )
                 ORDER BY column_position
                 )
               )
             , 1
             )
           , '</c><c>'
           , ','
           )
         , '</c>'
         )
       , '</c>'
       ) td_____v
     , CASE WHEN index_type LIKE '%NORMAL%' AND t.iot_type IS NULL THEN index_type END td_____y
     , index_type td_____y1
  FROM all_objects o
     , all_indexes i
     , all_ind_columns aic
     , all_tables t
 WHERE o.object_name = UPPER('^^td_____x')
   AND o.owner = UPPER('^^td_____o')
   AND o.object_name = i.index_name
   AND o.owner = i.owner
   AND o.object_type = 'INDEX'
   AND aic.index_owner = i.owner
   AND aic.index_name = i.index_name
   AND aic.table_owner = i.table_owner
   AND aic.table_name = i.table_name
   AND t.owner = i.table_owner
   AND t.table_name = i.table_name
 GROUP BY
       o.object_id
     , i.table_owner
     , i.table_name
     , i.index_type
     , t.iot_type
/
REM Handling of non-existing indexes
SELECT 'td: index "^^td_____o"."^^td_____x" does not exist' td_____banner
  FROM dual
 WHERE '^^td_____i' = 'NULL' AND '^^1' IS NOT NULL
/
SELECT 'DECODE' td_____fn, 'SYS' td_____o, 'DUAL' td_____t
  FROM dual
 WHERE '^^td_____i' = 'NULL'
/
SET PAGES 10000 HEADING ON
COLUMN td_____treedump FORMAT A71 -
HEADING 'Treedump of ^^td_____x [^^td_____i] (^^td_____y1) on|^^td_____o..^^td_____t||  index                                        table                  |file block              dba        -> file block     row  (keys/block)' -
JUSTIFY CENTER

REM Actual dumping
WITH i AS (
  SELECT /*+
           MATERIALIZE
           CURSOR_SHARING_EXACT
           NO_MONITORING
           DYNAMIC_SAMPLING(0)
           INDEX_FFS(t ^^td_____x)
           NOPARALLEL_INDEX(t ^^td_____x)
         */
         ROWID rid
       , ^^td_____fn(^^td_____i, 'L', ROWID) irid
       , dbms_rowid.rowid_relative_fno(^^td_____fn(^^td_____i, 'L', ROWID)) irid_f
       , dbms_rowid.rowid_block_number(^^td_____fn(^^td_____i, 'L', ROWID)) irid_b
    FROM ^^td_____o..^^td_____t t
   WHERE '^^td_____i' <> 'NULL'
)
SELECT i.irid td_____irid
     , TO_CHAR(irid_f, 'FM0009')
    || '.'
    || TO_CHAR(irid_b, 'FM00000009')
    || ' ('
    ||  '0x'
    || TO_CHAR(dbms_utility.make_data_block_address(irid_f, irid_b), 'FM0000000x')
    || '/'
    ||  dbms_utility.make_data_block_address(irid_f, irid_b)
    || ')'
    || ' -> ' 
    || TO_CHAR(dbms_rowid.rowid_relative_fno(DECODE('^^td_____y', NULL, NULL, rid)), 'FM0009')
    || '.'
    || TO_CHAR(dbms_rowid.rowid_block_number(DECODE('^^td_____y', NULL, NULL, rid)), 'FM00000009')
    || '.'
    || TO_CHAR(dbms_rowid.rowid_row_number(DECODE('^^td_____y', NULL, NULL, rid)), 'FM00009')
    || ' ('
    || TO_CHAR(ROW_NUMBER() OVER (PARTITION BY i.irid ORDER BY ^^td_____c, rid), 'FM00009')
    || '/'
    || TO_CHAR(COUNT(*) OVER (PARTITION BY i.irid), 'FM0009')
    || ')' td_____treedump
     , ^^td_____v
  FROM i
     , ^^td_____o..^^td_____t t
 WHERE i.rid = t.ROWID
 ORDER BY
       ^^td_____c
     , rid
     , i.irid
/
REM Cleanup and restoration of the original SQL*Plus environment
REPHEADER OFF
@.td
UNDEFINE 1 2 td_____i td_____o td_____t td_____x td_____f td_____c td_____v td_____fn td_____y td_____y1
COLUMN 1 CLEAR
COLUMN 2 CLEAR
COLUMN td_____i CLEAR
COLUMN td_____o CLEAR
COLUMN td_____t CLEAR
COLUMN td_____x CLEAR
COLUMN td_____f CLEAR
COLUMN td_____c CLEAR
COLUMN td_____v CLEAR
COLUMN td_____y CLEAR
COLUMN td_____y1 CLEAR
COLUMN td_____banner CLEAR
COLUMN td_____fn CLEAR
COLUMN td_____treedump CLEAR
COLUMN td_____irid CLEAR
SET TERMOUT ON

updatable view: where the data lives (part 2)

Here is the solution I came up with to solve this puzzle. Hope curious minds would be happy. The script uses dbms metadata related API as well as XMLType/XPath processing combined with the queries against the data dictionary. It's tested on 11.1.0.7/11.2.0.2. It may not work properly on 11.1.0.7 having DB NLS character set set to AL32UTF8. Other multi-byte character sets may have similar issue. Not everything you find in the script is documented however it was too tempting to solve the task using "plain" (or somewhat plain) SQL. :-) Have fun.

REM
REM This script is provided as a possible solution for the puzzle.
REM
REM It is not recommended for production usage.
REM

SET DEFINE "^" VERIFY OFF TAB OFF PAGES 1000 LINES 1000 TRIMSPOOL ON ECHO OFF
DEFINE owner = '^^1'
DEFINE view_name = '^^2'

COLUMN alias FORMAT A30
COLUMN owner FORMAT A15
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN resolved_from FORMAT A20

SELECT alias
     , CASE
         WHEN object_type = 'SYNONYM'
         THEN (
              SELECT table_owner
                FROM dba_synonyms
               WHERE owner = x.owner
                 AND synonym_name = x.table_name
              )
         ELSE owner
       END owner
     , CASE
         WHEN object_type = 'SYNONYM'
         THEN (
              SELECT table_name
                FROM dba_synonyms
               WHERE owner = x.owner
                 AND synonym_name = x.table_name
              )
         ELSE table_name
       END table_name
     , column_name
     , insertable
     , updatable
     , deletable
     , object_type
     , CASE
         WHEN object_type = 'SYNONYM'
         THEN '"' || owner || '"."' || table_name || '"'
       END resolved_from
  FROM (
       SELECT x.*
            , (
              SELECT object_type
                FROM dba_objects
               WHERE owner = x.owner
                 AND object_name = x.table_name
              ) object_type
         FROM (
              SELECT c.column_name alias
                   , COALESCE(
                       x.owner
                     , (
                       SELECT '^^owner'
                         FROM dba_objects
                        WHERE owner = '^^owner'
                          AND object_name = x.table_name
                          AND object_type <> 'TRIGGER'
                       )
                     , (
                       SELECT table_owner
                         FROM dba_synonyms
                        WHERE owner = 'PUBLIC'
                          AND synonym_name = x.table_name
                       )
                     , NVL2(x.table_name, '^^owner', '')
                     ) owner
                   , x.table_name
                   , x.column_name
                   , c.insertable
                   , c.updatable
                   , c.deletable
                FROM (
                     SELECT (
                            SELECT column_name
                              FROM dba_tab_cols
                             WHERE table_name = '^^view_name'
                               AND owner = '^^owner'
                               AND column_id = rn
                            ) column_alias
                          , owner
                          , table_name
                          , column_name
                       FROM (
                            SELECT ROWNUM rn
                                 , EXTRACTVALUE(
                                     column_value
                                   , '/SELECT_LIST_ITEM/COLUMN_REF/SCHEMA'
                                   ) owner
                                 , EXTRACTVALUE(
                                     column_value
                                   , '/SELECT_LIST_ITEM/COLUMN_REF/TABLE'
                                   ) table_name
                                 , EXTRACTVALUE(
                                     column_value
                                   , '/SELECT_LIST_ITEM/COLUMN_REF/COLUMN'
                                   ) column_name
                              FROM TABLE(
                                     XMLSequence(
                                       EXTRACT(
                                         sys.dbms_metadata_util.parse_query(
                                           '^^owner'
                                         , dbms_xmlgen.convert(
                                             XMLType.GetClobVal(
                                               EXTRACT(
                                                 XMLtype(
                                                   dbms_metadata.get_xml(
                                                     'VIEW'
                                                   , '^^view_name'
                                                   , '^^owner'
                                                   )
                                                 )
                                               , '/ROWSET/ROW/VIEW_T/TEXT/text()'
                                               )
                                             )
                                            , 1
                                            )
                                         )
                                       , '//QUERY/SELECT/SELECT_LIST/SELECT_LIST_ITEM'
                                       )
                                     )
                                   )
                            )
                     ) x
                   , dba_updatable_columns c
               WHERE c.table_name = '^^view_name'
                 AND c.owner = '^^owner'
                 AND c.column_name = x.column_alias
              ) x
      ) x
/

The solution of the puzzle may not cover all possible cases; but with extra polishing and tailoring it can be extended. For instance the hierarchy traversal still has to be implemented. I also have to warn on the usage of undocumented features: don't use them in production.

updatable view: where the data lives

There was a task given: let's suppose there is an updatable view (see docs: Notes on Updatable Views and Creating an Updatable View: Example); a solution has to be provided to figure out the real table/column names of the view's updatable column(s). An example below should illustrate the point:

SQL> CREATE TABLE t1 (
  2    n                                NUMBER PRIMARY KEY
  3  , v                                VARCHAR2(30)
  4  , d                                DATE
  5  )
  6  /             

Table created.                    

SQL> CREATE TABLE t2 (
  2    n                                NUMBER PRIMARY KEY
  3  , v                                VARCHAR2(30)
  4  , d                                DATE
  5  )
  6  /             

Table created.                    

SQL> CREATE OR REPLACE VIEW v1
  2  AS
  3    SELECT *
  4      FROM t1
  5    WHERE n = 1 OR n = 2
  6  /

View created.

SQL> CREATE OR REPLACE VIEW v2
  2  AS
  3    SELECT *
  4      FROM t2
  5    WHERE n <= 10
  6  /

View created.

SQL> CREATE OR REPLACE VIEW v
  2  AS
  3    SELECT t1.n alias_n
  4         , ABS(t1.n) a
  5         , t2.v alias_v
  6         , LENGTH(t2.v) l
  7         , t1.d alias_d
  8         , TO_CHAR(t1.d, 'YYYY/MM/DD HH24:MI:SS') tc
  9         , (t2.n) alias_p
 10         , t2.n + 1 alias_n_plus
 11      FROM v1 t1, v2 t2
 12    WHERE t1.n = t2.n
 13  /

View created.

SQL> SELECT column_name
  2       , updatable
  3       , insertable
  4       , deletable
  5    FROM user_updatable_columns
  6   WHERE table_name = 'V'
  7  /

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
ALIAS_N                        YES YES YES
A                              NO  NO  NO
ALIAS_V                        YES YES YES
L                              NO  NO  NO
ALIAS_D                        YES YES YES
TC                             NO  NO  NO
ALIAS_P                        YES YES YES
ALIAS_N_PLUS                   NO  NO  NO

8 rows selected.

As you can see ALIAS_N -> V1.N -> T1.N, ALIAS_V -> V2.V -> T2.V. As you can guess the goal here to figure out the very base data columns like T1.N and T2.V and so on.

I'll update this post with a potential solution some time later. It's a relatively complex solution which requires a bit of understanding how some Oracle components work. It may not be a complete one but with some extra polishing and with the acceptance of certain limits (e.g. limits of Oracle RDBMS security) the ultimate goal can be achieved or somewhat achieved :-)

Update 04/30/2010:

REM
REM Updatable view: where the data lives.
REM
REM Views seeding example.
REM
REM This script creates a relatively complex set of views, which have
REM direct dependencies on tables (somewhat obvious) and other views.
REM Objects are residing in the local to the owner and other schemas,
REM referenced either explicitely via schema.object_name notation, or
REM via synonym, or public synonym.
REM
REM Please make sure that users are removed from the system after
REM playing with this script.
REM

DROP USER u1 CASCADE;
DROP USER u2 CASCADE;
DROP USER u3 CASCADE;
DROP USER u4 CASCADE;
DROP PUBLIC SYNONYM u1_v1;

GRANT RESOURCE TO u1 IDENTIFIED BY u1;
GRANT RESOURCE TO u2 IDENTIFIED BY u2;
GRANT RESOURCE TO u3 IDENTIFIED BY u3;
GRANT RESOURCE, SELECT ANY TABLE TO u4 IDENTIFIED BY u4;

CREATE TABLE u1.t1 (
  n                                NUMBER PRIMARY KEY
, v                                VARCHAR2(30)
);

CREATE OR REPLACE VIEW u1.v1
AS
  SELECT n v1_n, v v1_v
    FROM u1.t1;

CREATE OR REPLACE PUBLIC SYNONYM u1_v1 FOR u1.v1;
GRANT ALL ON u1.v1 TO PUBLIC;

CREATE TABLE u2.t2 (
  n                                NUMBER PRIMARY KEY
, v                                VARCHAR2(30)
);

CREATE OR REPLACE VIEW u2.v2
AS
SELECT t1.v1_n v
     , t1.v1_v n
     , u2_t2.n t2_n
     , u2_t2.v t2_v
  FROM u1_v1 t1
     , u2.t2 u2_t2
 WHERE t1.v1_n = u2_t2.n;
GRANT ALL ON u2.v2 TO u3;

CREATE OR REPLACE SYNONYM u3.u2_v2 FOR u2.v2;

CREATE TABLE u3.t3 (
  n                                NUMBER PRIMARY KEY
, v                                VARCHAR2(30)
);

CREATE OR REPLACE VIEW u3.v3
AS
SELECT t3.n u3_v3_n
     , t3.v u3_v3_v
     , u2_v2.n u2_v2_n
     , u2_v2.v u2_v2_v
     , u2_v2.t2_n u1_v1_n
     , u2_v2.t2_v u1_v1_v
  FROM u3.t3
     , u2_v2
 WHERE u2_v2.n = t3.n;

CREATE OR REPLACE VIEW u4.v4
AS
  SELECT u4_v4.u1_v1_n c1
       , u4_v4.u1_v1_v c2
       , u4_v4.u2_v2_n c3
       , u4_v4.u2_v2_v c4
       , u4_v4.u3_v3_n c5
       , u4_v4.u3_v3_v c6
    FROM u3.v3 u4_v4
/

If such dependency is traversed by this would be observed:

SQL> @check U4 V4

ALIAS           OWNER           TABLE_NAME      COLUMN_NAME     INS UPD DEL OBJECT_TYPE     RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
C1              U3              V3              U1_V1_N         YES YES YES VIEW
C2              U3              V3              U1_V1_V         YES YES YES VIEW
C3              U3              V3              U2_V2_N         YES YES YES VIEW
C4              U3              V3              U2_V2_V         YES YES YES VIEW
C5              U3              V3              U3_V3_N         NO  NO  NO  VIEW
C6              U3              V3              U3_V3_V         NO  NO  NO  VIEW

6 rows selected.

SQL> @check U3 V3

ALIAS           OWNER           TABLE_NAME      COLUMN_NAME     INS UPD DEL OBJECT_TYPE     RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
U3_V3_N         U3              T3              N               NO  NO  NO  TABLE
U3_V3_V         U3              T3              V               NO  NO  NO  TABLE
U2_V2_N         U2              V2              N               YES YES YES SYNONYM         "U3"."U2_V2"
U2_V2_V         U2              V2              V               YES YES YES SYNONYM         "U3"."U2_V2"
U1_V1_N         U2              V2              T2_N            YES YES YES SYNONYM         "U3"."U2_V2"
U1_V1_V         U2              V2              T2_V            YES YES YES SYNONYM         "U3"."U2_V2"

6 rows selected.

SQL> @check U2 V2

ALIAS           OWNER           TABLE_NAME      COLUMN_NAME     INS UPD DEL OBJECT_TYPE     RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
V               U1              V1              V1_N            YES YES YES SYNONYM         "PUBLIC"."U1_V1"
N               U1              V1              V1_V            YES YES YES SYNONYM         "PUBLIC"."U1_V1"
T2_N            U2              T2              N               YES YES YES TABLE
T2_V            U2              T2              V               YES YES YES TABLE

SQL> @check U1 V1

ALIAS           OWNER           TABLE_NAME      COLUMN_NAME     INS UPD DEL OBJECT_TYPE     RESOLVED_FROM
--------------- --------------- --------------- --------------- --- --- --- --------------- --------------------
V1_N            U1              T1              N               YES YES YES TABLE
V1_V            U1              T1              V               YES YES YES TABLE

A solution is published here.

read-only access

It's a well known fact that in Oracle RDBMS a granted SELECT privilege on a table would also give a grantee a possibility of doing SELECT FOR UPDATE against the same table. So, if a DBA grants the SELECT privilege directly some surprises are anticipated. There was a question posted to an internal mailing list about this problem. Here is what I proposed as a possible solution:

SQL> SELECT * FROM v$version WHERE ROWNUM = 1;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

CONNECT / AS SYSDBA
-- DROP USER viewer CASCADE;
-- DROP USER owner CASCADE;
GRANT CREATE SESSION TO viewer IDENTIFIED BY viewer;
GRANT CREATE SESSION, CREATE VIEW, CREATE TABLE TO owner IDENTIFIED BY owner;
ALTER USER owner QUOTA 1M ON users;
ALTER SESSION SET CURRENT_SCHEMA = owner;
CREATE TABLE t (p NUMBER);
INSERT INTO t VALUES (1);
CREATE OR REPLACE VIEW t_v AS SELECT t.* FROM t WHERE ROWNUM > 0 WITH READ ONLY;
GRANT SELECT ON t_v TO viewer;
CREATE OR REPLACE SYNONYM viewer.t FOR owner.t_v;
CONNECT viewer/viewer
INSERT INTO t(p) VALUES(5);
DELETE t;
UPDATE t SET p = 5;
SELECT * FROM t FOR UPDATE;
SELECT p FROM t FOR UPDATE;
SELECT p FROM t FOR UPDATE OF p;
SELECT * FROM t;

According to the last reply of the original poster so far it's considered being the best possible workaround.

People who replied to the same thread also mentioned this: AskTom; MySQL/Oracle vs. PostgreSQL. Since it's a public information I'm taking a liberty of posting the links for the sake of referencing/comparison.

On Numbers

I somehow lost my posting privileges to oracle-l [probably due to the read-only mode I'm in]...

You are not currently authorized to post messages to oracle-l.

New subscribers to this list are not able to post messages at first. To get this privilege turned on, you must email the list administrator at oracle-l-admins@freelists.org. If you are using an anonymous email address, you must also identify yourself. Please be sure to send the request from the address for which you require the privilege, not from some other address.

However to avoid lost of information I'd put it in here. This post is in reply to the question about oracle numbers, namely:

anyone every see this or know how it could happen:

SQL> select * from toto;

       NUM
----------
         0
        -.
        -.
        -.
        -.
        -.
         0

7 rows selected.

SQL> desc toto;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 NUM                                                NUMBER(18,5)

Here is my reply:

Oracle engine can construct numbers, valid or "invalid" ones. It can be due to the bugs (OCI, jdbc, PL/SQL) or because one was playing foolish games like I am doing below. Both positive and negative zero, as well as positive and negative infinity do exist for years, with the introduction of "new numbers" (BINARY_FLOAT) all that magic became documented (in some sense), however for TRUE numeric (NUMBER) columns the anomalies your faced below are primarily due to the bugs in client software or due to misuse of PL/SQL. I have to warn the readers that the example below must not be used on any production environment, moreover one must not try to insert those "numbers" into any table that is considered part of any production environment -- the results are unpredictable and can crash clients and damage users' experiences. Be careful. Vladimir

REM Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

CREATE TABLE t (i NUMBER, n NUMBER, h RAW(10));
BEGIN
  FOR i IN 0..255
  LOOP
    BEGIN
      INSERT INTO t VALUES(
        i
      , utl_raw.cast_to_number(HEXTORAW(TO_CHAR(i, 'FM0X')))
      , HEXTORAW(TO_CHAR(i, 'FM0X'))
      );
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
    BEGIN
      INSERT INTO t VALUES(
        -i
      , utl_raw.cast_to_number(HEXTORAW(TO_CHAR(i, 'FM0X') || '66'))
      , HEXTORAW(TO_CHAR(i, 'FM0X') || '66')
      );
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;
/
SET TRIMSPOOL ON TAB OFF PAGES 1000 LINES 10000
COLUMN dmp FORMAT A30
COLUMN h FORMAT A20
SPOOL /tmp/kyle.lst
SELECT ABS(i) i, n, h, DUMP(n, 16) dmp FROM t ORDER BY ABS(i), i;
SPOOL OFF

читаем данные с дисков очень быстро...

SQL> SELECT /*+ PARALLEL(t) */ COUNT(*) FROM t;

   COUNT(*)
-----------
 1937420560

Elapsed: 00:00:24.17

---------------------------------------
| Id  | Operation                     
---------------------------------------
|   0 | SELECT STATEMENT              
|   1 |  SORT AGGREGATE               
|   2 |   PX COORDINATOR              
|   3 |    PX SEND QC (RANDOM)        
|   4 |     SORT AGGREGATE            
|   5 |      PX BLOCK ITERATOR        
|   6 |       TABLE ACCESS STORAGE FULL
---------------------------------------


Statistics
----------------------------------------------------------
      48  recursive calls
       0  db block gets
23233525  consistent gets
23105978  physical reads
       0  redo size
     526  bytes sent via SQL*Net to client
     524  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
      16  sorts (memory)
       0  sorts (disk)
       1  rows processed

DP = 16 для 16 секций таблицы

On @! thingy

Long time back there was a discussion about @! thingy (USER@! or SYSDATE@!). Below is an example of how that can be used.

Here is a task: there is a PL/SQL unit being called over a database link. The unit has to resolve the caller i.e. a remote user.

I'm using 11.2.0.0.0 and 10.2.0.3.0 versions of Oracle RDBMS. The 11g database is a local database to a user caller. Let's create a user and create a database link to the remote database.

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

SQL> GRANT CREATE SESSION,CREATE DATABASE LINK TO caller IDENTIFIED BY caller;

Grant succeeded.

SQL> CONNECT caller/caller
Connected.
SQL> CREATE DATABASE LINK l CONNECT TO callee IDENTIFIED BY callee USING 'localhost:1521/orcl';

Database link created.

Let's take care of the remote part:

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

SQL> GRANT CREATE SESSION,CREATE PROCEDURE TO callee IDENTIFIED BY callee;

Grant succeeded.

SQL> CONNECT callee/callee
Connected.
SQL> CREATE OR REPLACE FUNCTION resolve_users
  2  RETURN VARCHAR2
  3  AS
  4    l_remote_user                    user_users.username%TYPE;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'SELECT USER@! FROM dual' INTO l_remote_user;
  7    RETURN l_remote_user || '->' || USER;
  8  END;
  9  /

Function created.

Now we are going to call a PL/SQL unit resolve_users() over a database link being connected as user caller.

SQL> VAR v VARCHAR2(40)
SQL> EXEC :v := resolve_users@l;

PL/SQL procedure successfully completed.

SQL> PRINT v

V
---------------------------------------
CALLER->CALLEE

The local call as of callee gives this as a result:

SQL> VAR v VARCHAR2(40)
SQL> EXEC :v := resolve_users;

PL/SQL procedure successfully completed.

SQL> PRINT v

V
---------------------------------------
CALLEE->CALLEE

Which seems to be pretty obvious.

And of course if you don't know the details about the database link (user_db_links) and want to get more information about the local and remote user, you can try to use this statement to get it:

SQL> SELECT user local_user
  2       , username remote_user
  3    FROM v$session@l
  4   WHERE sid = (
  5           SELECT sid
  6             FROM v$mystat@l
  7            WHERE ROWNUM = 1
  8         )
  9  /

LOCAL_USER                     REMOTE_USER
------------------------------ ------------------------------
CALLER                         CALLEE

It presumes that the grants on v_$session and v_$mystat are given to callee.

As you probably noticed I did not use CREATE USER command. I'm too lazy for that :-). Instead I used a very ancient way of creating a user — I directly granted the privileges to him.

05/11/2011:
There was a question asked about the detection of the callee's database sid. I'm not sure when exactly it would be required, however, one can attempt using dbms_system.get_env() API call to figure that out. It has to be available on almost all modern versions of the ORACLE RDBMS. One has to be aware that dbms_system has a set of undocumented API calls, therefore, direct grant on execution of that package may pose security issues. Therefore one either has to re-think and re-state the task or create a wrapper to ensure only particular API calls of dbms_system are exposed. The example below is just to illustrate the point and should not be used in any production environments w/o careful considerations.

CREATE OR REPLACE FUNCTION get_sid
RETURN VARCHAR2
IS
 l_sid                            VARCHAR2(30);
BEGIN
  sys.dbms_system.get_env('ORACLE_SID', l_sid);
  RETURN l_sid;
END;

One can use SYS_CONTEXT('USERENV', 'SERVICE_NAME') to detect the service name.

On SQL*Plus Defines

Some time back Pavel Luzanov and me had an idea of writing a series of articles on SQL*Plus. We spent some time and came up with two (1, 2 those are in Russian, so use http://translate.google.com/ to get them translated into your language) of them, but then it all somehow stopped. Lack of time and desire I believe...

Anyway, there are questions popping up here and there on some basic SQL*Plus functionality and this post below is an answer to one of them.

This brief example illustrates how one can avoid SQL*Plus asking a user to enter a value of a define variable if its value was not provided by the user. I.e. it helps one to implement the NVL-like behavior and set the value of a define variable to the default unless the user entered a specific value.

So, here it goes...

SQL> COLUMN 1 NEW_VALUE 1
SQL> COLUMN 2 NEW_VALUE 2
SQL> COLUMN 3 NEW_VALUE 3
SQL> COLUMN 4 NEW_VALUE 4
SQL> SELECT '' "1", '' "2", '' "3", '' "4"
  2    FROM dual
  3   WHERE ROWNUM = 0
  4  /

no rows selected

SQL> DEFINE 1
DEFINE 1               = "1" (CHAR)
SQL> DEFINE 2
DEFINE 2               = "2" (CHAR)
SQL> DEFINE 3
DEFINE 3               = "" (CHAR)
SQL> DEFINE 4
DEFINE 4               = "" (CHAR)
SQL> 
SQL> REM ...but...
SQL> 
SQL> DEFINE 5
SP2-0135: symbol 5 is UNDEFINED

Copy it into a sql file and execute like @sql_file.sql first second and you'll see that value of &1 is first, value of &2 is second, but both &3 and &4 are set to NULL (default).

On REVERSing comma-separated set of words

Here is a task [beware it's in Russian :-)]. There is a given string: '5,Z,0,q,1,b,A,Z' we have to get 'Z,A,b,1,q,0,Z,5' as a result of some SQL statement, in other words we have to put the coma separated set of words in the reversed order. The author of the question proposed to use REGEXP to solve it.

There are two options. One is simple:

SQL> COLUMN o FORMAT A30
SQL> COLUMN r FORMAT A30
SQL> VAR s VARCHAR2(30)
SQL> EXEC :s := '5,Z,0,q,1,b,A,Z';

PL/SQL procedure successfully completed.

SQL> 
SQL>  SELECT :s o
  2        , SUBSTR(SYS_CONNECT_BY_PATH(w, ','), 2) r
  3     FROM (
  4           SELECT REGEXP_INSTR(:s, '([^,])+', 1, LEVEL) p
  5                , LEVEL l
  6                , REGEXP_SUBSTR(:s, '([^,])+', 1, LEVEL) w
  7             FROM dual
  8          CONNECT BY REGEXP_SUBSTR(:s, '([^,])+', 1, LEVEL) IS NOT NULL
  9          )
 10    WHERE CONNECT_BY_ISLEAF = 1
 11    START WITH REGEXP_INSTR(:s, ',([^,])+$', 1) + 1 = p
 12  CONNECT BY PRIOR l = l + 1
 13  /

O                              R
------------------------------ ------------------------------
5,Z,0,q,1,b,A,Z                Z,A,b,1,q,0,Z,5


Of course instead of using REGEXP one can try to solve the same task using SUBSTR/INSTR functions.

The second is is a bit trickier. It uses an undocumented but a well known REVERSE function. The REVERSE function is used for reverse indexes, basically it reverses the byte order of a passed value. However, it won't work correctly for the multibyte character sets. So, to solve that limitation we have to ensure that the character values passed to the reverse function aren't multibyte ones. For that we use ASCIISTR and UNISTR functions to get the strings converted back and forth.

SQL> EXEC :s := 'é,ô,ÿ';

PL/SQL procedure successfully completed.

SQL> COLUMN dump_ok FORMAT A40
SQL> COLUMN dump_bad FORMAT A40
SQL> COLUMN ok FORMAT A10
SQL> COLUMN bad FORMAT A10
SQL> COLUMN o FORMAT A10
SQL> SELECT :s o
  2       , ok
  3       , DUMP(ok, 16) dump_ok
  4       , bad
  5       , DUMP(bad, 16) dump_bad
  6    FROM (
  7         SELECT TRANSLATE(
  8                  RTRIM(
  9                    UNISTR(
 10                      REVERSE(
 11                        SYS_CONNECT_BY_PATH(
 12                          REVERSE(
 13                            ASCIISTR(
 14                              REGEXP_SUBSTR(:s, '([^,])+', 1, LEVEL)
 15                            )
 16                          )
 17                        , ','
 18                        )
 19                      )
 20                    )
 21                  , ','
 22                  )
 23                  USING CHAR_CS
 24                ) ok
 25              , RTRIM(
 26                  REVERSE(
 27                    SYS_CONNECT_BY_PATH(
 28                      REVERSE(
 29                         REGEXP_SUBSTR(:s, '([^,])+', 1, LEVEL)
 30                      )
 31                    , ','
 32                    )
 33                  )
 34                , ','
 35                ) bad
 36           FROM dual
 37          WHERE CONNECT_BY_ISLEAF = 1
 38        CONNECT BY REGEXP_SUBSTR(:s, '([^,])+', 1, LEVEL) IS NOT NULL
 39         )
 40  /

O          OK         DUMP_OK                                  BAD        DUMP_BAD
---------- ---------- ---------------------------------------- ---------- --------------------------
é,ô,ÿ      ÿ,ô,é      Typ=1 Len=8: c3,bf,2c,c3,b4,2c,c3,a9     ¿,¿,¿      Typ=1 Len=5: bf,2c,b4,2c,a9

SQL> SELECT DUMP(:s, 16) FROM dual;

DUMP(:S,16)
------------------------------------
Typ=1 Len=8: c3,a9,2c,c3,b4,2c,c3,bf


So, as you can see without appropriate conversion, we get bad "characters" in the string.

Additionally one has to make sure that the difference between the character set and national character set is understood. That can be done using TRANSLATE ... USING function.

Below is an output on nls settings and version used:

SQL> SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               AL32UTF8

SQL> SELECT * FROM v$version WHERE ROWNUM = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

Triggers...

It's an old story... first time I saw this behavior roughly 10 years ago, in 1997. Last century, last millennium. It was my first project abroad, in Denmark. I love Denmark. The project was about migrating some popular publishing software from Sybase to Oracle. That was old 8.0.3 release of Oracle.

Once I mentioned about this fact here [the thread is in Russian].

Below is an example of how Oracle trigger's behavior one just has to know about. Hope you find it useful. I'm using 10.2.0.3, it works same way on 11.1.0.7.0 too.

SQL> DEFINE rows = 100000

SQL> CREATE TABLE x (
  2    p                                NUMBER
  3  )
  4  /

SQL> CREATE OR REPLACE TRIGGER x$trg$bd
  2    BEFORE DELETE ON x
  3  BEGIN
  4    dbms_session.set_identifier('0');
  5  END;
  6  /

SQL> CREATE OR REPLACE TRIGGER x$trg
  2    BEFORE DELETE ON x
  3    FOR EACH ROW
  4  BEGIN
  5    dbms_session.set_identifier(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') + 1);
  6  END;
  7  /

SQL> TRUNCATE TABLE x;

SQL> INSERT INTO x(p) SELECT ROWNUM FROM dual CONNECT BY LEVEL <= &&rows;
100000 rows created.

SQL> COMMIT;

SQL> VAR tx VARCHAR2(20)
SQL> EXEC :tx := dbms_transaction.local_transaction_id(TRUE);

SQL> COLUMN extends NEW_VALUE extends NOPRINT
SQL> SELECT r.extends
  2    FROM v$rollstat r
  3       , v$transaction t
  4       , v$session s
  5   WHERE s.sid = SYS_CONTEXT('USERENV', 'SID')
  6     AND t.addr = s.taddr
  7     AND r.usn = t.xidusn
  8  /

SQL> EXEC DELETE x;

SQL> 1 SELECT r.extends - &&extends extra
SQL> /

     EXTRA
----------
        12

SQL> COLUMN planned FORMAT 999999
SQL> COLUMN total FORMAT 999999
SQL> SELECT TO_NUMBER(&&rows) planned
  2       , TO_NUMBER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')) total
  3    FROM dual
  4  /

PLANNED   TOTAL
------- -------
 100000  100012


Life is cruel! Beware!

P.S.: BTW, now I definitely know why I love Denmark and a reason is explained here by Mogens. And he definitely knows all those reasons! :-)

Reading files in a directory

Fairly typical task, one can find some java based solutions at AskTom website. This post illustrates how to to get the listing using relatively modern extensions of Oracle RDBMS: dbms_scheduler and external tables. I'm using 10.2.0.3.0 running on RH Linux.

The idea is simple: we execute an external OS command and then get its output back via external table interface.

First thing we have to do is to come up with a command, I'm using a simple shell script:

#!/bin/sh
[ -z "$1" -o -z "$2" ] && exit 1
/usr/bin/find "$1" -type f -maxdepth 1 -ctime -1 -printf "%f %CY%Cm%Cd%CH%CM%CS\n" > "$2"

As you can see the script accepts two arguments: first is a directory, second a file that then will be queried as an external table.

Then we create an external table:

CREATE OR REPLACE DIRECTORY TMP AS '/tmp'
/
CREATE TABLE get_dir_context (
  filename    VARCHAR2(1024)
, ctime       DATE
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY tmp
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY " "
  (
    filename   CHAR(1024)
  , ctime      DATE "YYYYMMDDHH24MISS"
  )
 )
 LOCATION ('listing.txt')
)
REJECT LIMIT UNLIMITED
/

And eventually, execute the external command using dbms_scheduler:

DECLARE
  l_job_name                  VARCHAR2(30) := 'GET_DIR_CONTENT';
  l_command                   VARCHAR2(30) := '/tmp/get_dir_content.sh';
  l_directory                 VARCHAR2(1024) := '/tmp';
  l_outputfile                VARCHAR2(1024) := '/tmp/listing.txt';
BEGIN
  dbms_scheduler.create_job(
    job_name          => l_job_name
  , job_type          => 'EXECUTABLE'
  , job_action        => l_command
  , enabled           => FALSE
  , number_of_arguments => 2
  , auto_drop         => TRUE
  );
  dbms_scheduler.set_job_argument_value(
    job_name          => l_job_name
  , argument_position => 1
  , argument_value    => l_directory
  );
  dbms_scheduler.set_job_argument_value(
    job_name          => l_job_name
  , argument_position => 2
  , argument_value    => l_outputfile
  );
  dbms_scheduler.enable(l_job_name); 
END;
/

One can use these commands to monitor the execution and fetch the content:

SELECT job_name, status, run_duration, actual_start_date, additional_info
  FROM user_scheduler_job_run_details;

COLUMN filename FORMAT A30
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT * FROM get_dir_context;

EXEC dbms_scheduler.purge_log; 

In this example the script is placed into a /tmp directory but make sure you secure the scripts used in production!

A Three Rows Deadlock

Well, it just happens too many times, way too many times... This post does not explain anything in details, it just shows the code and presents a quotation from the official Oracle RDBMS documentation. I use 10.2.0.3 version of Oracle RDBMS.

Here we go... Let's suppose we've got a table. It has two column one is a numeric — an identifier, the other one is a date when the row was inserted or modified. For clarity and simplicity I don't use any other columns. So, here is what we have:

CREATE TABLE t (id, dt)
AS
  SELECT 1, SYSDATE
    FROM dual
   UNION ALL
  SELECT 2, SYSDATE + 1
    FROM dual
/

A simple table with two rows in it. Nothing fancy.

We also have to define some simple operations. From time to time we want to get some rows from this table for update purposes. To make sure we don't introduce any deadlocks we order them by date.

SELECT id, dt
  FROM t
 ORDER BY dt
   FOR UPDATE
/

Also, from time to time people come and do modifications, or insert new rows, for instance:

UPDATE t
   SET dt = dt + 1
 WHERE id = 2
/

or

INSERT INTO t VALUES(3, SYSDATE - 1)
/

Again, nothing fancy. The fancy part comes now. Sit tight! Let's simulate the process here:

SESSION#1:
UPDATE t
   SET dt = dt + 1
 WHERE id = 2
/

SESSION#2:
SELECT id, dt
  FROM t
 ORDER BY dt
   FOR UPDATE
/

SESSION#3:
INSERT INTO t VALUES(3, SYSDATE - 1)
/
COMMIT
/
SELECT id, dt
  FROM t
 ORDER BY dt
   FOR UPDATE
/

After this point we have two sessions #2 and #3 blocked.

The show is about to begin! If issue COMMIT in the session #1 we see something like this:

SESSION#2:
        ID DT
---------- ---------
         3 10-SEP-07
         1 11-SEP-07
         2 13-SEP-07

SESSION#3:
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

Ta-da! We got a deadlock even we used a legitimate SELECT FOR UPDATE statement. Why? Let's read the documentation:

The return set for a SELECT... FOR UPDATE may change while the query is running; for example, if columns selected by the query are updated or rows are deleted after the query started. When this happens, SELECT... FOR UPDATE acquires locks on the rows that did not change, gets a new read-consistent snapshot of the table using these locks, and then restarts the query to acquire the remaining locks.

This can cause a deadlock between sessions querying the table concurrently with DML operations when rows are locked in a non-sequential order. To prevent such deadlocks, design your application so that any concurrent DML on the table does not affect the return set of the query. If this is not feasible, you may want to serialize queries in your application.

Life is cruel.