Author: Laurent Schneider

RAC workshop

As announced, I have been speaking for Credit Suisse employees last week. The objective was to give the database project and team leaders the keys arguments for going or not going to RAC. First : what is RAC ? Oracle Real Application Cluster. You have a shared storage, and two instances accessing the same database. […]

Fit for RAC

I will be speaking tomorrow in German and Wednesday in English in Credit Suisse Bank. The workshop is about the differences between single instance and RAC Some of them : architecture, availability, scalability, manageability, skills required, price. I will talk also about the operational aspects, upgrade, dictionary, parameter file.

Paul Moen article on MONTHS_BETWEEN

I have been shocked by Paul Moen article on MONTHS_BETWEEN. Here is my own case : SQL> select months_between( date ‘2000-03-01’, date ‘2000-02-28’) * 31 from dual; 4 Incredible! I have always been very careful with months_between because of the documented end_of_month behaviour (there is one month between 30-APR and 31-MAY), but I did not […]

-ignoreDiskWarning

I am in the process in installing Oracle 10gR2 patch 2 with response file. There is no plenty of disk available, but installing a patch does not require as much space as specified by the runInstaller. when I start in silent mode, I get : $ ./runInstaller -responseFile /home/oracle/10202.rsp -silent Starting Oracle Universal Installer… —————————————————————————– […]

search for a string in all tables of a schema

this is often asked on the forums. I also needed this a while ago while reverse engineering a database model. Here is my today solution: 1) select * and extract the first column found per table with regexp (10g) SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||’”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0; Enter […]

Oracle 9iR2 desupport

How long is Oracle 9iR2/10g going to be supported? Infinitely! Metalink updated the upcoming desupport advisory. For 8iR3, you had something like : Error Correction Support (ECS): 31-DEC-2004 Extended Support (ES): 31-DEC-2007 Extended Maintenance Support (EMS): 31-DEC-2006 Now for 9iR2, 10gR1, 10gR2, you have this : Release GA Date Premier Extended Sustaining Support Support Support […]

su in sqlplus

How to switch user in Oracle ? One approach is to change the password : SQL> connect / as sysdba Connected. SQL> select password from dba_users where username=’SCOTT’; PASSWORD —————————— F894844C34402B67 SQL> alter user scott identified by abc123; User altered. SQL> connect scott/abc123 Connected. SQL> create table t… SQL> connect / as sysdba Connected. SQL> […]

Oracle Magazine January-February 2007

Check the oracle magazine on otn.oracle.com/oramag In the peer-to-peer, you can read more about 3 Oracle Aces, Steve Karam, Nicolas Gasparotto, Dave Moore. In the technology columns, three more aces talk about Oracle technlogies. Steven Feuerstein about The Right Place for PL/SQL, Arup Nanda about Recover in a Flash, and Tom Kyte about On Top-n […]

10.2.0.3

I successfully installed 10gR2 patchset 2 on my notebook : SQL> select ACTION,VERSION,COMMENTS from registry$history; ACTION VERSION COMMENTS ——- ———- —————————— CPU 10.2.0.2.0 CPUOct2006 UPGRADE 10.2.0.3.0 Upgraded from 10.2.0.2.0 SQL> select COMP_NAME,VERSION,STATUS from dba_registry; COMP_NAME VERSION STATUS ———————————– ———- ———– Oracle Expression Filter 10.2.0.3.0 VALID Oracle Database Catalog Views 10.2.0.3.0 VALID Oracle Database Packages and […]

pivot table

First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table. Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java […]

How To Add The Domain Name Of The Host To Name Of The Agent

I have been looking for this note for ages : Metalink note 295949.1 Now I know how to rename my targets in Grid Control! the trick is to stop the agent AH/emctl stop agent to remove the upload and state files/subdirectories cd AH/sysman/emd find state upload recv agntstmp.txt lastupld.xml protocol.ini -type f -exec rm {} […]

difference between two dates

How can i get the difference in days between two dates, d1 and d2 ? – for example : with t as (select to_date(‘2000-02-01′,’YYYY-MM-DD’) d1, to_date(‘2000-01-23′,’YYYY-MM-DD’) d2 from dual) select d1,d2,d2-d1 from t; D1 D2 D2-D1 ———- ———- ———- 01.02.2000 23.01.2000 -9 How can i get the difference in hours:minutes:seconds between two dates, d1 and […]

alias oraver 2.0

Thanks to an anonymous comment in my post yesterday, I can now provide a more flexible version of my alias, which do not require the database to be running nor the sysdba privilege $ alias oraver oraver=’echo ‘\”ORACLE_SID VERSION %CPU RSZ VSZ START_TIME’\”;awk -F: ‘\”/^[^ *#]/{print “printf 42%-9s %11s %5s %8s %8s %s\\n42″,$1,”$(ORACLE_HOME=”$2,$2″/bin/sqlplus -v 2>/dev/null|cut […]

ps -ef |grep pmon alternative

I just wrote a new alias to check if the databases are up and running. I added the version and a dash for non-running database. Needed is /etc/oratab + sysdba access to the database. Here it is : awk -F: ‘/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2” “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep […]

rac automation

Werner Puschitz talked yesterday about Automated Oracle Real Application Clusters Deployment: How Dell Does IT. The result of the automation is, install a 6 nodes cluster in 30 minutes. Which is quite impressive. Werner divided the automation in three RPM packages. 1) configure the host: set up private and virtual interface (according to a naming […]