OraNA :: Oracle News Aggregator

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...)

Tim wrote
… 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
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958#14442395195806
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>
)

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 )

sokrates@11.2 > create directory t as '/tmp';
Directory created.
sokrates@11.2 > !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... 66.29.212.73
Connecting to www.w3schools.com|66.29.212.73|: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...)

sokrates@11.2 > create package p is
2 function func return int deterministic;
3 end p;
4 /

Package created.

sokrates@11.2 > 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 ?

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e53000.htm#sthref13803

says

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...)

with qs as
(
select
round(
dbms_random.value(-20, 20),
dbms_random.value(1, 15)
) q
from dual
connect by level<=10
)
select
qs.q,
case
when qs.q = floor(qs.q) then
0
when qs.q is null then null
else
(
select max(level)
from dual
connect by round(qs.q, level - 1) != qs.q
)
end as "scale(q)"
from qs

with y as
(
   select 
      add_months(date'2012-01-01', level-1) monn, 
      to_char(add_months(date'2012-01-01', level-1), 'MONTH') mon
   from dual
   connect by level<=12
)
select 
   value as language, 
   y.mon, 
   to_char(y.monn, 'MONTH', q'|nls_date_language='|' || value || q'|'|') month, 
   to_char(y.monn, 'MON', q'|nls_date_language='|' || value || q'|'|') month_s
from v$nls_valid_values n, y
where n.parameter='LANGUAGE'
order by language, y.monn

TRADITIONAL CHINESE looks easy