Sokrates on Oracle

Archive for the ‘Bug’ Category

Yet Another Elementary SQL Bug

Posted by Matthias Rogel on 20. February 2014

Environment

sokrates@12.1 > select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8
sokrates@12.1 > !echo $NLS_LANG
american_america.UTF8

Setup is

create table t ( r varchar2(1 char));
insert into t values('£');
commit;

alter session set nls_length_semantics=BYTE;
alter table t add( v as ( cast(r as varchar2(1))));

alter session set nls_length_semantics=CHAR;
create index t_idx on t(v);

exec dbms_stats.set_table_stats(null, 'T', numrows => 1e7)

We get different result for count(v) when using length-semantics BYTE, depending if a table scan or an index scan is used:

sokrates@12.1 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@12.1 > select /* index full scan used */ count(v) from t;

  COUNT(V)
----------
	 1

sokrates@12.1 > select /* full table scan used */ count(*), count(v) from t;

  COUNT(*)   COUNT(V)
---------- ----------
	 1	    0

Same behaviour was observed on 11.2

Posted in 11.2, 12c, Bug, sql | 4 Comments »

Issue with updatable views

Posted by Matthias Rogel on 7. January 2014

It’s sometimes amazing, how many bugs there are still with elementary SQL.

Here is one concerning updatable views:

sokrates@12.1 > create table t ( v varchar2(30) );

Table created.

sokrates@12.1 > create view v as
  2  select v as dontdothatman, v as canbelostwheninserted
  3  from t; 

View created.

sokrates@12.1 > insert /* this is fine */ into v 
  2  values('fine', 'fine');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine

sokrates@12.1 > insert /* exception expected because 1st value is lost */ into v
  2  values('this one is lost', 'why isnt that one lost ?');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine
why isnt that one lost ?       why isnt that one lost ?

Posted in 12c, Bug, sql | 4 Comments »

ORA-54012 oddity and a workaround

Posted by Matthias Rogel on 3. September 2012

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 is only one virtual column.
It seems, I cannot name a virtual column the same as its generating function – at least in a “create table”, we have to split this “create table” in a “create table” and an “alter table”.

So

sokrates@11.2 > create table t(
2 i int,
3 fun as ( p.func() )
4 );

Table created.

sokrates@11.2 > alter table t rename column fun to func;

Table altered.

So we end up in a table for which the SQL generated by dbms_metadata cannot be replayed, see:

sokrates@11.2 > select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SOKRATES"."T"
   (    "I" NUMBER(*,0),
        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

sokrates@11.2 > drop table t purge;

Table dropped.

sokrates@11.2 > REM copy & and paste the above DBMS_METADATA - output
sokrates@11.2 > CREATE TABLE "SOKRATES"."T"
  2   (    "I" NUMBER(*,0),
  3        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
  4   ) SEGMENT CREATION DEFERRED
  5  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  6  TABLESPACE "USERS";
      "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
      *
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression

Posted in 11.2, Allgemein, Bug, bugfighting | Leave a Comment »

V$BLOCKING_QUIESCE and the difference between killing and disconnecting

Posted by Matthias Rogel on 5. January 2012

Testing how to quiesce an instance today:

SID 94 > alter system quiesce restricted;
(15 minutes ago on test-db 11.2 )
Ok, it waits for several sessions to finish, right ?
( see Doc
Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. … )

So, we queried V$BLOCKING_QUIESCE and killed all sessions listed there via
alter system kill session ....
and saw them disappear in V$BLOCKING_QUIESCE thereafter.

However, after killing the last one we expected SID 94 to finish the quiesce – command, but: it didn’t.


SQL > select * from v$blocking_quiesce;

no rows selected

SQL > select sw.event
2 from v$session_wait sw
3 where sw.sid=94;

EVENT
——————————–
wait for possible quiesce finish

… for several minutes.

bouncing and restarting the instance and repeating the same experiment but using

alter system disconnect session .... immediate

rather than

alter system kill session ....

gave the expected result.

So, when we want to quiesce our instance we’ll use this algorithm.

(We’ll do that only on a  weekend maintenance windows when no interactive user is connected, all batch jobs running will be restartet automatically by our scheduling system when system is unquiesced again.)

But: seems to be a bug to me that V$BLOCKING_QUIESCE showed no entry though quiescing was apparently blocked for minutes.

Posted in 11.2, Bug, dba | Leave a Comment »

ORA-8103s

Posted by Matthias Rogel on 9. August 2011

looking forward to Tanel Poder’s webinar this evening.
Especially, the announcement to learn something about “ORA-8103s” called my attention.
The reason is, that I was hitting some bug or bugs in 11.2 (sporadically and not reproducible ORA-8103s) last November and working with support on this since then (issue has status “Development working” since some weeks now)
They used to occur several times per day in complex queries (selects in batch jobs).
Usually, the following workaround was helpful: wait for a minute, then issue the same select again.
So, we “patched” the batch jobs they occurred in, set parameter event to
‘8103 trace name errorstack forever, level 10’
and uploaded tons (dozens of GBs) of tracefiles to support.
In the meantime, we applied some patches concerning other issues and monitored the number of ORA-08103’s per day via
with days as
(
select date'2010-11-19' + level as day
from dual
connect by date'2010-11-19' + level <= sysdate
)
select
days.day,
count(distinct x.message_text) as count_08103s_per_day
from x$dbgalertext x, days
where x.message_text(+) like '%ORA-08103%'
and trunc(x.originating_timestamp(+)) = days.day
group by days.day
order by days.day

After ugrading to 11.2.0.2.2 via Patch 11724916, this number went strongly down, but we still had issues from time to time with a false ORA-08103.

Today I noticed that since applying Patch 9285259 we did hit no ORA-08103 anymore !

Coincidence ? I don’t know

Update August 28th:
I was wrong, false ORA-08103s have not disappeared yet, we encountered some of them last week, re-activated the SR (which is open unsolved now since last November …).
The reason that I thought it had disappeared was that I didn’t see it mentioned in the alert.log anymore, however, since I had unset event, it was not logged there.

Posted in 11.2, Bug, bugfighting | Leave a Comment »

Prevent Validation of Check Constraints in 11.2.0.2

Posted by Matthias Rogel on 27. March 2011

11.2.0.2 has a great new “feature”: you can disable the validation of a check contraint during an update !

how does it work ?
it is a bit like magic: when an update violates a check constraint, just use a merge-statement to update the table.

See:

sokrates@11.2.0.2 > create table oops ( i int primary key, n number constraint ch_n_sm_5 check( n < 5 ));

Table created.

sokrates@11.2.0.2 > insert into oops values(1, 2);

1 row created.

sokrates@11.2.0.2 > insert into oops values(2, 4.5);

1 row created.

sokrates@11.2.0.2 > commit;

Commit complete.

sokrates@11.2.0.2 > update oops set n = n * 7 where i = 1;

update oops set n = n * 7 where i = 1
*
ERROR at line 1:
ORA-02290: check constraint (SOKRATES.CH_N_SM_5) violated

so far, so good. now the trick:

sokrates@11.2.0.2 > merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;

1 row merged.

sokrates@11.2.0.2 > REM oops !!!

sokrates@11.2.0.2 > select i, n from oops;

I        N
———- ———-
1       14
2      4.5

sokrates@11.2.0.2 > commit;

Commit complete.

Now, isn’t that a great new feature ?

What does Oracle say ?

Bug 9285259: MERGE IGNORES CHECK CONSTRAINT

says:


Fixed in Product Version 12.1

WORKAROUND:
-----------
none

So, in my opinion, if you use “merge” to update rows, you have a real problem and probably your best decision could be “don’t use 11.2 at all, wait until they have fixed that behaviour in 12.1”

Remarks.

I can reproduce the Bug on 11.2.0.2 on Linux x86-64

According to the metalink note mentioned above, it affects also 11.2.0.1, which I haven’t checked.

Furthermore, I don’t know if it’s only specific to that platform.

Posted in 11.2, Bug | 11 Comments »