Software Engineer Thoughts: 2006

Saturday, December 30, 2006

Security: getting DBA rights quite easy

I've stepped today at ORASEC blog by Paul Wright. I've found this issue a big problem. Anybody that is using Oracle client on windows can quite easy get DBA rights by editing client9.dll file. I've to check if this is still problem with latest Oracle 10.2.0.3 release.

Cheers, Paweł

Thursday, December 21, 2006

Applying Oracle 10.2.0.3 patch

Today I've installed patch 10.2.0.3 for Oracle on Windows on top of 10.2.0.2 database. I've noticed that my spool file generated by upgrade script was 33 MB big. It occurred that source of all oracle build-in packages were spooled. It was terrible experience to search for errors in this log file, as searching for 'ORA-' give hundreds results and I had to figure out if it is in comment of package, or real error.
Fortunately patch was installed successfully and everything runs fine.

Cheers, Paweł

Thursday, December 07, 2006

UTL_MATCH - String Similarity in Oracle

You can compare string using equality operator (=), or using similarity operator (LIKE). But there are cases where it is not enough. You Can use UTL_MATCH package to calculate string similarity index. This packages offers 4 functions that take two strings as parameters:

edit_distance - algorithm by Levenshtein - returns number of edits that must be done to change one string into second,
edit_distance_similarity - normalized results of edit_distance in percents - integer values,
jaro_winkler - returns similarity based on Jaro-Winkler distance algorithm,
jaro_winkler_similarity - same as above but presented as integer in range 0-100.

Lets teke a look how it works. First we can create sample table and insert some data:

create table countries(name varchar2(15) not null);
insert into countries values ('Poland');
insert into countries values ('Germany');
insert into countries values ('United States');
insert into countries values ('Portugal');
insert into countries values ('Czech Republic');
insert into countries values ('China');
insert into countries values ('Slovakia');
insert into countries values ('Slovenia');
commit;

Now we can take a look at results. Lets compare to miss-spelled country name: 'Slovnia'

select name
 ,to_char(utl_match.edit_distance(name, 'Slovnia'),'999') edit_dist
 ,to_char(utl_match.edit_distance_similarity(name, 'Slovnia'),'999') edit_dist_sim
 ,to_char(utl_match.jaro_winkler(name, 'Slovnia'),'999d9999') jaro_winkler
 ,to_char(utl_match.jaro_winkler_similarity(name, 'Slovnia'),'999') jaro_winkler_sim
from countries
order by jaro_winkler_sim desc;

NAME                 EDIT EDIT JARO_WINK JARO
-------------------- ---- ---- --------- ----
Slovenia                1   88     .9750   97
Slovakia                2   75     .8881   88
China                   5   29     .5619   56
United States          12    8     .5531   55
Poland                  6   15     .5317   53
Portugal                7   13     .5119   51
Germany                 7    0     .3571   35
Czech Republic         13    8     .0000    0

Above we can observe differences in algorithms.
Lets test it on NULLs:

SQL> select to_char(utl_match.edit_distance('test', NULL),'999')
  2      edit_dist
  3   ,to_char(utl_match.edit_distance_similarity('test', NULL),'999')
  4      edit_dist_sim
  5   ,to_char(utl_match.jaro_winkler('test', NULL),'999d9999')
  6      jaro_winkler
  7   ,to_char(utl_match.jaro_winkler_similarity('test', NULL),'999')
  8      jaro_winkler_sim
  9  from dual;

EDIT EDIT JARO_WINK JARO
---- ---- --------- ----
  -1  125     .0000    0

We can see that using edit_distance on NULLs migth be dengerous.
All samples were run on Oracle 10g XE

Hope You find it usefull.
Paweł

Wednesday, November 22, 2006

MERGE and SEQUENCE

Last days I was working on code and I was using merges often. And i've notice some side-effect. When you use sequence in WHEN NOT FOUND clause then next sequence value is gathered for all rows processed in query. I've observed it on 10g R2 EE (10.2.0.2) and on Oracle XE. Let me show test case. First create sequence:

SQL> create sequence a_sq start with 1 increment by 1 nocache;
Sequence created.

This sequence generator starts with 1 with step 1. Lets taka look how many rows we have in USER_OBJECTS view:

SQL> select count(*) from user_objects;
 COUNT(*)
----------
 46

Lets create sample table and populate it with 10 rows:

SQL> create table a_tab as
 2 select object_id o_id, object_name o_name, a_sq.nextval o_sq
 3 from user_objects where rownum <=10;
Table created.

So we have table with 10 rows, and last number generated by sequence is also 10:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 10 10

Now we run merge:

SQL> merge into a_tab
 2 using (select object_id , object_name
 3 from user_objects )
 4 on (object_id = o_id)
 5 when matched then
 6 update set o_name = object_name
 7 when not matched then
 8 insert (o_id, o_name, o_sq)
 9 values (object_id, object_name, a_sq.nextval);
47 rows merged.

47 rows were merged, but 10 rows was updated and 37 inserted. Lets check it:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 47 57

It seems that sequence generated 47 new values, but only 37 rws were inserted. Lets run the same merge one more time. This time rows will be only updated, as all rows are already in a_tab table.

SQL> merge into a_tab
 2 using (select object_id , object_name
 3 from user_objects )
 4 on (object_id = o_id)
 5 when matched then
 6 update set o_name = object_name
 7 when not matched then
 8 insert (o_id, o_name, o_sq)
 9 values (object_id, object_name, a_sq.nextval);
47 rows merged.

Now no new rows where created. But what happen to sequence:

SQL> select a_sq.nextval from dual;
 NEXTVAL
----------
 105

It generated another 47 values, that are useless. It can be proved:

SQL> select count(*), max(o_sq) from a_tab;
 COUNT(*) MAX(O_SQ)
---------- ----------
 47 57

This side effect might have impact on performance. Also you loose lot of numbers, when most of rows are updated. Cheers, Paweł

Monday, November 20, 2006

Links 2006-11-20

Links 2006-11-20:

Cheers, Paweł

Sunday, November 19, 2006

Blogger Beta Backup tool

Finally i found time to prepare solution to backup my blog. I've decided to do that as I had problems to backup my blog using available tools. To archive that I use Blogger Beta Atom feeds, few tables in Oracle, and PL/SQL package to read blog posts, parse them using XML DB features and store in DB tables.

Requirements

Oracle 10g (tested on Oracle XE on Windows and Oracle Enterprise 10.2.0.2 on Windows). Oracle9i should work also, but it's not tested.
Blogs maintained by Beta Blogger and hosted on blogspot.com. You must allow to publish full content of your posts and comments.
Direct connection to Internet

Installation

Download installation package
Create new Oracle schema and grant to it:
1. roles CONNECT and RESOURCE
2. EXECUTE on SYS.UTL_HTTP
run script install.sql

Usage

First You have to register blog you want to backup. Use BLOGSPOT_BACKUP.register_blog function:

begin
 dbms_output.put_line(
 BLOGSPOT_BACKUP.register_blog
 ('Software Engineer Thoughts', 'http://pbarut.blogspot.com/'));
 commit;
end;

To download all your posts with comments andl labels use this PL/SQL block:

begin 
 BLOGSPOT_BACKUP.synchronize_blog(null, false); 
 BLOGSPOT_BACKUP.synchronize_labels(null, null); 
 BLOGSPOT_BACKUP.synchronize_comments(null, null, false); 
 commit; 
end;

For more information take a look into BLOGSPOT_BACKUP specification.

Data storage

Table name	Desription
BLOGS	Blogs registered for backup.
BLOG_POSTS	Blog posts downloaded.
BLOG_LABELS	Labels associated to blog posts.
BLOG_COMMENTS	Comments related to blog posts.
FEED_CACHE	Used speed up tests. In tests phase feeds are read from this table instead of downloading every time.

End notes

I'm publishing this tool, as some of you might find it useful. It should work on all beta blogger blogs, but I do not give ant warranty. In case of any problems fill free to contact me.

Regards, Paweł

Thursday, November 16, 2006

The Palm: The Eighth Wonder of World

You must definitely see The Palm: The Eighth Wonder of World. Incredible...

Paweł

Monday, November 13, 2006

ORA-30625 and XMLType

I will present some differences in handling XMLType in SQL and PL/SQL by Oracle. Test case was done on Oracle 10.2.0.2. Lets define variable and fill it with sample data. It is important to have empty value in tag <empty>.

SQL> var xm varchar2(100);
SQL> exec :xm := '<root><empty></empty></root>';
PL/SQL procedure successfully completed.

Lets try to extract value of empty tag:

SQL> var xmv varchar2(100);
SQL> exec :xmv := XMLType(:xm).extract('//empty/text()').getstringval();
BEGIN :xmv := XMLType(:xm).extract('//empty/text()').getstringval(); END;
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed 
ORA-06512: at line 1

Lets do the same with SQL:

SQL> select XMLType(:xm).extract('//empty/text()').getstringval() from dual;
XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL()
--------------------------------------------------------------------------------

Now we get NULL. You can confirm this with that query:

SQL> select nvl(XMLType(:xm).extract('//empty/text()').getstringval(),
'(NULL)') from dual;
NVL(XMLTYPE(:XM).EXTRACT('//EMPTY/TEXT()').GETSTRINGVAL(),'(NULL)')
--------------------------------------------------------------------------------
(NULL)

So, be careful when you extract data from XMLType in PL/SQL. If you want NULL to be returned for empty tags then you should use function like this:

CREATE OR REPLACE
FUNCTION XML_EXTRACT_NO_EXCEPTION
( p_xml IN XMLTYPE
, p_xpath IN VARCHAR2
, p_namespace IN VARCHAR2 default NULL
) RETURN VARCHAR2 AS
BEGIN
return case when p_xml.extract(p_xpath, p_namespace) is not null
then p_xml.extract(p_xpath, p_namespace).getstringval()
else NULL
end;
END XML_EXTRACT_NO_EXCEPTION;

And then you can use it safe in PL/SQL:

SQL> exec :xmv := XML_EXTRACT_NO_EXCEPTION(XMLType(:xm), '//empt/text()');
PL/SQL procedure successfully completed.
SQL> print :xmv
XMV
--------------------------------------------------------------------------------

Another reason for ORA-30625 can be that you did not provide namespace in execution of extract. More on this in my post
XMLType and Namespace problem.

Paweł

Sunday, November 12, 2006

SQL Developer 1.1 preview

Today I give a try to new SQL Developer 1.1. Installation on Windows 2000 goes smoothly without any problems. Users interface is almost the same as in previous version. One change that I noticed at one is search plug in, that allows user to search Oracle documentation and also Ask Tom site. It integrated well with my Firefox . There are 2 new features that was missing in previous version:

XMLType support - now XML data is displayed as query results, so you can open pop-up and see all details
Display for Date type - you can define in preferences how dates should be displayed

What I'm still missing is possibility to update more then one row in Index Organized Tables (IOT) in single transaction. At the moment when you change one row you must commit transaction. If you do not do that, whole transaction will be forced to be rolled back.
Changes goes in good directions. Might be in few months i will completely stop to use TOAD...
Paweł

Tuesday, November 07, 2006

XMLType and Namespace problem

I've run into some strange problem with XMLType and XML namespaces. I'll try to make a showcase. Lets define variable and simple XML content

SQL> var x varchar2(200);
SQL> exec :x := '<feed
xmlns="http://www.w3.org/2005/Atom"><id>test_id</id></feed>'
PL/SQL procedure successfully completed.

Try to find out what tag is on root:

SQL> select XMLType(:x).getRootElement() from dual;
XMLTYPE(:X).GETROOTELEMENT()
--------------------------------------------------------------------------------
feed

Lets find out the namespace:

SQL> select XMLType(:x).getNamespace() from dual;
XMLTYPE(:X).GETNAMESPACE()
--------------------------------------------------------------------------------

Seems to be no namespace. But there was namespace in XML definition. Lets try extract data:

SQL> select XMLType(:x).extract('/feed').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED').GETSTRINGVAL()
--------------------------------------------------------------------------------

So nothing was extracted. It's not what I was expecting. Ok, lets try to force namespace:

SQL> select XMLType(:x).extract('/feed',
'xmlns="http://www.w3.org/2005/Atom"').getStringVal() from dual;
XMLTYPE(:X).EXTRACT('/FEED','XMLNS="HTTP://WWW.W3.ORG/2005/ATOM"').GETSTRINGVAL(
--------------------------------------------------------------------------------
<feed
xmlns="http://www.w3.org/2005/Atom"> <id>test_id</id>
</feed>

Now works fine.
For me it is little confusing: there is no namespace, but you must provide namespace if you want to extract data.
Paweł

Wednesday, October 25, 2006

Search Oracle Blogs

Google announced release of new tool Google Custom Search Engine. Using this tool You can create our own list of sites you want to search within. There are also many options to customize search result pages, to have search results on your own site. You may also collaborate with other users, to create list of sites on specific topic.
Based o this I’ve created a new search engine that searches only Blogs related to Oracle. You can check if you are already listed. If not you can add your site. You are welcome to take part and propose sites with oracle related blogs. For sure I will include it in this listing.
On top of my page you can find search box. Try it.
If you want this search box on your page just copy following HTML code

<!-- Google CSE Search Box Begins -->
<form id="searchbox_007198821948994293043:dv284dbx17s" action="http://www.google.com/cse">
  <input type="hidden" name="cx" value="007198821948994293043:dv284dbx17s" />
  <input name="q" type="text" size="40" />
  <input type="submit" name="sa" value="Search" />
  <input type="hidden" name="cof" value="FORID:1" />
</form>
<script type="text/javascript" src="http://www.google.com/coop/cse/brand?form=searchbox_007198821948994293043%3Adv284dbx17s"></script>
<!-- Google CSE Search Box Ends -->

Paweł

Oracle 11g new features, part 2

There are two new interesting sources for 11g new features.
Tim Hall post The Next-Generation Self-Managing Database (11g)
And Lewis Cunningham post OOW: Enhancements in Oracle 11g PL/SQL
Also these features or enhancements seems very promising

PL/SQL Native Compilation. There is no need for external C compiler, so it’s easier to make PL/SQL faster.
New Data Type: simple_integer. Another performance booster.
Intra-unit inlining. - this is feature that I was missing for years. Now I can create complex expressions and reuse it easily. I hope it will work also in SQL
Database Replay and SQL Replay - this is very interesting idea.

I'm looking forward to play with these features.

Cheers, Paweł

Tuesday, October 24, 2006

Oracle 11g new features

I have found Laurent post on 11g new features very interesting. Let me comment on some items:

/*+result_cache*/ is very promising. From description it seems that it might be very useful for web applications based on Oracle. But sentence "There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated" suggest that it will be useful only in read only environment. Otherwise cache will be constantly invalidated making this feature useless. Question is if query results will be stored in new memory structure, or it will be in Buffer Cache? Anyway there might be need for huge RAM
Using sequence in PL/SQL without DML. This is long time expected feature
Trigger order. This can be also sometimes useful.

All other features seem to be just improvements or enhancements. I do not see any revolutionary solution in this list.

Paweł

Saturday, October 21, 2006

Binding list variable

Today I was working with XMLType, and as a side effect I found interesting solution to transform list of values contained in single string to rows. You can find some solutions on Tom Kyte blog. But my solution is new approach that uses xmlType and xmlSequence

SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';

PL/SQL procedure successfully completed.

SQL> select items.extract('/l/text()').getStringVal() item
  2  from table(xmlSequence(
  3    extract(XMLType('<all><l>'||
  4      replace(:list,',','</l><l>')||'</l></all>')
  5          ,'/all/l'))) items;

ITEM                                                                            
--------------------------------------------------------------------------------
2                                                                               
4                                                                               
6                                                                               
8                                                                               
10                                                                              
34                                                                              
33                                                                              

7 rows selected.

Simple! Isn’t it?
Cheers, Paweł

Wednesday, October 18, 2006

What tool do you use to make blog backup?

Today a have a question to you: What tool do you use to make backup of your blog?
My blog in hosted on blogspot and is run by beta.blogger.com. I was trying to make backup (mirror) using HTTrack but it run into infinite loop. It was downloading every post many, many, many.... times with little changed name.
Looking forward for your recommendation…
Paweł

Monday, October 16, 2006

A better view? I do not think so.

I have read today Gojko Adzic article A better view. I must say that it made me thinking about my approach to database and front-end applications. I do not share his opinion.
In my approach PL/SQL API is the only possible way to retrieve and manipulate data in Oracle Database. Especialy when it goes for Web Applications.

Logging user activity. Using triggers is fantasting to logging changes to data (Journaling), but usually is useless for logging user activity. User activity should be logged based on his/her actions and it’s parameters not data modifications. You do not want your batch processes to generate so many log.
Performance Monitoring. With well-written API you can get very detailed statistics for time consumed by individual routine. Based on this statistics You can decide where are your bottlenecks and you can improve that.
Simplicity. For easy, standard operations You should prepare generators that automatically generate API procedures for many simple tables.
Cursors. PL/SQL procedures can return REF CURSORS as an output. With dynamic SQL you can return different variations of data depending on input parameters.
API can be used for many Applications. In many big organizations one database is often accessed by different client applications. You do not have to write the same (similar) SQL’s in PHP, java or whatever other tool You want/plan to use.

Hope You understand my point of view.
If you are disagree or agree, please leave a comment.

Cheers, Paweł

Sunday, October 15, 2006

Group Comparison Conditions

Sometimes there is a need to compare one value to many others. In Oracle SQL or PL/SQL for this purpose you can use Group Comparisons. Syntax for this is:
expr { = | != | < | > | <> | <= | >= } {ANY | SOME | ALL} (expr, …, expr) , for example X = ANY (1,2,3) .
You can use subquery instead of expression list. For full syntax go to Oracle Documentation. Lets see how it works. First create sample table:

SQL> CREATE TABLE T1 (N1 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T1 SELECT LEV, LEV
  2  FROM (SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL < 10);
9 rows created.
SQL> COMMIT;
Commit complete.

Then do some queries

SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3

And lets see what will happen if there is NULL in list:

SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         4 4                                                                    
         5 5                                                                    
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6, NULL);
no rows selected

So we can see that ANY is equivalent to SOME. These Grouping conditions are in fact rewritten to simple conditions. ANY is rewritten to many comparisons with logical OR, a < ANY (b,c,d) is rewritten to (a<b OR a<c OR a<d) while ALL is rewritten to AND statement a < ALL(b,c,d) becomes (a<b AND a<c AND a<d).
We can observe that = ANY is equivalent to IN operator.

SQL> SELECT * FROM T1 WHERE N1 = ANY (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         4 4                                                                    
         6 6                                                                    
SQL> SELECT * FROM T1 WHERE N1 IN (4,6, NULL);
        N1 TEXT                                                                 
---------- ----------                                                           
         4 4                                                                    
         6 6

Also observe that != ALL is equivalent to NOT IN operator.

SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         5 5                                                                    
         7 7                                                                    
         8 8                                                                    
         9 9                                                                    
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6);
        N1 TEXT                                                                 
---------- ----------                                                           
         1 1                                                                    
         2 2                                                                    
         3 3                                                                    
         5 5                                                                    
         7 7                                                                    
         8 8                                                                    
         9 9                                                                    
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6, NULL);
no rows selected
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6, NULL);
no rows selected

Take a look at trick with joining many tables based on the same key. Lets create more tables

SQL> CREATE TABLE T2 (N2 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T2 SELECT * FROM T1;
9 rows created.
SQL> CREATE TABLE T3 (N3 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T3 SELECT * FROM T1;
9 rows created.
SQL> COMMIT;
Commit complete.

And simple join:

SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
        N1         N2         N3                                                
---------- ---------- ----------                                                
         1          1          1                                                
         2          2          2                                                
         3          3          3                                                
         4          4          4                                                
         5          5          5                                                
         6          6          6                                                
         7          7          7                                                
         8          8          8                                                
         9          9          9                                                
9 rows selected.

Is it really evaluated in proper way?

SQL> set autotrace traceonly;
SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
9 rows selected.
Execution Plan
----------------------------------------------
Plan hash value: 3348756600 
---------------------------------------------- ...
| Id  | Operation              | Name        | ...
---------------------------------------------- ...                                   
|   0 | SELECT STATEMENT       |             | ...
|   1 |  NESTED LOOPS          |             | ...
|   2 |   NESTED LOOPS         |             | ...
|   3 |    INDEX FAST FULL SCAN| SYS_C004041 | ...
|*  4 |    INDEX UNIQUE SCAN   | SYS_C004042 | ...
|*  5 |   INDEX UNIQUE SCAN    | SYS_C004043 | ...
---------------------------------------------- ...                                         
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   4 - access("N1"="N2") 
   5 - access("N1"="N3")

So we can observe that it is really rewritten and oracle uses indexes in optimal way.
Hope you find it useful

Paweł

GUI for FireBird DB

Friend of mine, that is still working on xBase (Clipper), asked me to recommend him a good GUI tool for SQL database. At first I didn't know what he exactly wanted. In fact he wanted a tool to retrive and manipulate data in FireBird DB. After trying few tools I recommended IB_SQL. It's realy light weighted and intuitive.

It was my first experience with FireBird. I must say that for me it seems insecure. Whole database is stored in one file, and if you have this file, you can easily open it without need for any password.

Paweł

Friday, October 06, 2006

RMAN connection to Catalog

Today my nightly backups were not taken. I had an OS script that started backup using this command:

rman target=sysop/password@targetdb catalog=rman/password@catdb 
      log=rman.log append @rman.txt

The reason was that network was down and there was no connection to catdb. It leaded to error and RMAN did not start at all. So I’ve changed my command line to:

rman target=sysop/password@targetdb 
      log=rman.log append @rman.txt

And added a line in rman.txt

connect catalog rman/password@catdb;

Next time there will be no connection to catdb backup will be done anyway. It will be not registered in catalog only. Catalog will be synchronized on next backup procedure or next time you connect to target and catalog an issue any command that operate on catalog.

Cheers, Paweł

Thursday, October 05, 2006

More on Flashback

If you are instrested in more materials on Oracle Flashback you should take a look at Tom Kyte presentation TomKyte_FlashBackQ_Mar-05.zip found on Calgary Oracle Users Group.
Polish speeking readers can have a look at my article PLOUG'tki nr 31 (IX 2004) - Podróż w czasie, czyli Oracle Flashback that I wrote for Polish Oracle Users Group.

Cheers, Paweł

UNDO_SQL in FLASHBACK_TRANSACTION_QUERY

When You read my post on Flashback and Materialized View You might think about other workaround. In FLASHBACK_TRANSACTION_QUERY View there is column with SQL Statement to Undo operation on table. Lets do simple test:

SQL> insert into a_fb values (1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from a_fb;
1 row deleted.
SQL> commit;

And now see whats in FLASHBACK_TRANSACTION_QUERY.

SQL> select commit_scn, operation, row_id, undo_sql
  2  from FLASHBACK_TRANSACTION_QUERY
  3  where table_name = 'A_FB'
  4  order by commit_scn;
COMMIT_SCN OPERATION                        ROW_ID                              
---------- -------------------------------- -------------------                 
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
    641488 INSERT                           AAAM2dAAEAAAAG9AAA                  
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';                  
                                                                                
    641571 DELETE                           AAAM2dAAEAAAAG9AAA                  
insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1');

To run above query You need SELECT ANY TRANSACTION privilege
So now instead of issuing FLASHBACK TABLE A_FB to SCN 641400 You may run statements from UNDO_SQL:

insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1');
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';

But it will not work! Because when you insert new row usually it will get new row id

SQL> select rowid, id from a_fb;

ROWID                      ID                                                   
------------------ ----------                                                   
AAAM2dAAEAAAAG9AAB          1

So when issuing Delete statement you must map old rowid to that new one. When you do Flashback on Table oracle manages it internally. But when you use UNDO_SQL you must take care for all details. So be careful with that…

Cheers, Paweł

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.

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ł

Sunday, September 17, 2006

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ł

Thursday, September 14, 2006

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ł

Wednesday, September 13, 2006

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ł

Tuesday, September 12, 2006

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ł

Sunday, September 10, 2006

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:

issue command to set restore point:

CREATE RESTORE POINT before_patch GUARANTEE FLASHBACK DATABASE;

apply application patch
test system after patch
if ok, release restore point:
```
DROP RESTORE POINT before_patch;
```

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ł

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ł

Sunday, September 03, 2006

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:

Expect the unexpected and deal with it
Scope functions and variables appropriately
Keep your functions and subroutines simple
Layout code to increase legibility
Name your variables to aid readability
Code for human consumption
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ł