13 – Happy or Unhappy Number?

This is my 13 post. So I’ve decided to make some digression. Usually number 13 is considered as unhappy. It seems not a rule for me. This year 13 is my happy number. On 13th January I’ve signed new job contract. It was just about 13:15 (or might be it was exactly at 13:13). What is more interesting, sum of digits in that date (13-01-2006) is equal to 13 (1+3+0+1+2+0+0+6=13). Number of building I’m now working in is also 13. As for now I’m quite happy in my new job.
Also according to Walter Schneider 13 is definitely happy number. I’ve observed that for me the unhappiest day in month is 26th. In fact it is 2*13. When I use Walters method to it gives me: 26 => 40 (2*2+6*6) => 16 => 37 => 58 => 89 => 145 => 42 => 20 => 4. So 26 is unhappy number and it confirms my observations :).
More on Happy Numbers in Wikipedia.

Cheers, Paweł

Flashback Table and Materialized View – not working together

I must say that I like Flashback features very much. This is very useful option and should be used more often. But sometimes you might hit some obstacles. One of them is Materialized view. When You want to issue FLASHBACK TABLE statement on table that has materialized view it does not work.
Lets try:

SQL> create table a_fb 
  2  (id number primary key, text char(200)) enable row movement;
Table created.
SQL> create materialized view log on a_fb with rowid;
Materialized view log created.

Now insert some data:

SQL> insert into a_fb select object_id, object_name
  2  from all_objects where rownum <= 100;
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568668        258       1069        100                       

ok. Lets insert more data and then try to flashback to above SCN.

SQL> insert into a_fb select object_id, object_name
  2  from all_objects where rownum <= 100
  3  and object_id not in(select id from a_fb);
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568704        258       1257        200                       

And now flashback:

SQL> flashback table a_fb to scn 568668;
Flashback complete.

It seems that Materialized view log is not a problem. Moreover when FLASHBACK TABLE is executed new rows are inserted to Materialized view log. You can check it issuing this select:

select count(*) from MLOG$_A_FB;

Now lets try to create materialized view

SQL> create materialized view a_fb_mv
  2  REFRESH FAST ON COMMIT with rowid
  3  as
  4  select id, text, rowid a_rowid from a_fb;
Materialized view created.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568852        258       1069        100                       

Lets add some data and roll back to that SCN

SQL> insert into a_fb select object_id, object_name
  2  from all_objects where rownum <= 100
  3  and object_id not in(select id from a_fb);
100 rows created.
SQL> commit;
Commit complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568893        258       1257        200                       

Lets see what we have in Materialized view:

SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb_mv;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  568895        258       1257        200                       

Problem

Try to flashback:

SQL> flashback table a_fb to scn  568852;
flashback table a_fb to scn  568852
                *
ERROR at line 1:
ORA-08194: Flashback Table operation is not allowed on materialized views 

You cannot flashback table if there is materialized view defined on it. But materialized view log is not a problem. I must say that I do not see any reason for that. If materialized view log is generated during flashback operation why materialized view is not updated according to that log?

Workaround

This is quite simple. You just have to drop materialized view and issue flashback table statement:

SQL> drop materialized view a_fb_mv;
Materialized view dropped.
SQL> flashback table a_fb to scn  568852;
Flashback complete.
SQL> select sys.dbms_flashback.get_system_change_number,
  2  min(id), max(id), count(*)
  3  from a_fb;
GET_SYSTEM_CHANGE_NUMBER    MIN(ID)    MAX(ID)   COUNT(*)                       
------------------------ ---------- ---------- ----------                       
                  569009        258       1069        100                       

Now you need only to recreate dropped materialized view.

Related articles:

• UNDO_SQL in FLASHBACK_TRANSACTION_QUERY
• Rolling Back the DDL
• More on Flashback

ORA_ROWSCN continuation…

In my previous post I was wondering how SCN is assigned to ROW or Block. I couldn’t find explanation in Oracle Documentation. But I’ve found this document written by Jonathan Lewis. You should read this! I’m little afraid about 'best guess' technology.
By the way you can also read article about undocumented userenv('commitscn') written in May 1999. When I first saw it I thought it might be replaced by ORA_ROWSCN.
So I’ve try this:

SQL> create table t (n1 number, text varchar2(10)) ROWDEPENDENCIES;

Table created.

SQL> insert into t values (userenv('commitscn'), 'T1');

1 row created.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
             4621851 T1                                                       

SQL> declare
 2    pragma autonomous_transaction;
 3  begin
 4    insert into t values (userenv('commitscn'), 'TAuto');
 5    commit;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
             4621851 T1                                                       
  4621852    4621851 TAuto                                                    

SQL> commit;

Commit complete.

SQL> select ora_rowscn, n1 commitscn, text from t;

ORA_ROWSCN  COMMITSCN TEXT                                                     
---------- ---------- ----------                                               
  4621854    4621853 T1                                                       
  4621852    4621851 TAuto

So for sure ORA_ROWSCN and userenv('commitscn') is not the same. I must say that I’m even more confused.

If You have some spare time, I recommend to read this post: Is it crazy to feel guilty about success? and from Eddie Awad blog: SYS_OP_MAP_NONNULL, REVERSE, LNNVL, NVL, NVL2, COALESCE

Cheers, Paweł

ORA_ROWSCN behavior: bug or feature?

ORA_ROWSCN is pseudo column that gives SCN (System Change Number) for transaction that was modifying record. Lets take a closer look. First create table:

 CREATE TABLE A_TABLE
   (ID NUMBER, TEXT CHAR(2000));

Then feed table with some data:

SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into a_table select rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

Procedure auto_commit is used to force change of SCN. Take a look what we have in table:

SQL> select ora_rowscn, count(*) from a_table
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4589308         24                                                           
   4589313         16                                                           
   4589293         15                                                           
   4589297         24                                                           
   4589302         21                                                           

We ran INSERT statement 10 times and we get 5 different SCN’s in table. None of them has exactly 10 rows. This is because table was created with default setting: NOROWDEPENDENCIES, and SCN is kept on block level. Every and each row in block has the same SCN. Lets commit this transaction:

SQL> commit;

Commit complete.

SQL> select ora_rowscn, count(*) from a_table
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4589363        100                                                           

Now all rows haveexactly the same SCN.
Now lets take a look what will happen for table with ROWDEPENDENCIES on.

 CREATE TABLE A_TABLE_ROW
   (ID NUMBER, TEXT CHAR(2000)) ROWDEPENDENCIES;

Insert some data:

SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into A_TABLE_ROW select rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

Query table:

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
                  100                                                           

Now ORA_ROWSCN is NULL for uncommitted transaction. This is very interesting.

SQL> commit;

Commit complete.

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)                                                           
---------- ----------                                                           
   4590773        100                                                           

What will happen when updating:

SQL> update A_TABLE_ROW set id = id where rownum <20;

19 rows updated.

SQL> select ora_rowscn, count(*) from A_TABLE_ROW
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)
---------- ----------
                   19
   4590773         81

We can conclude that data in active transaction have NULL ORA_ROWSCN for tables with ROWDEPENDENCY.
What about Index Organized Tables:

 CREATE TABLE A_TABLE_IOT
   (ID NUMBER primary key, TEXT CHAR(2000)) organization index;

Generate data:

 SQL> declare
  2    procedure auto_commit
  3    is pragma autonomous_transaction;
  4    begin commit; end;
  5  begin
  6    for i in 1..10 loop
  7      insert into A_TABLE_IOT select i*10+rownum, rownum 
from dual connect by level <=10;
  8      auto_commit;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select ora_rowscn, count(*) from A_TABLE_IOT
  2  group by ora_rowscn;

ORA_ROWSCN   COUNT(*)
---------- ----------
                  100

For IOT with NOROWDEPENDENCIES we still get NULLs for ORA_ROWSCN.

Questions:

1. Why we have such differences in implementation of ORA_ROWSCN?
2. We can see that SCN is assigned at commit. Does commit tackles all blocks modified by transaction? For short transaction it is not a problem, as blocks probably are still in buffer. But large transactions that generate lot of redo, and there are few check points during transaction time should not change this blocks. That means that block should have only transaction id, and oracle translates this transaction id to SCN.

Cheers, Paweł

Avoiding Deadlocks: SKIP LOCKED

After reading Tim's article on Deadlocks, I've recalled solution that can help avoid deadlocks in some situations.
Assume that in our application we have to choose few (say 5) documents from pool to assign to users for processing. Usually we can use just update statement like that:

UPDATE documents
SET doc_state = 'ASSIGNED',
  doc_user = USER
WHERE doc_state = 'TO PROCESS'
 AND rownum <= 5;

But this can cause some waits in highly loaded environments where this statement is run often. To overcome that we can use SKIP LOCKED clause:

BEGIN
  FOR r IN
    (SELECT id FROM documents
     WHERE doc_state = 'TO PROCESS'
     AND rownum <= 5 FOR UPDATE SKIP LOCKED) LOOP
    UPDATE documents
    SET doc_state = 'ASSIGNED',
      doc_user = USER
    WHERE id = r.id;
  END LOOP;
END;

In this statement we do not suffer from waits, and we get up to 5 rows assigned to current user.

Cheers, Paweł

Binary Days

As addition to may previous post lets see other Binary days, where day number is power of 2.

SQL> select rownum-1 power_of2
  2  , to_char(trunc(sysdate,'YYYY')-1+power(2,rownum-1),
  3    'DD-MON-YYYY DAY')  magic_day
  4  from dual connect by level <=9;

 POWER_OF2 MAGIC_DAY
---------- ---------------------------------------------------------
         0 01-JAN-2006 SUNDAY
         1 02-JAN-2006 MONDAY
         2 04-JAN-2006 WEDNESDAY
         3 08-JAN-2006 SUNDAY
         4 16-JAN-2006 MONDAY
         5 01-FEB-2006 WEDNESDAY
         6 05-MAR-2006 SUNDAY
         7 08-MAY-2006 MONDAY
         8 13-SEP-2006 WEDNESDAY

9 rows selected.

Interesting coincidence - there are only 3 days of week: SUNDAY, MONDAY and WEDNESDAY.

BTW. Does anybody know if is it possible to turn off these automatic <br /> tags in beta blogger?

Paweł

Programmers Day

Today is 256th day of year. It is considered as Programmers Day. Let's celebrate...

Cheers, Paweł

Row Generators

You probably now all these methods to generate rows. I've decided to choose the best method. My criterias are:

• Maximum Speed
• Without limitations
• Easy of use

To measure time I use PL/SQL block:

declare
v_start_time  timestamp;
v_end_time    timestamp;
v_max_number  integer;
v_max_gen     integer;
begin
v_max_number := 1000;
v_max_gen := 0;
v_start_time := systimestamp;
for r in (QUERY_HERE) loop
  v_max_gen := r.r;
end loop;
v_end_time := systimestamp;
dbms_output.put_line('Max gen = '||v_max_gen);
dbms_output.put_line('Time = '||(v_end_time-v_start_time));
end;

Where QUERY_HERE was one of:

ALL_OBJECTS	select rownum r from all_objects where rownum <= v_max_number
2*ALL_OBJECTS	select rownum r from all_objects, all_objects where rownum <= v_max_number
group by cube	select rownum r from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14)) where rownum <= v_max_number
level	select level r from dual connect by level <= v_max_number
PL/SQL Function	select rownum r from table(rowgen(v_max_number))
	where function was created using: CREATE OR REPLACE TYPE numtab_type IS TABLE OF NUMBER; CREATE or replace FUNCTION rowgen(pin_nor IN NUMBER) RETURN numtab_type DETERMINISTIC PIPELINED AS BEGIN FOR i IN 1..pin_nor LOOP PIPE ROW(i); END LOOP; RETURN; END rowgen; /

My Results, all times in seconds

Method/Rows	1000 rows	10000 rows	1000000 rows	Comment
ALL_OBJECTS	0.1	(*)	(*)	(*) only 5292 rows generated. We cannot generate big number of rows
2*ALL_OBJECTS	0.2	0.7	66.2
group by cube	0	0.1	> 5 min	Number of elements in cube clause vary on how big numbers we want get. For big numbers it is slow and not flexible in setting.
level	0	0.1	2.8
PL/SQL Function	0	0.1	2.2	We have to create Type and Function

Conclusion

If we consider speed, we should use one of two methods: level or PL/SQL Function. When we also consider the Easy of use we have only one solution select level r from dual connect by level <= v_max_number

Cheers, Paweł

Kubica 3rd in F1 race

Well Done
Polish driver Robert Kubica in his 3rd race in Formula 1 took 3rd place on Monza in Italy. This is really BIG SUCCESS as he is driving Formula 1 car for few weeks in races only.
Well done Robert. Keep it that way...

Rolling Back the DDL

Assume You were asked if You can rollback changes in tables in Oracle database, You would answer that it is not possible. DDL always makes commit before and after statement. But wait a minute: there is solution for that. In 10g we have Flashback feature. This feature can be extremely useful when you are running application patch on database. You can use this scenario:

1. issue command to set restore point:

   CREATE RESTORE POINT before_patch GUARANTEE FLASHBACK DATABASE;

2. apply application patch
3. test system after patch
4. if ok, release restore point:

   DROP RESTORE POINT before_patch;

5. if something go wrong, flashback to restore point:

   SHUTDOWN DATABASE;
   STARTUP MOUNT;
   FLASHBACK DATABASE TO RESTORE POINT before_patch;

Let me explain some issues. CREATE RESTORE POINT command has two versions: with and without GUARANTEE FLASHBACK DATABASE. When you enter GUARANTEE clause, then you must be sure that Flashback area is big enough to store all changed blocks. And at the end you must drop this restore point. Otherwise you will run into out of space in flashback area. You must also be aware that FLASHBACK DATABASE has impact on all schemas in database, so be careful.
Moreover you can use Restore Point while upgrading database 10g R1 to 10g R2. It can be very time saving in case of failure during upgrade process.
Restore Points can be also usefull in application testing process. You can define restore point, run test procedures, save results, restore to to saveponit and test again ...
To check if that you have set restore point you can run query:

select * from v$restore_point;

Hope you will find it useful,
Cheers, Paweł

Related articles:

• UNDO_SQL in FLASHBACK_TRANSACTION_QUERY
• Flashback Table and Materialized View – not working together
• More on Flashback

Visual SourceSafe - Invalid DOS Path

I hate wrong error messages. I hate messages that lead me in wrong direction. Yesterday I've spend too much time on resolving problem with VSS. I had to add new user to VSS. Pretty simple task.
I've started and bang: "Invalid DOS PATH \\server\vss\project\users"
I've checked path - it's OK.
Double checked. Again the same error.
I've checked my privileges - Full access.
I've made local copy of VSS repository - still the same problem.

It's time to search for solution on Internet.
Few click, and I've got it: no template.ini file in \\server\vss\project\users catalog.
So, I've copied ss.ini of one user to template.ini, removed unneeded lines, and task completed.
Full description of solution.

Whenever you are programming, please, make sure that error messages are meaningful to users. Try to give as many details as possible, it will help to solve problem.

Cheers, Paweł

Successful Programmer

At begging I would propose to read 7 Secrets of Successful programmers. I agree with all those suggestions. I would only change order:

1. Expect the unexpected and deal with it
2. Scope functions and variables appropriately
3. Keep your functions and subroutines simple
4. Layout code to increase legibility
5. Name your variables to aid readability
6. Code for human consumption
7. Comment often and comment well

Well, why I put commenting as last? Good code should be self-commenting. You should use meaningful names for functions and variables, have good layout of code. Commenting is very important when your use some unusual code construction or trick. Then it should be explained why. Too often i see comments that describe what is done in current line or block. It is useless. Comment should explain why this line is needed. What is done you can read from code.

Cheers, Paweł

About Blog and Me

Hi All Readers! I'm a Software Engineer with many years of experience in developing computer software. I'm mostly working on software that uses Database Systems. In past I was using dBase and Clipper. Since 1996 it is Oracle. I was working on all version since Oracle 7.3 and on many platforms: Novel, Sun Solaris, IBM AIX, Compaq Tru64, Windows. On this blog I want to share my knowledge and experience with all visitors. I hope You will find some interesting and useful topics here. I'll be writing about my current problems and solutions, tricks I use in everyday work, bugs and workarounds, and thinks that are interesting to me, and You might find interesting too. Cheers, Paweł