Do not trust Google Maps

Ok, Might be title is too strong, but you should not trust Google Maps when searching for streets in Krosno, Poland (city where I was born). Here is sample, try to search for "Lwowska, 38-400 Krosno, Poland" and you get:
Search is working correctly, but names on street do not match reality. Instead of "Lwowska" street is "Jana Lenarta". Another example is "Platynowa" instead of "Podkarpacka". It seems that all street names are messed up on map. Hope they fix it soon.

Cheers, Paweł

Rant: Great fun with Wii

I'm not a computer game player, but Nintendo Wii really impressed me. I was with a visit to my friend and he showed me his new gadget. We started to play for a while, and time passed by. It's really addictive. The best thing is that you do not seat at computer and use joystick or keyboard, but you stand up and move whole body to play. It really gives pleasure, and we have good time. If you do not know Wii yet check this Wii commercial.

Have fun
Paweł

More on PHP with DRCP support

I was asked to compare DRCP to SHARED server connections. I've also promised to give some more statistics and comparisons for DRCP and standard connectivity.
First few results to compare to SHARED server as asked in comments to my previous post:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect DEDICATED server						Not tested
2	oci_pconnect DEDICATED server	1200	84	63	116	4077
3	oci_connect SHARED server	1200	280	264	150	1290
4	oci_pconnect SHARED server	1200	82	78	93	3965
5	oci_connect with DRCP	1200	104	94	45	3265
6	oci_pconnect with DRCP	1200	78	78	23	4396

In this case SHARED server is configured to use 4 dispatchers and max process = 1000.


Cheers, Paweł

Testing new OCI8 (1.3.0 beta) for PHP with DRCP Support

I've seen Christopher Jones announcement on availability of beta OCI8 extension for PHP with support for DRCP. I've decided to give it a try. On my test machine running OEL5 I've installed latest PHP 5.4.2 and then replaced OCI8 sources with new OCI8 1.3.0 beta release. I've configured PHP:
./configure --with-oci8=$ORACLE_HOME --with-apxs2=/usr/sbin/apxs --with-config-file-path=/etc/httpd/conf --enable-sigchild --without-sqlite --without-mysql
compiled it and installed. Everything went fine, and on sample page I've displayed phpinfo():

I've changed php.ini to enable Database Resident Connection Pooling:

oci8.connection_class="TESTPOOL"

and tested that it is used in PHP.
Then I've decided to test and compare performance of different methods of connection to Oracle from PHP. I've created simple PHP page, that connects to Oracle, execute simple query that returns one row, and inserts one row to another table. I've prepared test script for Apache JMeter against this page.
Test specification:

• Run on single processor machine
• Oracle and Apache+PHP run in VMware
• DRCP setup to have max 4 processes (3 processes effectively used, 4^th for authentication only)
• 6 simultaneous users opening the same web page 200 times
• 4 versions of PHP script were used:
  

1. oci_connect used to connect to Oracle without using DRCP (Database Resident Connection Pooling)– in this case new connection is created for every request from web browser. Connection is then closed at the end of script
   
2. oci_pconnect used to connect to Oracle without using DRCP – in this case new connection is created for every apache process that is responding to request from web browser. Connection is then closed at the end of script
   
3. oci_connect used to connect to Oracle with using DRCP (Database Resident Connection Pooling)– in this case for every request connection is obtained from pool and given back to pool at the end of script, seems that in this case only process is reused (PURITY=NEW), while session is not (see my observations when using SQL*Plus )
   
4. oci_pconnect used to connect to Oracle with using DRCP (Database Resident Connection Pooling)– in this case for every request connection is obtained from pool and given back to pool at the end of script. But in this case full potential of DRCP is used (PURITY=SELF)

Here are the results:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect without DRCP	1200	989	875	828	359
2	oci_pconnect without DRCP	1200	123	94	187	2505
3	oci_connect with DRCP	1200	117	94	115	2907
4	oci_pconnect with DRCP	1200	82	78	30	4060

I must say that I'm positively impressed by performance achieved by version 3 where oci_pconnect with DRCP was used. I expected that performance for version 2,3,4 will be very similar. But I've observed 60% increase in Throughput and 33% shorter response times when comparing version 2 and 4 (oci_pconnect with and without DRCP). I've changed one setting for my test in JMeter: I've turned on Keep-Alive for http transfer and here are the results:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect without DRCP						Not tested
2	oci_pconnect without DRCP	1200	84	63	116	4077
3	oci_connect with DRCP	1200	104	94	45	3265
4	oci_pconnect with DRCP	1200	78	78	23	4396

In this case I've observed less differences on Throughput and Response times between version 2 and 4. This is because with Keep-Alive causes that the same apache processes are used for requests coming from the same web browser. As connection is kept open, reduces time to establish connections between clients (web browsers) to apache server. Thats why we can see higher throughput. Additionally for oci_pconnect without DRCP less amount of sessions are created. Without Keep-Alive apache opened much more sessions, as it had to create more processes and every process had to connect to Oracle. Lets summarize my observations:

1. oci_pconnect with DRCP works stable in both cases (with and without Keep-Alive) giving maximun throughput and very low standard deviation,
2. oci_pconnect with DRCP has high standard deviation what means that there are lot of request with response time much higher then average,
3. In both cases connecting to Oracle with use of DRCP performed better than without.

Next time I'll try to go into extremes to see how DRCP behaves.
Cheers, Paweł

I've got spammed (in comments)

Due to quite massive spam I'm receiving today in comments I had to turn on word verification for comment posting. Hope it will protect me from spam for future. And sorry for inconvenience when commenting,

Cheers, Paweł

Using Named Parameters within SQL

One of the Oracle 11g new feature that I like is ability to use Named Parameter syntax in functions calls inside SQL. For example it is possible to run such query:

select DBMS_METADATA.get_xml(
          object_type     => object_type,
          name            => object_name) 
     from user_objects
where object_type in ('TABLE', 'VIEW');

It was not possible in previous Oracle versions (before 11g). It was only possible to specify parameters by position:

select DBMS_METADATA.get_xml(object_type, object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

This is great feature as now SQL is more compatible with PL/SQL. And Named Parameters syntax is my favorite method of calling functions, as it gives me direct knowledge what value is assigned to parameters. Specifying parameters by position requires to remember sequence of parameters in function definition. Additionally named parameters syntax allows programmer to skip any of parameter that has default value, or to change order of parameters in function call. It is also possible to mix those 2 notations in single statement:

select DBMS_METADATA.get_xml(object_type, name => object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

Hope you like it

Cheers, Paweł

Comments in SQL*Plus

This Warning from Eddie Awad remind me of problem with comments I've had some time ago. I've prepared an script that was intended to run in sqlplus. Then I've extensively tested it, and as a last step, I've commented it. And ... did not test again. When this script was run on production caused errors. The problem was in the way that sqlplus interpret comments.
Example:

SQL> select * from dual;

D
-
X

runs fine, while this:

SQL> select * from dual; -- some comments

stops at second line waiting for input:

SQL> select * from dual; -- some comment
  2 

It was really unpleasant experience.

Cheers, Paweł

More on Oracle 11g DRCP

Due to comments from Sreekumar Seshadri and Krishna Mohan Itikarlapalli from Oracle, I've corrected my previous post on DRCP Functionality. I must own up that I've omitted reading chapter on DRCP in OCI Programmers Guide. Based on name "Database Resident Connection Pooling" I implied that it's only required to configure it at Database Server Side. In Fact it is partially true, because if you want to take advantage of Connection pooling it's needed to change client code too. There are 3 levels of benefits of DRCP and OCI enhancements in Oracle 11g:

1. Configuring DRCP at DB Side without any change in Application. In this case only processes are pooled, and every time new connection is made, new session is created. This is what I've observed in previous post when I was using SQL*plus.
2. and 3. need changes in Application in code that connect to DB. I will just quote OCI Programmers Guide:
   

   • Applications that use OCISessionGet() outside of an OCISessionPool, and specify the connection class and set purity=SELF will be able to reuse both DRCP pooled server processes and sessions. However, following an OCISessionRelease(), OCI will terminate the connection to the connection broker. On the next OCISessionGet() call, the application will reconnect to the broker. Once it reconnects, the DRCP will assign a pooled server (and session) belonging to the connection class specified. Reconnecting, however, incurs the cost of connection establishment and re-authentication. Such applications achieve better sharing of DRCP resources (processes and sessions) but do not get the benefit of caching connections to the connection broker.

   • Applications that use OCISessionPool APIs and specify the connection class and set purity=SELF make full use of the Database Resident Connection Pool functionality through reuse of both the pooled server process and the associated session and also getting the benefit of cached connections to the connection broker. Cached connections do not incur the cost of re-authentication on OCISessionGet().

So to really test DRCP I need to write some code to test it.

Cheers, Paweł

Oracle 11g DRCP: Functionality Test

This post is continuation of my series of post about Database Resident Connection Pooling.
Let start with session and processes. My test shows that Pooled Servers are shown in v$session view only when there is user connected, that means session is busy. For inactive servers only process in v$process view is reported. To identify this processes we can run query like that:

select * from v$process
where program like '%(L0%)';

If our Connection pooling is started, at least one process will be reported. This is so called Connection Broker process that is authenticating connections and assigning them to another server for processing. According to my tests Connection Broker this process usually have highest number in brackets (ex. L003 if you set up max number of processes to 4). To see busy servers run this query:

select * from v$session
where program like '%(L0%)';

Note that Connection Broker is never reported in v$session view.

In next test I've tested if global variables in packages are stored between connections. My test confirms that packages are always initialized after new connection is taken from pool. That's very good, and is what I was expecting. This is advantage over traditional connection pooling at client side like in Java, where environment is totally shared between consecutive use of the same connection. (This is true when only process if reused, when PURITY=NEW)

My next test was concerning usage of context. I've created sample CONTEXT, initialized it in session and reconnected. I've ensured that session was assigned to the same process (pooled session) and read context. My test also confirmed that context is reinitialized between connections. In traditional connection pooling it will not happen. Also when using PHP and persistent connection you have to take care of clearing global variables and context after acquiring session from pool (or before giving it back to pool). Database Resident Connection Pooling will make programmers life easier, at least on that field. (This is true when only process if reused, when PURITY=NEW)

My last test for today was performance test. Test was run on rather poor desktop machine with single processor.

Testcase	Normal Connection	Pooled Connection	%Improvement
Connect and Disconnect 1000 times	226 sec	154 sec	31%
Connect, Insert one row, Disconnect 1000 times	309 sec	178 sec	42%

This results are very promising. First we see 30% improvement of time to connect to DB. What is more important we can see 40% less time on repeating the same simple statement after establishing each connection. The only explanation is that Pooled connection can reuse already parsed statement. That is good news if we think about scalability.

You may be also interested in Oracle Official White-paper on DRCP.

At the end I would like to thank Christopher Jones as he was involved in DRCP development. I've mailed with him few times last year about problems with PHP-Oracle connectivity and lack of connection pooling.

Cheers, Paweł

Blog roll reorganization

Today i've reorganized my blogroll. Until now I was reading news using OraNA.info maintained by Eddie Awad. But there is too many post that are out of my interest. So i've decided to subscribe to feeds from some of them. Here is full list: Adrian Bilington, Alex Gorbachev, Alexander Kjerulf - CHO, Alexander Kornbrust, Alison Holloway, AMIS Blog, Blogging about Oracle, Carl Backstrom, Chen Shapira, Christopher Jones, Coskan Gundogar, Creating Passionate Users, Dimitri Gielis, Dominic Brooks, Don Seiler, Eddie Awad, Eric S. Emrick, Escape from Cubicle Nation, Gary Myers, H.Tonguç Yilmaz, Howard Rogers - Dizwell, Jeff Moss, Jeremy Schneider, Jonathan Lewis, Jorrit Nijssen, Kevin Closson, Laurent Schneider, Lewis Cunningham, Lutz Hartmann, Marco Gralike, Niall Litchfield, OraQA, Patch, Patrick Wolf, Penelope Trunk, Pythian Group, Robert Baillie, Sayed Jaffar Hussain, Steven Feuerstein, Steven Karam, Tanel Poder, Tim Hall, Tom Kyte, Venkatakrishnan J, YAS.

Cheers, Paweł

Operating on XMLType containing & (Ampersands)

I was reading today Lewis post on How to Deal With Oracle XML and Ampersands ("&") in XML Documents. Personally I prefer different way. I use XMLElement function to generate XML documents. In my opinion this is more elegant way. The sample from Lewis post will look like that:

set define off
declare
  v_xml xmltype;
begin
  select xmlelement("ROWSET", 
           xmlelement("ROW", 
             xmlelement("COLA", 'lewis&me')))
    into v_xml 
    from dual;
end;
/

Disadvantage of this method is that you have to use SQL. But in most cases it's used while retrieving data from database, so it should not be a problem. Some time ago I was proposing to read document Mastering XML Generation in Oracle Database 10g Release 2. It is really good source of knowledge on XML manipulation inside Oracle database.
If you want to extract correctly ampersands and other special characters like < and > from XML document I suggest to read my post on differences between extract and extractvalue functions. If you want to manually convert &amp; to & you can use function dbms_xmlgen.convert with second parameter set to 1:

SQL> select dbms_xmlgen.convert('me&lewis', 1) from dual;

me&lewis

Cheers, Paweł

Alternative Syntax for Insert

Alternative Syntax for Insert

Let me show my idea of SQL improvement, that I would like to see. Traditional insert works like that:

insert into table_a
    (column_1, column_2, column_3, column_4)
  values
    (value_1, value_2, value_3, value_4);

What I would like to have is something like that:

insert into table_a
   set column_1 = value_1
      ,column_2 = value_2
      ,column_3 = value_3
      ,column_4 = value_4;

With this syntax code would be easier to maintain. Especially, when you have to add or delete, or find error in insert to table with many columns. Biggest advantage of this would be close relationship between column name and value that will be set for that column. In INSERT that we know it column name and value are quite far from each other, so even for table with 10 columns it might be hard to find corresponding entries.
In PL/SQL there is an workaround to get similar solution:

declare
    v_row table_a%rowtype;
begin
    v_row.column_1 := value_1;
    v_row.column_2 := value_2;
    v_row.column_3 := value_3;
    v_row.column_4 := value_4;
    insert into table_a values v_row;
end;

But it's PL/SQL not pure SQL. I really would like to have this syntax in next Oracle release...

Cheers, Paweł

One Year Blogging

It's one year since I started blogging. It's time for some statistics and summary. During last year:

1. I've published 69 posts - it's 1 post every 6 days (on average)
2. 51 post are about Oracle (74%)
3. Over 29200 page views
4. 84% of traffic comes from Google
5. 35% of traffic is from US, Next on list are: UK (8%), India (7%), Germany (5%) and Poland (4%)

Top 10 of most often visited pages:

1. Oracle 11g new features
2. Restore CONTROLFILE and/or SPFILE from autobackup
3. ORA-03297 on empty datafile
4. ORA-30625 and XMLType
5. Visual SourceSafe - Invalid DOS Path
6. Empty String IS NOT NULL?
7. Oracle XMLType: exctractvalue vs. extract
8. Applying Oracle 10.2.0.3 patch
9. XMLType and Namespace problem
10. UTL_MATCH - String Similarity in Oracle

Most commented posts:

1. Visual SourceSafe - Invalid DOS Path
2. Yet another tokenizer in Oracle
3. Log Buffer #48
4. What tool do you use to make blog backup?
5. Rolling Back the DDL

And finally post that I think are valuable but not so often seen or commented:

1. Moving domain index to another tablespace
2. Monitor progress of long running processes
3. Extracting Data to Excel using only SQL*Plus
4. Rows to single string or aggregating strings
5. Flashback Table and Materialized View – not working together
6. Group Comparison Conditions
7. Search Oracle Blogs - I was before Eddie with this idea; but Eddie has more readers...
8. Binding list variable
9. Blogger Beta Backup tool
10. Moving/Adding Control file when using SPFile
11. Table that cannot be deleted

I hope that in next months I will find time and good subjects to blog about.

Cheers, Paweł

Oracle 11g DRCP: Database Resident Connection Pooling - second attempt

In my last post I wrote about unsuccessful attempt to check functionality of DRCP: Database Resident Connection Pooling. Now it's time for success story :) The problem with previous situation was, that Oracle was not fully functional. And that was because I manually set, that i have all proper versions of linux packages, what was not true.
Now I've installed Oracle Enterprise Linux v 5.0 and Oracle 11g over it, with some help of Tim instructions.
I've added entry in TNSNAMES.ORA on linux box:

DB11GPOOL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect to pool

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 01:44:53 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server

So I've started connection pooling (as SYS at Oracle):

exec DBMS_CONNECTION_POOL.START_POOL();

And tried again:

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 02:02:37 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select 1 from dual;
         1
----------
         1

This time successful login.
Now I've left this session untouched for some time (more then 300 second - default time-out for pool)

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2821
Session ID: 125 Serial number: 21

As you can see, session was timed-out by pool, and disconnected.
Ok, lets change parameters for pool to observer behaviour, when max number of session is reached.
Execute it as SYS:

begin
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','1');
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE','4');
end;
/

After I set it, I'was able to have 3 (not 4) simultaneous sessions using Connection pool. Whenever I start 4-th or 5-th session it is waiting for connection. You do not get any error, session just hung up. When one of active session is released (closed or timed-out) then one of waiting sessions is being connected. When I was looking for explanation, I found out one that in view V$CPOOL_STATS:

• NUM_OPEN_SERVERS=4 - Total number of busy and free servers in the pool (including authentication servers)
  
• NUM_BUSY_SERVERS=3 - Total number of busy servers in the pool (not including authentication servers)
  
• NUM_AUTH_SERVERS=1 - Number of authentication servers in the pool
  

So, we have to have at least 1 authentication server in our pool. We should be aware of this when we setup small number of MAXSIZE of pool.
When I first read about database connection pooling I was thinking that when you start using all connections in pool, than next attempt to connect will open new normal session. But it will not be good. Mechanism of queues is better, because client will not overload database server. Might be it's not good for clients, as they might wait long time. On the other hand in well written application sessions should be short and closed immediately when no more needed in request.
More tests of DRCP in next posts...
Cheers, Paweł

DRCP: Database Resident Connection Pooling

Finally I've found time and managed to install Oracle 11g. I did it on Fedora Core 5 run in VMWare Server on Windows 2K Pro.
My first attempt was to install it on Fedora Core 6 but I didn't succeed, and even I managed to corrupt VM totally. My second try was to create new VM with FC5. I've applied all available updates, and tried to install Oracle 11g. Oracle gave warning that some packages do not match requirements, but after manually marking check-box it proceeded and installed successfully. And seems to be fully functional. Do not try this for production DB :)
After that I've spend lot of time trying to connect to this DB from my Windows box. I was reconfiguring VM Network setting, FC Network setting many times without any sign of progress. Finally I found out that it can be firewall that is causing problem. It was really silly that I spend so much time to solve so simple problem...
Any way my Oracle 11g is up and running, so I started to test one of th features that are in my interest: DRCP: Database Resident Connection Pooling.
I've enables DRCP:

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
PL/SQL procedure successfully completed.

And created sample user for tests:

SQL> create user pooltest identified by pool ;
User created.
SQL> grant connect to pooltest;
Grant succeeded.

I've added entry in TNSNAMES.ORA on Windows box:

DB11GPOOL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect using POOLED server:

c:\Temp>sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 27 22:30:08 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-56606: DRCP: Client version doesnot support the feature

So, DRCP needs proper client version to work. It's not a good news, as I thought that this will not require any changes at client side :(
I have to make my test on single box, or install Oracle on one more box (VM). Ok, first try from the same box where DB installed:

[oracle@vm111 admin]$ sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 27 22:41:51 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Aborted

Seems that I have to dig little more more to make it working.

Cheers, Paweł

UPDATE: Success story on DRCP in next post.

It's feature not a bug ;)

Friend of mine send me an link to funny picture:


Cheers, Paweł

Oracle 11g is released - my links

As you probably know, Oracle 11g is released - now only for linux x86. I've already downloaded it, but had no time to install and play with it. Anyway I've decided to make summary of bloggers post on 11g for my future reference:

• Oracle 11g Documentation
• Oracle 11g new parameters by Howard Rogers "dizwell"
• Oracle 11g new parameters part 2 by dizwell
• Oracle 11g new parameters part 3 by dizwell
• Bind variable peeking in 11g by Yas
• Invisible Indexes by Yas
• Security for XMLDB (DBMS_NETWORK_ACL_ADMIN) by Marco Gralike
• 11g Native Web Services by Paul Gallagher (Tardate)
• FOLLOWS Clause in Create Trigger Statement by Jurgen Kemmelings
• New PL/SQL Compiler Warning in Oracle 11g by Jurgen Kemmelings
  
• Oracle 10.2 versus 11.1 by Julian Dyke
• Oracle11g: Where’s My Alert Log? by Kevin Closson
• Oracle Database 11g Release 1 (11.1) Installation On Enterprise Linux 4.5 and 5.0 by Tim Hall
  
• Oracle 11g Release 1 RAC On Linux Using NFS by Tim Hall
  

There is much more posts about Oracle 11g but those listed above got by attention.

Cheers, Paweł

Pictures on Google map

Few days ago I wrote about idea to integrate Google Maps and Picasa pictures in the way that you can see pictures on map. Today I've discovered that it is already done in very similar way. When you go to maps.google.com there is an new tab My Maps. Then you can check "Photos from Panoramio" and you can scroll and pane map to see pictures from different places. Really cool feature. I'm looking forward for same functionality with Google Picasa. BTW, Panoramio was (will be) bought by Google in May 2007. I wonder if they integrate it with Picasa ...
Have Fun with pictures




Cheers, Paweł

Oracle 11g Announced: Features Preview

Oracle 11g was today announced. As an engineer I'm interested in new features of 11g release. I was already writing about this here and here. But this were just my thoughts based on others relations from Oracle Open World 2006.
Now official list of Oracle 11g new features is published. Here is page that contains more technical information on Oracle 11g.
Based on new features list I choose some that are very useful in project that I'm work now on, and I see them as important:

1. Binary XML storage and XML path indexing for schema-less XML documents
   
2. Result caches: improves speed of repeated execution of queries and function calls that access read-only or read-mostly data.
   
3. Database Resident Connection Pooling: enables faster connections to the database for application, that do not provide connection pooling (ex. PHP).
   
4. Faster triggers, including more-efficient invocations of per-row triggers
5. Faster simple SQL operations
6. SecureFiles: ti's new solution for storing large objects (LOBs) and datatypes such as XML
7. Automatic compilation for PL/SQL and Java in the database: new “native” compilation of PL/SQL that do not need C compiler.
   

New features that look interesting, and I want to play with them:

1. Semantic Technologies: native support for Resource Description Framework (RDF) and Web Ontology Language (OWL) standards,
2. Oracle Spatial enhancements: Richer, more-interactive map application development
3. New partitioning capabilities:
   - Partitioning by parent/child references,
   - Partitioning by virtual columns,
   - More composite partitioning options, including range/range, list/range, list/hash, and list/list,
   - Interval partitioning, which allows you to automatically create new partitions based on intervals, such as every month or every day,
   
4. Oracle Flashback Data Archive: enables fast query access to old versions of the data.
5. Expanded support for standards, such as XML Query (XQuery) 1.0 and service-oriented architecture (SOA)

I'm waiting for Oracle 11g to be available for download...

Cheers, Paweł

Extracting Data to Excel using only SQL*Plus

Lately I had to quickly extract some data from Oracle DB to Excel. There are a lot of tools that can do that, ex. Toad or SQL Developer, but I had only access to SQL*Plus. Additionally I was on Windows machine and database was in UTF8. And extracted data was also using many different languages.
Here are steps I used to archive my goal:

1. start command line (CMD)
2. set NLS_LANG variable
   

   set NLS_LANG=.UTF8

3. start sqlplus and log to database
   

   sqlplus user/pwd@db

4. set long line size and avoid paging
   

   SQL> set linesize 4000
   SQL> set pagesize 0

5. set markup html in sqlplus
   

   SQL> set markup html on spool on

   
   It is important to use spool on clause, as it adds html header to spool file when spooling is started
6. start spooling
   

   SQL> spool a.html

7. run your query
   
8. turn off spooling
   

   SQL> spool off

9. open file in Excel

I've tested this procedure on Oracle 10g R2 (10.2.0.1 and 10.2.0.3) and Excel 2003

Cheers, Paweł

After Vacations: Tagging Photos with Geo Location

Welcome back after my vacations. This time nothing about Oracle, but about some idea that was in my mind for few years and now is life: Google Picasa has new functionality: putting pictures on maps. I'm using Picasa to share pictures with friends and family for more than year now. And often I was asked: where did you take this picture? Now I can tag pictures with Geo coordinates and then are shown on map. Take a look at my public galleries on picasa, especially latest one with some pictures from my vacations.
There are still some features that I would like to see;When playing with google maps I would like to have option to show pictures from specific place. Pictures from public galleries could be presented. There might be an copyright issue, but it could be solved easily: users will have option to allow (or not) to publish pictures this way.
And I must buy Camera with build in GPS receiver to simplify mapping pictures...


Cheers, Paweł

Dynamically set name of spool file in SQL*Plus

Small tip for today. Often there is a need to set name of spool file based on current time and might by also database you are connected to. It's often needed when script is run as scheduled task and there is a need to generate files with different names. So here is an solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

If there is a need to have also name of database included into filename then it can be read from global_name view:

define logname=date
column clogname new_value logname
select 'prefix_'||substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||
 to_char(sysdate, 'yyyymmdd') clogname
from (select global_name, instr(global_name, '.') dot from global_name);
spool '&logname'

Hope you find it useful.
I would also like to share an photo I made in Rome in February this year

To see more pictures you are welcome to follow Rome 1, Rome 2, Rome 3

Cheers, Paweł

Log Buffer #48

Welcome to 48 edition of Log Buffer. While I read Log Buffer regularly for many months already it's first time I do review of databases blogosphere. Thanks to Dave Edwards from Pythian for inviting me.

This edition will be mostly related to development tools, techniques and environments for database applications. Let start with Inside Oracle APEX where Patrick Wolf presents internals of APEX Repository. In turn Dimitri Gielis is discussing how to reuse Oracle APEX components effectively. If you still do not know what APEX is, you can begin with Oracle Application Express—What’s it All About? by Peter Lorenzen.

Jonathan Bruce at his Weblog is reviewing and recommending DataDirect Connect for ADO.NET Oracle provider for Visual Studio.
Anton Scheffer from AMIS is solving Sudoku with single SQL statement using MODEL Clause; really impressive method, I must find time to experiment with MODEL clause as it seems to be very powerful.

Linear Algebra is not commonly known feature of Oracle database. Marcos M. Campos on Oracle Data Mining and Analytics is presenting how it can be used for Principal Components Analysis and Visualization. Unfortunately complexity of examples rather will not help with wider usage of UTL_NLA package.

Jonathan Lewis on his Oracle Scratchpad presents differences in new (Oracle 10g) and old sorting algorithm. Difference is visible only for those who were using side effect of sorting by ROWID in previous oracle releases.

Jorrit Nijssen alias Jornica issued an warning on using after statement trigger and returning clause together as there is an side effect.

For many of Oracle DBAs it might be weird idea: Kevin Closson is proposing to install Oracle over NFS. He states that it's cheaper, simpler and will be even better with upcoming Oracle 11g. By the way Oracle 11g will be launched on 11th of July according to Eddie Awad. This announcement made big rumour in Oracle blogosphere and Lutz Hartmann is now documenting new feature of collecting statistics in 11g.

Laurent Schneider is discussing Best Practices for Aliasing columns in SQL queries. Dominic Delmolino on Oracle Musigns suggest another method: uniquely name columns. Also Steven Feuerstein on Toad World shows good practice: "Don't put COMMIT; in you code!".

Kevin Closson and Doug Burns discuses what is the role of Oracle, OTN, Technorati and readers for bloggers community.

Jeff of his Jeff' SQL Server Weblog that database design should not be made due to desired output. "We often see bad designs submitted at SQLTeam.com with the justification of that design being the client wanted it that way! ... you need to demonstrate to them that even when storing the data properly, we can *still* create a View which returns those exact results, making them happy".

Data Quality: Where to Validate? Beth on Confessions of a database geek is reviewing levels where validation should occur also giving some recommendations.

Roland Bouman shows What MySQL can do to enter the off-line Web. In my opinion the idea of using Web applications offline will be very hot in nearest future.
Peter Zaitsev is discussing an issue with degraded performance after upgrading MySQL 4 to MySQL 5. He also presents some workaround for that problem.
Paul McCullagh on PrimeBase XT shows how to manipulate BLOBs in MySQL using BLOB Streaming Engine.
Ronald Bradford on Technical Notes and Articles of Interest reviews Clustering solution for MySQL database.
Kevin Burton is predicting end of RAIDs . Instead he proposes to use many instances of MySQL with replication mechanism.
Xaprb announced release of MySQL Archiver 0.9.1 tool to archive content of critical OLTP tables.

JonC is answering most common Security Questions Applied to MySQL and pointing that biggest challenge is to deal with human errors. Alex Kornbrust is presenting THC Orakel new tool to Sniffer Oracle Passwords. He also points few errors in white-paper that is accompanying the tool.

Mark Rittman is on vacations leaving us with lot of BI Articles and Links to read.

Steve Jones on SQL Musings talks over High Availability of SQL Server. Euan Garden on his blog announces interesting Web Casts about new features of SQL Server 2008. While Jamie Thomson show how Group by Grouping Sets will work in Katmai: SQL Sever 2008.

Josh Berkus is reporting on JPUG 2007 PostgreSQL Conference and his keynote on "PostgreSQL Today and Tomorrow". "This was the most challenging conference for me so far since the entire conference was in Japanese, a language of which I have no comprehension."

At the and some news from my country. Jakub Pawlowski is summarizing XV PLOUG (Polish Oracle Users Group) Seminary - "Oracle Application Server 10g R3: applications, productivity, security, reliability."
That's all for today. Wait for next Log Buffer on June 15th presented by Coskan Gundogar.

Paweł

Rows to single string or aggregating strings

In oracle there is no build-in group function that will concatenate strings, just like max summarizes all numbers. But there is quite easy way to do that in pure SQL. Assume we want to have get as an query result list of tables and column. But we want all columns to be concatenated and comma separated. Result should look

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Lets start with hierarchical query to build "tree" for each table. The only one branch of this tree will start with first column and end with last one. SYS_CONNECT_BY_PATH gives as option to track that branch:

SQL> select table_name, 
  2        SYS_CONNECT_BY_PATH(column_name,  ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  ;

TABLE_NAME	COLUMN_LIST
BLOGS	, BGH_ID
BLOGS	, BGH_ID, BGH_NAME
BLOGS	, BGH_ID, BGH_NAME, BGH_FEED_URL
...	...

Then I just take max path and truncating leading comma gives me expected result:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Function Max works becouse if one string (ex.: "abc") is an prefix of the second one (ex: "abc123") than this second one is threated as bigger. I must warn that it might be not true for all NLS settings.

But what if we want columns to be ordered alphabetically? Than we have to use Row_Number function to calculate column position in table and make same changes to SQL:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from (select table_name,  column_name,  ROW_NUMBER() OVER (partition by table_name ORDER BY column_name) AS curr  from user_tab_columns)
  4  start with curr = 1
  5  connect by table_name = prior table_name
  6             and curr = prior curr +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_AUTHOR, BLC_AUTHOR_URI, BLC_BLG_ID, BLC_CONTENT, BLC_IDENTIFIER, BLC_PUBLISHED, BLC_PUBLISHED_TEXT, BLC_UPDATED, BLC_UPDATED_TEXT, BLC_URL
BLOG_POSTS	BLG_BGH_ID, BLG_CONTENT, BLG_ENTRY, BLG_ID, BLG_IDENTIFIER, BLG_PUBLISHED, BLG_PUBLISHED_TEXT, BLG_TITLE, BLG_UPDATED, BLG_UPDATED_TEXT, BLG_URL
BLOGS	BGH_FEED_URL, BGH_ID, BGH_NAME
FEED_CACHE	CCH_TIME, CCH_URL, CCH_VALUE

Hope you will find it useful and fun.
All samples where run on Oracle 10gR2 XE 10.2.0.1.
Schema used in this sample is described in Blogger Backup tool entry.

Paweł

Oracle Blog Aggregators and Bloggers Community

Today I've noticed that my blog became aggregated on official Oracle Blogs site. For me it is really good news as it can bring me more readers. My blog is also aggregated at OraNA.info by Eddie Awad (Oracle blogger of the year 2006) and on orablogs.com by Brian Duff (Oracle employee). Unfortunately I cannot find any of my post on OTN Semantic Web. I hope it will change in near future, as this is still beta release...
For me it seems that latest discussions about Oracle Bloggers community have positive impact. Official list of Oracle blogs not maintained by Oracle employees is more complete now. Also The Pythian Group is doing great job for Oracle community by publishing series of Log Buffer.

Cheers, Paweł

Solving ORA-22905: cannot access rows from a non-nested table item

Some time ago i wrote about Binding list variable. In fact it is conversion of string to rows. My example works fine in SQL but when used in PL/SQL it might cause error ORA-22905: cannot access rows from a non-nested table item. I do know what are the necessary conditions for this error, as it does not always happen in PL/SQL. I faced it usually for quite complicated queries. To overcome this problem I just cast binding variable to varchar2 as shown below:

  procedure test_proc(p_list in varchar2)
  is
  begin
    for r in (select items.extract('/l/text()').getStringVal() item
              from table(xmlSequence(
                extract(XMLType(''||
                  replace(cast (p_list as varchar2(4000)),
                          ',','')||'')
                      ,'/all/l'))) items) loop
      null;
    end loop;
  end;

It worked for me in all situations where I faced ORA-22905 on Oracle 10gR2 (10.2.0.3)

Paweł

OraNA.info upgraded

Eddie Awad just announced that he has improved functionality of Oracle Blogs aggregator OraNA.info. Now it shows post from long history, not only last 20. It is now really usable.
And if you are Oracle Blogger, go and join OraBloggers group on Google groups.

Cheers, Paweł

Zend Core for Oracle v 2.0.1

Zend has announced new version of Zend Core for Oracle.

Key features contained in all Zend Core V2.0 releases include:

• Improved Windows Performance and Reliability – Up to 200-300%
• PHP 5.2.1 enhanced with several key security and bug fixes
• Unified "stack" installer - Bundled copies of Apache and MySQL for quick and easy install and configuration
• Bundled Zend Framework for fast and easy application development
• Universal Database Support - drivers provided for most popular databases.
• Mac OS X Support
• Zend Core Updater - Automatically download Zend Network updates

Give it a try...

Cheers, Paweł

Moving domain index to another tablespace

It seems to be very easy task that can be accomplished using rebuild option:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS;

We can even do it online for most indexes:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS ONLINE;

But trying this for domain index (ex. Spatial Index) causes error:

ALTER INDEX MY_GEOMETRY rebuild tablespace users;
ORA-29871: invalid alter option for a domain index

What is the reson and how to overcome this? Domain index is in fact set of other objects. For spatial index it is table (MDRT_*). Spatial Index can be moved quite easy with

ALTER INDEX MY_GEOMETRY 
REBUILD PARAMETERS('TABLESPACE=NEW_TBS');

Oracle Text CONTEXT index is set of tables:

• DR$[index_name]$I
• DR$[index_name]$K
• DR$[index_name]$N
• DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate text index. But first storage parameters have to be defined:

begin
   ctx_ddl.create_preference('MY_TEXT_STORE', 'BASIC_STORAGE');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'K_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'R_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'N_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_INDEX_CLAUSE',
       'tablespace NEW_TBS COMPRESS 2');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'P_TABLE_CLAUSE',
       'tablespace NEW_TBS');
end;
/

and then just drop and create index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column) 
indextype is ctxsys.context parameters(
'storage MY_TEXT_STORE');

For more information go to Oracle Text Application Developer's Guide

Cheers, Paweł

RMAN TIP: Avoiding ORA-27211

I'm using RMAN to backup only to disk. Usualy when I have to delete obsolete backup I run command:

delete obsolete device type disk;

It is anoying to type device type disk every time, but

delete obsolete;

causes:

RMAN-03002: failure of delete command at 09/12/2005 08:44:27
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

The reason is that some time ago I was testing backups to tape. There is simple solution:

Configure CHANNEL device type 'SBT_TAPE' clear;

It will remove information about tape device configuration, so all commands will use default device: disk.

Cheers, Paweł

CPUApr2007 and Interim patch 3 for 10.2.0.3 on Windows

On Monday I've downloaded Interim patch 3 (5916257) for 10.2.0.3 on Windows released on 13th April. Today I've downloaded CPUApr2007 (5948242) alias Interim patch 4. I've compared content of these patch and majority of files are identical. Most changes are in patch description files. The only important file that has some changes is oracle.exe while all other *.dll files are identical. Also scripts to apply do database catcpu.sql differs only by 2 lines.
Interim patch 3:

DEFINE CPU_NUMBER  = 5916257
DEFINE CPU_DESC    = 'Patch3'

CPUApr2007:

DEFINE CPU_NUMBER  = 5948242
DEFINE CPU_DESC    = 'CPUApr2007'

I wonder why Oracle published 2 patches just in 4 days? On Windows all interim patches contain also previous interim patch. For me the only explanation is that they didn't what to include other bug description into CPU. What do you think?

Cheers, Paweł

Oracle: Cursors, Bind Variables and Performance

I've found today very good document on Efficient use of bind variables, cursor_sharing and related cursor parameters dated 2002. Document is related to Oracle 9i, but also applies to Oracle 10g.

Paweł

Monitor progress of long running processes

This time I want to share how I use v$session_longops to monitor Oracle long running queries but also how to create your own entries in this view. When monitoring long lasting operation I'm interested what is running now, and what was finished lately. For this purposes I use query:

SELECT sid,
  serial#,
  opname,
  target_desc,
  percent,
  sofar,
  totalwork,
  to_char(start_time,   'hh24:mi:ss') start_time,
  to_char(efin,   'hh24:mi:ss') estimate_fin,
  case when sofar <> totalwork and last_update_time < sysdate-1/10000 then '*' else null end broken
FROM
  (SELECT sid,
     serial#,
     opname,
     target_desc,
     sofar,
     totalwork,
     to_char(CASE
             WHEN totalwork = 0 THEN 1
             ELSE sofar / totalwork
             END *100,    '990') percent,
     start_time,
     last_update_time,
     start_time +((elapsed_seconds + time_remaining) / 86400) efin
   FROM v$session_longops
   ORDER BY  CASE
             WHEN sofar = totalwork 
                THEN 1
                ELSE 0 END,
          efin DESC)
WHERE sofar <> totalwork or rownum <= 20;

It lists all currently running operations and up to twenty lately finished. The most important columns are estimate_finish – it predicts time of process end, and broken – if contains star (*) it is very possible that process was terminated or hung. Constant 1/10000 (about 8 seconds) in comparison

efin < sysdate-1/10000

is to avoid false alerts. If single step of yours process takes more then 8 seconds than you should alter this value to meet your needs. You can find more information on v$session_longops view in Oracle documentation.
Sample output:

Lets take a look how to write your own entries to long operations view. As a samle I'll just use simple loop that for each record in ALL_OBJECTS process will sleep for 0.1 second:

declare
  v_rindex pls_integer;
  v_slno   pls_integer;
begin
  v_rindex := dbms_application_info.set_session_longops_nohint;
  for r_qry in (select t.*, rownum rn, count(*) over () cnt 
                  from ALL_OBJECTS t ) loop
    dbms_application_info.set_session_longops
      ( rindex =>  v_rindex,
        slno   =>  v_slno,
        op_name => 'ALL_OBJECTS processing',
        target  =>  0,
        target_desc => 'ALL_OBJECTS',
        context     => 0,
        sofar       => r_qry.rn,
        totalwork   => r_qry.cnt,
        units       => 'loops'
      );
    dbms_lock.sleep(0.1);
  end loop;
end;

So what is important here:

• rindex – for first call it must be ste to dbms_application_info.set_session_longops_nohint – it means add new row to v$session_longops view
• rownum is used to get row number; it is passed to sofar parameter
• analytical function (count(*) over () cnt) is used to calculate all rows (steps) in that process; it is passed to totalwork parameter
• rindex, slno – should be assigned always to the same variable; it is needed to pass information about row that should be changed

Additionaly you can add when others handler to set progress to 100%, but you do have to do that. Broken flag described above should work fine, and you know if process ended normally or with errors. Full specification of dbms_application_info.set_session_longops

Paweł

Table that cannot be deleted

Today I run into interesting situation on one of development machines. I was drooping all tables of one user and after that there was still one table with name SYS_IOT_OVER_54321. Seeing that name I've concluded that it must be an overflow table for some Index Organized Table. But there were no other table in that schema. Of course I was not able to delete it.
Than I've realized that there are some tables in recycle bin. This situation is inconsistent as table is in recycle bin but its overflow area is still in normal table list.

Here is test case:

SQL> create table IOT_TEST
  2  (a_pk number not null primary key
  3  ,a_number number
  4  ,big_string varchar2(4000)
  5  ) organization index
  6  including a_number overflow tablespace users;

Table created.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             IOT_TEST                                         
IOT_TEST                                                                        

So lets drop it and see what will happen:

SQL> drop table IOT_TEST;

Table dropped.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             BIN$rKpJH0NuROKt+Woa00+hMg==$0                   

As you can see we still have IOT Overflow table that cannot be deleted:

SQL> drop table SYS_IOT_OVER_14580;
drop table SYS_IOT_OVER_14580
           *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

The only way to get rid of that table is to purge recycle bin:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

no rows selected

In my opinion it is bug. I haven't yet reported it on metalink, but I'll fill SR soon. I've confirmed it on Oracle 10g R2 (versions: XE, 10.2.0.1, 10.2.0.2, 10.2.0.3)

Paweł

„Do not use Linux, please, pirate our software” - Microsoft

It seems that Microsoft wants You to pirate it's software. According to The Register, Jeff Raikes, Microsoft business group president, said that if You have to pirate, You should pirate Microsoft software. The idea behind it is that some percentage of pirates become paying customers. Microsoft wants You to:

• pay and use MS software,
• use MS software not paying for it; might be in future you will pay,
• not use other software. Microsoft does not want You to switch to competing software, especially legal royalty-free FLOSS software such as Linux, OpenOffice.org or Mozilla.org

So why they implemented Windows Genuine Advantage Programme, if they do not want to push You to be legal? Paweł

Polish Students won ACM Programming Contest

Today students from Warsaw University won ACM-International Collegiate Programming Contest. They solved 8 tasks out of 10, see Final results.

Congratulations to my compatriot,
Paweł

Moving/Adding Control file when using SPFile

Adding controlfile or moving to new location is tricky when your database was started with server parameter file (spfile). It is because You cannot edit spfile when database is not running.
Method 1 – preferred by me:

1. alter system parameter to point to new (planed) location of controlfiles

   alter system set control_files
   = '/dir1/CONTROL01.CTL'
   , '/dir2/CONTROL02.CTL'
   , '/dir3/CONTROL03.CTL'
   scope=spfile;

2. shutdown database,
3. copy one of controlfile to new locations,
4. startup database.

Method 2:

1. shutdown database,
2. copy one of controlfile to new locations,
3. copy spfile to pfile:

   create pfile from spfile;

4. manually edit pfile,
5. copy pfile back to spfile:

   create spfile from pfile;

6. startup database.

Cheers, Paweł

Technorati

I've just created my Technorati Profile, so this post is just to interrelate this blog with my Technorati account.
Paweł

the Month of PHP Bugs

For all PHP developers, web admins and users this might be very interesting. the Month of PHP Bugs is an initiative to improve the security of PHP. It focuses on PHP Core vulnerabilities that are dangerous for web servers with applications written in PHP. It is done in hope that flaws that are publicly disclosed will get fixed quickly.

Cheers, Paweł

Yet another tokenizer in Oracle

I was in need to have function that transforms string into table of words. Also words can be separated by many different chars. For this purpose i've created function:

create or replace function tokenizer
  (p_string in varchar2
  ,p_separators in varchar2
  ) return dbms_sql.varchar2s
is
  v_strs dbms_sql.varchar2s;
begin
  with sel_string as 
      (select p_string fullstring 
         from dual)
  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   from (select beg, lead(beg) over (order by beg) end_p, fullstring
           from (select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0
                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))
    where end_p is not null
      and end_p>beg+1;
  return v_strs;
end;

Usage is very simple. First parameter is string that should be tokenized, second parameter is string with characters that should be treated as word separators. Example:

SQL> set serveroutput on
SQL> declare v_strs dbms_sql.varchar2s;
  2  begin
  3    v_strs := tokenizer('I was in need to have function that transforms string into table of words.'
  4        , ' ,:;.<>[]{}()');
  5    for i in v_strs.first..v_strs.last loop
  6      dbms_output.put_line(v_strs(i));
  7    end loop;
  8  end;
  9  /
I                                                                               
was                                                                             
in                                                                              
need                                                                            
to                                                                              
have                                                                            
function                                                                        
that                                                                            
transforms                                                                      
string                                                                          
into                                                                            
table                                                                           
of                                                                              
words                                                                           

PL/SQL procedure successfully completed.

Let me explain some elements

  with sel_string as 
      (select p_string fullstring 
         from dual)

is just to bind string parameter into SQL just once. Otherwise it will have to be binded in many places.

                 select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0

Internal select generates numbers from 1 to length of string. Outer select chooses only these rows (numbers) that are separators in string.

                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))

adds separator at beginning and end of string, so first and last work can be recognized.

 select beg, lead(beg) over (order by beg) end_p

gives as positions of two consecutive separators, and finally

  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   ...
    where end_p is not null
      and end_p>beg+1;

select words.

Might be you find it useful,
Paweł

Bug 5705795 - AMAZON-LIKE ?

Today I've reviewed list of 10.2.0.3 patch 1 for Windows and i've noticed bug with name: 5705795 - MANY CHILD CURSORS CREATED FOR AMAZON-LIKE WORKLOAD IN 10.2.0.3. I was aware of this bug as it was mentioned in note 403616.1 on Metalink. I do not think it's good idea to use customer name in bug description. From this I can assume:

• Amazon is using latest Oracle Database,

• Amazon is running on Linux or Windows (as this bug is only for these platforms),

• probably Amazon is using Linux as patch for linux was released earlier,

• it gives some inside view of software design used at Amazon,

I do not think Amazon is happy with these reveals.

At least bug 5705795 is not public on Metalink.

Cheers, Paweł

Empty String IS NOT NULL?

Today friend of mine show me interesting case:

declare
  var varchar2(3);
  chr char(3);
begin
  var := ''; -- Empty string here
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NOT NULL

I must say that I was expecting that both variables will be null. It might be my lack of experience with CHAR datatype as I usually use VARCHAR2 even when it is expected that string will have constant length.

declare
  var varchar2(3);
  chr char(3);
begin
  var := null;
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NULL

Just as expected. So it seems that in some cases NULL and empty string is not equivalent. But this difference can be only seen in PL/SQL. When I tried to reproduce this in pure SQL it was always NULL:

SQL> create table a(v1 varchar2(3), v2 varchar2(3), c1 char(3), c2 char(3));
Table created.
SQL> insert into a values (null, '', null, '');
1 row created.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL
SQL> update a set c1 = v1, c2 = v2;
1 row updated.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL

Cheers, Paweł

Interesting posts 2007-02-06

Here are some interesting posts I've read recently:

• User Interface Design: Complexity vs. Flexibility - Rob Walling is traing to find aswer to question: can a design be simple and flexible at the same time?
• How to scale... Tom Kyte post about scalability of middleware and database. Do not forget to read Tim Hall response Middle-tier diet….
• Difference between Jeff Bezos and Bill Gates? by Kathy Sierra - very interesting story about top CEO's attitude to users.

Cheers, Paweł

Restore CONTROLFILE and/or SPFILE from autobackup

As I mentioned earlier in post about setting up autobackup, its time to describe how to restore CONTROLFILE and SPFILE from autobackups. If your database is still running and what You want is just get historical version of SPFILE or CONTROLFILE then it is easy task. Just start RMAN:

RMAN TARGET=/

And enter command to restore CONTROLFILE:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: control file restore from autobackup complete
Finished restore at 01-FEB-07

The until time clause sets time of validity for CONTROLFILE. By default RAM looks for autobackups for seven days into past starting from that time. It can happen that for several days there were no changes, and autobackup was not invoked. In that case add MAXDAYS clause:

RMAN> restore until time 'sysdate-3'
2> CONTROLFILE to 'c:\temp\cfile' from autobackup maxdays 100;

It will force RMAN to look for copies for more days into past. Similarly You can restore SPFILE:

RMAN> restore until time 'sysdate-3'
2> SPFILE to 'c:\temp\SPFILE' from autobackup maxdays 100;
Starting restore at 01-FEB-07
using channel ORA_DISK_1
recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: BAR
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20070129
channel ORA_DISK_1: autobackup found: C:\Backup\c-669001291-20070129-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01-FEB-07

After restoring SPFILE You can convert it to PFILE, so You can easily read settings:

RMAN> sql "create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''";
sql statement: create PFILE = ''c:\temp\PFILE'' from SPFILE = ''c:\temp\SPFILE''

Now more complex scenario: You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don't it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it'is part of the name of autobackup file. Details in my previous post. So if autobackup file name is 'c-669001291-20070129-03' then DBID is 669001291. Now we can start recovery:

RMAN target=/

At the beginning set Database ID:

RMAN> SET DBID 669001291;

Then startup instance:

RMAN> startup force nomount;

and run recovery of SPFILE

RMAN> RUN {
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE SPFILE FROM AUTOBACKUP; }

I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them.

RMAN> shutdown;
RMAN> startup force nomount;
RMAN> RUN { 
2> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F'; 
3> RESTORE CONTROLFILE FROM AUTOBACKUP; }

Now You can continue recovery of datafiles.

Paweł

Autobackup of SPFILE and CONTROLFILE

Even if you do not use RMAN to take backups of your database, you might find it useful to take snapshots of your SPFILE and CONTROLFILE automatically. You can configure Recovery Manager (RMAN) to automatically create copies of CONTROLFILE and SPFILE when you make changes to database such as:

• adding/dropping a new tablespace or datafile
• altering the state of a tablespace or datafile (online/offline/read only)
• adding a new online redo log,
• renaming a file
• adding a new redo thread
• and many more...

To setup autobackup just start RMAN and connect to database

RMAN target=/

or

RMAN target=sys/password@db

set directory and format of backup files:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\Backup\%F';

In name of autbackup file you have to use %F. It will be expanded to C-XXXXXXXXX-YYYYMMDD-NN, where:

• XXXXXXXXX – database id
• YYYYMMDD – day, when backuped
• NN – change number during day, starts with 00, and represented in hexadecimal

Activate autobackups:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Now autobackups are active, and every time you change database structure controlfile will be automatically backed up.
If you use RMAN for backups it will create copy of SPFILE or CONTROLFILE also after each backup operation.
I've tested it on Oracle 10g (10.2) but it should work in Oracle since version 8i. Next time i'll write how to restore SPFILE or CONTROLFILE from these backup.

Cheers, Paweł

Update: How to restore CONTROLFILE and SPFILE from RMAN autobackups