Author: Laurent Schneider

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 […]