Category Archives: Blogroll

Obfuscation contest

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen
Chen Shapira

Ok, I give a try.

To make the query unreadable and unformatable I used no space, no new line, and I started by q’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like

connect by .1 < 1.
connect by .25 > rownum/100.

with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2″. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.

dburitype(
'/SYS/DBA_PROCEDURES/ROW[PROCEDURE_NAME=''GETKEY'']/OBJECT_NAME'
).getxml()

Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get

"NVL2"(Q'<'>',"UTL_RAW".CAST_TO_VARC
------------------------------------
Oracle Community_Obfuscation_Contest

Side effect of cursor sharing

Cursor sharing transform strings in bind variable.

So if you do SELECT * FROM EMP WHERE ENAME='SCOTT'; it will be transformed in SELECT * FROM EMP WHERE ENAME=:sys_b0;

This sometimes improved performance of application that do not use binds, for instance.

Now let’s imagine your application generates trace files. The application generates a SELECT 'LSC-11.1011.000.1110.1.0100.000.110' FROM DUAL;
. Ok, let’s look for the string in trace files :

$ cd udump
$ grep LSC-11 *.trc
$
$ grep DUAL$ *.trc
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL

WTF! The tracing mechanism of the application is no longer usable :twisted:

Another side effect is the length of the column and is described there : http://asktom…P11_QUESTION_ID:3696883368520

alter user identified by values in 11g

I wrote about dba_users changes in 11g .

When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords.

When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved.

SQL> create user u identified by u;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> connect u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect u/u
Connected.

Per default only the proper case works

SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> connect u/U
Connected.
SQL> conn u/u
Connected.

When sec_case_sensitive_logon=false, both uppercase and lowercase passwords work (10g behavior).

When issuing a create user identified by values, you must chose if you want to have both passwords, only the case insensitive or only the case sensitive.


SQL> select password,spare4 from user$ where name='U';

PASSWORD
------------------------------
SPARE4
--------------------------------------------------------------
18FE58AECB6217DB
S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4

The hashes are in USER$.

SQL> alter user u identified by values '18FE58AECB6217DB';
User altered.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4';
User altered.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4;18FE58AECB6217DB';
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When using both hashes, switching back and forth to 11g mechanism is possible.

Software Configuration Manager

I just noticed today the Web 2.0 interface of metalink :

csm

The URL is http://csm.oracle.com, like Coftware Sonfiguration Manager (or maybe Configuration Software Manager).
[edit: original name is: Configuration Support Manager]

It seems nice, you can customize the portal with drag and drop, their is a blog too http://blogs.oracle.com/supportportal and a feedback button I am going to use right now!

Enjoy the new interface to read and update your SR :)

sr.png

take care of minus !

Imagine this script (10gR2) :

set echo on
select  BINARY_DOUBLE_INFINITY -
BINARY_DOUBLE_INFINITY from DUAL;

Run it and you would will get an expected result!

SQL> select BINARY_DOUBLE_INFINITY -
> BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY
----------------------
                   Inf

The issue in sqlplus is that – at the end of line means “query continues next line”. The correct answer of Inf-Inf is Nan.

SQL> select BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
---------------------------------------------
                                          Nan

Oracle 10.2.0.4

Sven’s Technik-Blog » Blog Archive » Oracle 10.2.0.4 für Linux x86 ist verfügbar…

Patchset 6810189 is now available for Download on Metalink.

[[code]]czozMzI6XCJzZWxlY3QgKiBmcm9tIHYkdmVyc2lvbjsNCg0KQkFOTkVSDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS17WyYqJl19LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KT3JhY2xlIERhdGFiYXNlIDEwZyBFbnRlcnByaXNlIEVkaXRpb24gUmVsZXtbJiomXX1hc2UgMTAuMi4wLjQuMCAtIFByb2QNClBML1NRTCBSZWxlYXNlIDEwLjIuMC40LjAgLSBQcm9kdWN0aW9uDQpDT1JFICAgIDEwLjIue1smKiZdfTAuNC4wICAgICAgUHJvZHVjdGlvbg0KVE5TIGZvciBMaW51eDogVmVyc2lvbiAxMC4yLjAuNC4wIC0gUHJvZHVjdGlvbg0KTkxTUlR7WyYqJl19TCBWZXJzaW9uIDEwLjIuMC40LjAgLSBQcm9kdWN0aW9uDQpcIjt7WyYqJl19[[/code]]

add a new language to Oracle

I first thought of adding Klingon. Well, finally I added Romansh, which is the fourth official language in my country.

Ok, here we go :

$ $ORACLE_HOME/nls/lbuilder/lbuilder &

The Oracle Locale Builder tool is started.

File –> New… –> Language

You specify the language, the spelling for January, for Monday, etc… Most fields are mandatory. If you do not know about one field, like EBCDIC, just find one in the “Show existing definition…” Dialog

File –> Save as…

You save your nlt file

File –> Tools –> Generate NLB

Three NLB files are generated.

$ ls -l *.nl*
-rw-r--r-- 1 lsc users   934 2008-02-19 18:17 lx003eb.nlb
-rw-r--r-- 1 lsc users  3843 2008-02-19 18:17 lx003eb.nlt
-rw-r--r-- 1 lsc users   128 2008-02-19 18:17 lx0boot.nlb
-rw-r--r-- 1 lsc users   428 2008-02-19 18:17 lx0boot.nlt
-rw-r--r-- 1 lsc users 22528 2008-02-19 18:17 lx1boot.nlb

Copy all your the NLB files to your ORACLE_HOME/data/nls.

Restart your instance.

Let’s test it ;)


select 
  to_char(
    sysdate, 
    'fmday dd month yyyy','NLS_DATE_LANGUAGE=romansch'
  ) 
from dual;
TO_CHAR(SYSDATE,'FMDAYDDMONTHYY
-------------------------------
mardis 19 favrer 2008

milliseconds in alert log

In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one.

$ tail -3 alert*.log
Mon Feb 04 15:52:38 2008
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;

If I check in the xml file, I get more info about the execution time, which contains milliseconds
$ adrci exec="show alert -term"|tail -4
2008-02-04 15:52:38.366000 +01:00
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
2008-02-04 15:52:42.101000 +01:00
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;

This timestamp could be really usefull !

There is also a command -tail for adrci, but it outputed the whole log for some unkown reason

Why cannot I use subquery there?

Is there any rule where you can use scalar subquery?
You can use a scalar subquery expression in most syntax that calls for an expression (expr).

Well, you cannot use it as the second argument of sys_connect_by_path

select sys_connect_by_path(ename,
   (select '/' from dual)) from emp 
   connect by prior empno=mgr;
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH 
function

You cannot use it in the ITERATE or in the RULES clause of model

SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
rules iterate ( (select 1 from dual) ) (y[0]=0);
*
ERROR at line 1:
ORA-32607: invalid ITERATE value in MODEL clause
SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
(y[0]=(select 1 from dual));
*
ERROR at line 1:
ORA-32620: illegal subquery within MODEL rules

Also impossible is in the DATAOBJ_TO_PARTITION function that is used in System Partitioning :

SQL> insert into t partition (
  dataobj_to_partition("T",
    (select :partition_id  from dual) )) 
values ('x') ;
*
ERROR at line 1: 
ORA-14198: rowid column must refer to table 
specified in 1st parameter 

Another documented limitation is the GROUP BY clause :


SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT ;
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT (SELECT COUNT(*) FROM EMP), COUNT(*) 
  FROM DEPT GROUP BY (SELECT COUNT(*) FROM EMP);
*
ERROR at line 1:
ORA-22818: subquery expressions not allowed here

SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT GROUP BY ();

(SELECTCOUNT(*)FROMEMP)   COUNT(*)
----------------------- ----------
                     14          4

How to resolve ORA-09925 ?

This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual…

There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit].

1) AUDIT_FILE_DEST is not writable

$ env
_=/usr/bin/env
ORACLE_SID=FOO
TERM=dtterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3
PWD=/u01/app/oracle/product/10.2.0/db_3
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -      
root's Password:
# mkdir /bar 
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.

2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable :!:


$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump  
drwxr-xr-x   2 root   dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:29 2007

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

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump 
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:48 2007

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

Connected to an idle instance.

SQL> quit

3) $ORACLE_HOME/rdbms/audit is not writable


$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:48:09 2007

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

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit 
drwxr-x---   2 root     dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:49:12 2007

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

Connected to an idle instance.

SQL> quit

my query is not using my index

I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries

I did not know that. Do I need to use hints?

set autotrace traceonly explain
create table t as 
select 
  sign(rownum-1) r, rpad(rownum,4000,'.') t 
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

select /*+ INDEX(T,I) */ r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787

--------------------------------------------------------------
----------------------

| Id  | Operation                   | Name | Rows  | Bytes | C
ost (%CPU)| Time     |

--------------------------------------------------------------
----------------------

|   0 | SELECT STATEMENT            |      |     1 |  4004 |
1979   (2)| 00:00:24 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |  4004 |
1979   (2)| 00:00:24 |

|*  2 |   INDEX FULL SCAN           | I    |     1 |       |
1979   (2)| 00:00:24 |

--------------------------------------------------------------
----------------------

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

   1 - filter("R"<>1)

SQL> select r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------
------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------
------------

|   0 | SELECT STATEMENT  |      |     1 |  4004 |   271K  (1)
| 00:54:18 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   271K  (1)
| 00:54:18 |

--------------------------------------------------------------
------------

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

   1 - filter("R"<>1)

Oracle 11g Hot patching

Online Patching : you can apply or roll back online patches while the RDBMS instance is running

1) download an interim patch for 11g, f.ex. dummy patch 6198642
2) unzip p6198642_111060_LINUX.zip
3) cd 6198642
4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql

Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/oracle/product/11/db_4
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.0
OUI version       : 11.1.0.6.0
OUI location      : /opt/oracle/product/11/db_4/oui
Log file location : /opt/oracle/product/11/db_4/cfgtoollogs/
    opatch/opatch2007-12-05_21-23-42PM.log

RollbackSession rolling back interim patch '6198642' from OH
    '/opt/oracle/product/11/db_4'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory 
    and will patch the local system only.

Please shutdown Oracle instances running out of this 
    ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/11/db_4')

Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files affected by the patch '6198642' for restore. 
    This might take a while...
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
    6198642_May_07_2007_00_50_36/original_patch/custom/
    scripts/pre -rollback 6198642 ':

Return Code = 0

Patching component oracle.rdbms, 11.1.0.6.0...
Copying file to "/opt/oracle/product/11/db_4/cpu/CPUDummy2007/
    catcpu.sql"
RollbackSession removing interim patch '6198642' from inventory

---------------------------------------------------------------
This is a dummy patch for testing only
---------------------------------------------------------------
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
    6198642_May_07_2007_00_50_36/original_patch/custom/scripts/
    post -rollback 6198642 ':

Return Code = 0

Running the "apply" sql script "/opt/oracle/product/11/db_4/cpu/
    CPUDummy2007/catcpu.sql" with reference to 'patchmd.xml' file 
    for the patch "6198642"...

The local system has been patched and can be restarted.

OPatch succeeded.

OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable ;-)

Tom Kyte tour in Europe

« Ask Tom Live » European Tour 2008

 City Date Price Register
Madrid 28 – 29 January 924 ** Click here
London 25 – 26 February £1,101 * Click here
Berlin 6 – 7 May € 1,474 ** Click here
* This price is valid until 31 January 2008 only
** This price is valid until 29 February 2008 only

Note that if you register after Feb 29th for the Madrid event (which is in January), the price is no longer valid :twisted:

I am also surprised to see such a price difference for the same course between the different events.

backup bigfile tablespace

One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel.


SQL> create bigfile tablespace big datafile size 1g;

Tablespace created.

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE 
  TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE 
  TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace big section size 100m;

Starting backup at 30.11.2007 07:23:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=42 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=41 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 30.11.2007 07:23:37
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_2: starting piece 2 at 30.11.2007 07:23:41
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_3: starting piece 3 at 30.11.2007 07:23:45
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_4: starting piece 4 at 30.11.2007 07:23:48
channel ORA_DISK_1: finished piece 1 at 30.11.2007 07:23:49
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_1_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 30.11.2007 07:23:53
channel ORA_DISK_2: finished piece 2 at 30.11.2007 07:23:53
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_2_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_2: starting piece 6 at 30.11.2007 07:23:57
channel ORA_DISK_3: finished piece 3 at 30.11.2007 07:23:57
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_3_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 76801 through 89600
channel ORA_DISK_3: starting piece 7 at 30.11.2007 07:24:01
channel ORA_DISK_1: finished piece 5 at 30.11.2007 07:24:01
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_5_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 89601 through 102400
channel ORA_DISK_1: starting piece 8 at 30.11.2007 07:24:05
channel ORA_DISK_2: finished piece 6 at 30.11.2007 07:24:05
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_6_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 102401 through 115200
channel ORA_DISK_2: starting piece 9 at 30.11.2007 07:24:09
channel ORA_DISK_4: finished piece 4 at 30.11.2007 07:24:09
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_4_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:21
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 115201 through 128000
channel ORA_DISK_4: starting piece 10 at 30.11.2007 07:24:13
channel ORA_DISK_1: finished piece 8 at 30.11.2007 07:24:13
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_8_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 128001 through 131072
channel ORA_DISK_1: starting piece 11 at 30.11.2007 07:24:18
channel ORA_DISK_2: finished piece 9 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_9_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_3: finished piece 7 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_7_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_4: finished piece 10 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_10_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: finished piece 11 at 30.11.2007 07:24:19
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_11_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30.11.2007 07:24:19

This works fine ;-)

SECTION SIZE requires 11gR1 or later

Create edition

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage.

One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time.

Unfortunately, the feature has not been implemented in 11g Release 1.

As you can see, the CREATE EDITION command does not work :

SQL> create edition v1;
create edition v1
       *
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> alter session set edition=v1;
ERROR:
ORA-38802: edition does not exist

The error messages ORA-38801 to ORA-38816 refer to EDITIONS.

SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ------
ORA$BASE                         YES

SQL> alter session set edition=ora$base;

Session altered.


Apparently the feature is implemented, but not ready to be used. Be patient …

random statements

I read the ultimate excuse database and checked if I can use model to do generate random statements:


with t as (
  select '%E and %E are travelling to %L.' fmt 
  from dual union all
  select 'About %N employees live in %L.' 
  from dual)
select str from t
model
reference
  dept on (
    select loc,count(*) over () c,rownum r 
    from dept)
  dimension by (r) measures (loc,c)
reference
  emp on (
    select ename,count(*) over () c, rownum r 
    from emp)
  dimension by (r) measures (ename, c) 
partition by (fmt)
dimension by (1 x) 
measures (cast(fmt as varchar2(4000)) str)
rules 
  iterate (1000) 
  until str[1] not like '%/%%' escape '/'
(str[1]=substr(str[1],1,
  decode(instr(str[1],'%'),0,4000,
    instr(str[1],'%')-1))||
  decode(substr(str[1],nullif(
    instr(str[1],'%'),0)+1,1),
    'N',to_char(trunc(
      dbms_random.value(0,1+emp.c[1]))),
    'E',initcap(emp.ename[trunc(
      dbms_random.value(1,1+emp.c[1]))]),
    'L',initcap(dept.loc[trunc(
      dbms_random.value(1,1+dept.c[1]))]))||
    substr(str[1],nullif(
      instr(str[1],'%'),0)+2))
/
STR
----------------------------------------
James and Ward are travelling to Dallas.
About 1 employees live in Chicago.
/
STR
--------------------------------------------
King and Blake are travelling to New York.
About 3 employees live in Dallas.

What is ROWNUM=1 ?

Is rownum=1 the first row returned? well, it depends :


select rownum, ename 
from emp 
order by ename;
    ROWNUM ENAME
---------- ----------
        11 ADAMS
         2 ALLEN
         6 BLAKE
         7 CLARK
        13 FORD
        12 JAMES
         4 JONES
         9 KING
         5 MARTIN
        14 MILLER
         8 SCOTT
         1 SMITH
        10 TURNER
         3 WARD

The ROWNUM is evaluated before the order by

select rownum 
from emp 
group by rownum 
having rownum between 3 and 6;
    ROWNUM
----------
         6
         5
         3
         4

the rownum is selected before the GROUP BY and before the HAVING.

select 
   rownum, 
   sal, 
   median(sal) over () 
from emp;
    ROWNUM        SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
         1        800              1550
        12        950              1550
        11       1100              1550
         3       1250              1550
         5       1250              1550
        14       1300              1550
        10       1500              1550
         2       1600              1550
         7       2450              1550
         6       2850              1550
         4       2975              1550
         8       3000              1550
        13       3000              1550
         9       5000              1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function

OR aggregate

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR


1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)

Let’s try


with t as (
  select 10 n from dual union all 
  select 12 from dual union all 
  select 6 from dual) 
select 
  utl_raw.cast_to_binary_integer(
    sys.mvaggrawbitor(
      utl_raw.cast_from_binary_integer(
        n
      )
    )
  ) N 
from t;
  N
---
 14

It is that easy !

disclaimer: mvaggrawbitor is not documented

Wake up SF, Oracle is coming !

I just arrived at San Francisco and meet my colleague Daniel and Lutz Hartmann. Thanks Dani for driving us to SF and thanks Lutz for the Indian food in the restaurant where we ended last year during Blogger 2006 Meetup :-) . By the way do not miss 2007 Meetup organized by Mark Rittman : oow 2007 blogger meetup

I took the cable car from Lutz hotel to the Fishermann’s Wharf. Here it is raining, but well, in Zurich it was snowing and the wind was so cold that they needed to unfreeze the aisles of the plane before take off!

In less than 8 hours the 11g exam cram is starting at Moscone South 104 ! My notebook clock is still set to Sun Nov 11, 10:10 am, I will try to sleep until 7am…