You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




31 January 2006

Application Server 10gR3 on Suse Linux 10

I just download and installed Oracle Application 10g Release 3 on my linux box.

The version released this week is very light! No ldap, no database, well, a light one...


Application Server 10g Release 3 x86: 531 MB
Included:
* Oracle HTTP Server
* Oracle Application Server Containers for J2EE (OC4J)
* Oracle Enterprise Manager 10g Application Server Control
* Oracle Business Rules
* Oracle TopLink

Application Server 10g Release 2 x86: 2 GB
Included:
* Oracle HTTP Server Oracle Application Server Containers for J2EE (OC4J)
* Oracle Application Server Web Cache
* Oracle Application Server Portal
* Oracle Application Server Wireless
* Oracle Sensor Edge Server
* Oracle Enterprise Manager 10g Application Server Control
* Oracle Database Server 10g (10.1.0.4.2)
* Oracle Internet Directory
* Oracle Application Server Single Sign-On
* Oracle Application Server Directory Integration Provisioning
* Oracle Application Server Delegated Administration Services
* Oracle Application Server Certificate Authority
* Oracle Application Server Forms Services
* Oracle Application Server Reports Services
* Oracle Application Server Personalization
* Oracle Business Intelligence Discoverer
* Oracle Security Developer Tools
* Oracle Application Server Guard
* OracleAS Backup and Recovery Tool





I tried to install on my notebook, and, o surprise, it works...
the only thing that was missing and required was public domain korn shell, pdksh, which is easy to find by googling



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




30 January 2006

Recursive PL/SQL

it will be a good week !

I found an elegant way to solve a query with recursive pl/sql.

an user wanted to have DHSGHDADSFDF translated in DHSGAF, that is, duplicated chars removed, order retained.

here is my function :

create or replace function f(v varchar2) return varchar2 is
begin
if (v is null) then return null;
else return substr(v,1,1)||f(replace(substr(v,2),substr(v,1,1));
end if;
end;
/


ref: using recursion with PL/SQL



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




raptor early adopter release 3.1 is out

raptor product page

I have not checked the differences with 3.0 yet, probably bug fixes with "do not save passwords"



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




26 January 2006

raptor early adopter release 3 is out

This is THE release I have been waiting for, a raptor which does NOT save the password on your local PC. Save a password, even encrypted, on you local PC allows every one with physical access to your PC to get access to your productive database, which may be high-secured and with controlled physical access.

Download immediatly on otn !



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




23 January 2006

static expression

Using Static Expressions with Conditional Compilation defines the kind of expression you can use with conditional compilation...

I got one question on forums.oracle.com today, an user wanted to use bitand and could not.

I wrote my answer but, ... , the site is down and eventually my answer did not get accepted.

So, to translate bitand(x,y)=z, using static expression, when x<8 and y<8 and z<8, then

(
(
(x=0) or
(y=0) or
(x=1 and (y=2 or y=4 or y=6)) or
(x=2 and (y=1 or y=3 or y=5 or y=7)) or
(x=3 and y=4) or
(x=4 and (y=1 or y=2 or y=3)) or
(x=5 and y=2) or
(x=6 and y=1)
) and z=0
) or (
(
(x=1 and (y=1 or y=3 or y=5 or y=7)) or
(x=3 and (y=1 or y=5)) or
(x=5 and (y=1 or y=3)) or
(x=7 and y=1)
) and z=1
) or (
(
(x=2 and (y=2 or y=3 or y=6 or y=7)) or
(x=3 and (y=2 or y=6)) or
(x=6 and (y=2 or y=3)) or
(x=7 and (y=2))
) and z=2
) or (
(
(x=3 and (y=3 or y=7)) or
(x=7 and y=3)
) and z=3
) or (
(
(x=4 and (y=4 or y=5 or y=6 or y=7)) or
(x=5 and (y=4 or y=6)) or
(x=6 and (y=4 or y=5)) or
(x=7 and y=4)
) and z=4
) or (
(
(x=5 and (y=5 or y=7)) or
(x=7 and y=5)
) and z=5
) or (
(
(x=6 and (y=6 or y=7)) or
(x=7 and y=6)
) and z=6
) or (
x=7 and y=7 and z=7
)


quite big, but this is evaluated only once, at compilation time, so it should be an acceptable workaround in some case !



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




20 January 2006

difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);.

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

SQL> set autot trace exp
SQL> delete emp;

14 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> set autot off
SQL> select count(*) from emp;
0
SQL> roll
Rollback complete.


but explain plan does not
SQL> select count(*) from emp;
14

SQL> explain plan for delete emp;

Explained.

SQL> select * from table (dbms_xplan.display);
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
14



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




19 January 2006

change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change "automatically"

Demo

SQL> CREATE DATABASE
MAXDATAFILES 5
MAXINSTANCES 1
MAXLOGFILES 2
MAXLOGMEMBERS 1
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1;

Database created.

SQL> create tablespace t1;

Tablespace created.

SQL> create tablespace t2;

Tablespace created.

SQL> select count(*) from v$datafile;
COUNT(*)
----------
6

datafiles exceeded, but no error!


SQL> alter database add logfile ('/dbms/oracle/LSC75/redo/f1.sql','/dbms/oracle/LSC75/redo/f2.sql') size 16M;

Database altered.

SQL> select group#, members from v$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 2

logfiles and logmembers exceeded, but no error!



SQL> alter database add logfile instance 'I2';

Database altered.

SQL> alter database add logfile instance 'I2';

Database altered.

SQL> alter database enable instance 'I2';

Database altered.

SQL> select count(*) from v$INSTANCE_LOG_GROUP;
COUNT(*)
----------
2


instances exceeded, but no error!

so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




18 January 2006

security bug revealed

Pete Finnigan just mentioned a bug, which allow any user in any oracle version to get dba privilege Imperva discovers a critical access control bypass in login bug.

This is incredible!

Well, Pete urged you to apply CPU2006January asap.



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




17 January 2006

select * from test where my_long like '%toto%'

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on developpez.com (french forum)


SQL> create table test ( my_long long);

Table created.

SQL> insert into test values ('hello toto !');

1 row created.

SQL> exec for r in ( select my_long from test ) loop if (r.my_long like '%toto%') then dbms_output.put_line(r.my_long); end if; end loop
hello toto !



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




12 January 2006

new version of raptor

a few improvement described in the readme.txt. One of this is big-tnsfile support.

always missing is a way to NOT SAVE the password on the client!



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




oem grid control 10gR2 on Suse10

Last friday I installed RAC on my Suse10. Today, I installed Grid Control.

Of course it is not supported.

The packages I neeeded additionaly are db1 and openmotif.

It did not work 100%. I finally started the iasconsole (emctl start iasconsole), and within the iasconsole, I restarted the failed elements.

I created the repository manually (sysman/admin/emdrep/bin/RepManager)



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




11 January 2006

clear screen reports cleared columns, breaks and computes


$ echo clear screen|sqlplus scott/tiger
SQL> columns cleared
breaks cleared
computes cleared


but if I quit properly, it does not report that

$ echo "clear screen
quit"|sqlplus scott/tiger
SQL>


a good reason to improve the quality of your shell script by quitting at the end ;-)



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




06 January 2006

Suse10 10gR2 Laptop RAC

Hi,
I recommend the following reading Linux 10g Laptop RAC.

I have just installed RAC 10gR2 on Suse10.

The document above guided me thru the steps.

Specific Suse10 :
- raw devices are configured by editing /etc/raw with values like raw1:loop1 and must be activated with rcraw start (+ chkconfig -a raw for persitence)

To take care :
- read point 8 to survive reboot...

Specific 10gR2 :
In 10gR2, MAXINSTANCES must not be specified by create controlfiles. In 10gR2, the controlfile can grow dynamically. Even if I created my controlfile with maxinstances=1, I can do alter database add logfile instance 'RAC2' ; alter database add logfile instance 'RAC2' ; alter database enable instance 'RAC2';, which is simply great! CREATE CONTROLFILE just belongs to the past!

Installation:
I had to run vipca manually as root at the end of the root script of the crs installation. I also removed my existing oracle_homes and rebooted before installing database software again. The VIP interface must be configured in /etc/hosts and DOWN (ifconfig down eth0:2).

Conclusion:
The doc written by Amit Poddar on Howad site is usefull. Enjoy rac'ing on your pocket desk calculator!



You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




05 January 2006

Webcomment (test)

This is the homepage of dora