It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.
firstname.lastname@example.org > create table strange(i int not null, j int not null); Table created. email@example.com > alter table strange add constraint unique_i unique(i) not deferrable (more...)
Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but (more...)
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.
Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2
Here is (more...)
Tom Kyte’s print_table procedure, available on
seems to be very popular and there exist tricky variations on the theme, for example the following nice xml-trick by Sayan Malakshinov.
Please note that it is very easy to use the existing print_table-code to generate a pipelined version which (more...)
If you are using Workspace Manager, it could be probably useful to know, that there is an undocumented restriction concerning import/export.
Due to Import and Export Considerations,
…Workspace Manager supports the import and export of version-enabled tables in one of the following two ways: a full database import (more...)
The biggest advantage of being developer and DBA at the same time in my eyes is: tuning lies in one hand.
Peter Scott twittered about bringing a query down from 25 hours to 83 seconds by rewriting a query using MINUS.
Funny, in February 1996 I started a new job and my first task was tuning a query running for several hours – basically
select ... from t where not exists ( select ... from r@remote r where <join t and r> )
which could be tuned down to a few seconds using MINUS, so quite similar to Peter’s job ( Version (more...)
The following SQL shows me what is currently in my buffer cache and runs in a reasonable amount of time ( never longer than 30 seconds with some dozens of GB buffer cache and around 5 million entries in v$cache ), it also shows me the cached percentage of each segment which is currently part of the cache.
Version is 11.2.
with cache_raw as ( select c.owner#, c.kind, c.name, c.partition_name, c.status, c.file#, count(*) co from v$cache c group by c.owner#, c.kind, c.name, c.partition_name, c.partition_name, file#, c.status ), (more...)
“Mounting” a csv-file as external-table is very handy.
Here a quick note to show, that we can “mount” an xml-file as well ( and query it via SQL )
firstname.lastname@example.org > create directory t as '/tmp'; Directory created. email@example.com > !wget -O /tmp/t.xml www.w3schools.com/xml/simple.xml --17:31:25-- http://www.w3schools.com/xml/simple.xml Resolving www.w3schools.com... 188.8.131.52 Connecting to www.w3schools.com|184.108.40.206|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 1135 (1.1K) [text/xml] Saving to: `/tmp/t.xml' 100%[============================================================================================================================>] 1,135 --.-K/s in 0s 17:31:25 (216 MB/s) - `/tmp/t. (more...)
firstname.lastname@example.org > create package p is 2 function func return int deterministic; 3 end p; 4 / Package created. email@example.com > create table t( 2 i int, 3 func as ( p.func() ) 4 ); func as ( p.func() ) * ERROR at line 3: ORA-54012: virtual column is referenced in a column expression
oops ? what’s wrong ?
ORA-54012: virtual column is referenced in a column expression
Cause: This virtual column was referenced in an expression of another virtual column
which is not true: there is no “another virtual column”, there (more...)