set my ORACLE_HOME, PATH, ORACLE_SID

I posted yesterday some of my aliases. My favorite (and most obfuscated) one is the following (for bash)


eval $(awk -F: '/^[+a-zA-Z]/{l=tolower($1); sub("^+","",l); print "alias "l"=47x="$2";PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID="$1"; echo ORACLE_SID="$1"; 47; "}' /etc/oratab 2>/dev/null)

I am setting my path and a default sid/home in my .profile


[ -z "$ORACLE_SID" ] && export ORACLE_SID=LSC01
export ORACLE_HOME=$(sed -n "s/:.$//;s/^$ORACLE_SID://p" /etc/oratab)
PATH=$ORACLE_HOME/bin
PATH=$PATH:$ORACLE_HOME/opmn/bin
PATH=$PATH:$ORACLE_HOME/dcm/bin
PATH=$PATH:$HOME/bin
PATH=$PATH:/usr/local/bin
PATH=$PATH:/usr/bin
PATH=$PATH:/usr/X11R6/bin
PATH=$PATH:/bin
PATH=$PATH:/usr/sbin
PATH=$PATH:/opt/gnome/bin
PATH=$PATH:/opt/kde3/bin
PATH=$PATH:.
export PATH

Than in my eval command above set an alias for each line in /etc/oratab

# /etc/oratab
LSC01:/app/oracle/oracle/product/10.2.0/db_2:Y
+ASM1:/home/oracle/oracle/product/10.2.0/db_2:Y
RAC1:/home/oracle/oracle/product/10.2.0/db_2:Y
RAC2:/home/oracle/oracle/product/10.2.0/db_2:Y
crs:/app/oracle/product/10.2.0/crs:N

so here is the list of my dynamically generated aliases

alias asm1='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=+ASM1; echo ORACLE_SID=+ASM1;'
alias crs='x=/app/oracle/product/10.2.0/crs; PATH=${PATH//$ORACLE_HOME/$x};ORACLE_HOME=$x; ORACLE_SID=crs; echo ORACLE_SID=crs;'
alias lsc01='x=/app/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x};ORACLE_HOME=$x; ORACLE_SID=LSC01; echo ORACLE_SID=LSC01;'
alias rac1='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=RAC1; echo ORACLE_SID=RAC1;'
alias rac2='x=/home/oracle/oracle/product/10.2.0/db_2; PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=RAC2;echo ORACLE_SID=RAC2;'

One alias I also like to set is a switch-user alias + keep settings and profile !
alias oracle='su - oracle -c "DISPLAY=$DISPLAY ORACLE_HOME=$ORACLE_HOME ORACLE_SID=$ORACLE_SID PATH=$PATH bash --rcfile ~lsc/.bashrc"'

I also have exotic, less recommendable, aliases…


alias +='sqlplus -L /'
alias -- -='cd -'
alias ..='cd ..'
alias ...='cd ../..'
alias ....='cd ../../..'
alias .....='cd ../../../..'
alias ......='cd ../../../../..'
alias .......='cd ../../../../../..'
alias ........='cd ../../../../../../..'

I also have a very handy function, called p

p() {
    sqlplus -L -s "/ as sysdba" <<EOF  | sed -n 's/@ //p'
set echo off lin 9999 trimsp on feedb off head off pages 0 tab off
col name for a25
select '@',name, value from v\$parameter2 where upper(name) like upper('%$1%');
EOF
}
P() {
    sqlplus -L -s "/ as sysdba" <<EOF  | sed -n 's/@ //p'
set echo off lin 9999 trimsp on feedb off head off pages 0 tab off
col name for a25
select '@',ksppinm name,ksppstvl value FROM x\$ksppi join x\$ksppcv using (inst_id,indx) where upper(ksppinm) like upper('%$1%');
EOF
}

so I can check the parameter directly from the shell

$ P shared_pool_size
_io_shared_pool_size      4194304
shared_pool_size          0
__shared_pool_size        83886080

I also use KSH sometimes, there I have a slicly different version of my aliases…

OCM preparation

The OCM 10g Upgrade is currently under development, and the OCM 10g exam should be available in May.

How will I prepare for the 10g OCM?

  • Part I, get confident with the environment :
    According to http://education.oracle.com/certification, the exam will be on redhat AS3 with DB 10.1.0.4. RHAS3 is not free. If I had not RH in my company, I would then probably use WhiteBox Linux or a similar RedHat clone to get an environment as close as possible to the lab. It seems very important to me is to get the same terminals (Gnome) and the same Browser (Mozilla 1.6).

    I am trained to customized my environment within seconds. For example, I like to have a bookmark for 10G Book List, one for the Reference, one for SQL Reference, one for PL/SQL Packages Ref and one for RMAN Reference. So within one click, I can reach the appropriate book. I also like to shortcut most of my commands… I like to type pmon instead of ps -ef |grep ora_pmon and so I defined some aliases. I am able to define those below plus a few more obscure ones in less than one minute.

    
    alias abort='echo shutdown abort|sqlplus -L -s / as sysdba'
    alias alert='vi $ORACLE_HOME/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log'
    alias nomount='echo startup nomount quiet|sqlplus -L -s / as sysdba'
    alias ora='cd $ORACLE_HOME'
    alias pmon='ps -ef | grep [p]mon'
    alias startup='echo startup quiet|sqlplus -L -s / as sysdba'
    alias sysdba='sqlplus -L / as sysdba'
    alias tns='cd $ORACLE_HOME/network/admin'
    

    I have read man vi until my eyes hurt. I have trained using GNOME keyboard shortcuts until my finger skin burnt.

    I have shortcut to set my oracle homes, and I am proficient with Unix commands.

    With the few commands I defined, I am able to do most maintenance tasks and to call the oracle binaries like sqlplus faster than Lucky Luke.

    I have read the SQL reference manual, the reference manual, and the RMAN reference manual.
    I can type in less than two minutes the complete syntax for creating a database. I am familiar with the most commonly used character sets. I can create tablespaces, add and remove files (well, removing logfile and tempfiles only in 10gR1).

    I have a bulletproof backup and recovery strategy. I know how to check critical ORA- message in the alert log, I can identify the missing files / directories on disk, I can recover from any error.

    I know -almost- every listener.ora, sqlnet.ora (except the security related ones), and tnsnames.ora. I can create sqlnet.ora, listener.ora, tnsnames.ora, start and stop my listener within one minute.

  • Part II, read the specification
    I print the documentation and I make sure I have understood the objectives. I try every scenario. In order to save time during the preparation, I am using scripts, so that I can generate a few databases quickly. For each scenario, I am generating errors. If you train with a collegue, try to generate the most surprising database corruption for your collegue at each scenario 😉

    There is a RAC scenario. I have received an Egenera blade to test Oracle Real Application Cluster, with up to 8 nodes. I documented in a previous post the steps I followed to install RAC on my notebook before receiving the Egenera hardware : Suse10 10gR2 Laptop Rac.

    … invest enough time … do not schedule it too early … search for articles in Oracle magazine

  • Part III, relax
    It is a nice challenge. Do sleep enough before the exam. Book a quiet hotel at least one day before. Do not work on the day before. Just rest.

  • Oracle Application Server

    One month ago, I attended an Oracle Application Server course in Geneva, Switzerland. We were a group of 3 students and the 5-days lesson has been extremly interesting and intense. Starting at 9am, we used to finish at 5:30pm or later.

    At LC Systems, we have a few customers using OAS, that is why I did jump into this non-database product cursus.

    As I experienced in the course, it is quite different from Oracle Database. Technically speaking, you do not need much database know-how to administer an application server.

    There are basically two parts. One is the Infrastructure. It contains a Directory Server (LDAP) which is stored physically on a database. As for the Grid Control, you can just click Next->Next->Next->Install to have it up and running (if you are lucky). You do not specify the size of the memory, the location of the files, the logging mode, nothing. Just the database passwords.

    The second part is called the Middle Tier. It contains a J2EE engine. There you can “hotplug” your application : portal, forms, reports, discoverer…

    The consistency, availability, backup strategy are not as clearly defined as in the db world I know. In the “online backup” mode, you should just hope that nothing is going to modify the xml configuration files at the time you edit them. If your file system is getting full of logs, it can well happen that your config file get truncated. If you think you are not modifying anything during the backup, it could well be Oracle itself that updates the xml files. What did also sound dubious to me is the point in time recovery. It sounds like : “Well, I will try to recover until time your repository with rman, and if I found your backup sets on the disk, it may work. If the config changed since that time, well, this is probably not the best thing to try…”

    The security concept made me hurl more than one time. For example Portal. There is a superuser called PORTAL in the Oracle Internet Directory (OID). To my surprise, you can see his database password in clear text in the OID. This user has, no joke, DBA role in the database. You can “revoke” the DBA role from PORTAL in the Application Server Console, but, believe me, it does not revoke the DBA at the db level. The mod_plsql enables you to encrypt passwords in files. This is a 2-ways encryption, I doubt the algorythm is very strong. The concept rather rely on “No one should ever log-in neither on the application server (with telnet) nor on the repository database (with sqlplus).

    This morning I passed my 1Z0-311 exam with success (0 error).

    OCP Column

    There is a new INSIDE OCP column in the Oracle Magazine this month (May-June 2006 Edition).

    It is about the Application Server exam. In the magazine, it is referenced as 1Z1-311, but the beta phase ended half a year ago, so the exam is now production 1Z0-311. I could guess most answers, and I hope I can succeed at first try, which is no sure thing. Anyway, I will try it next Friday at Oracle University in Baden/Zurich.

    New PC

    I bought a new PC at Eastern. I have installed 100Gb partition for Linux and 100Gb for Windows. I tried Fedora Core 5 and OpenSuse 10.0, in 64bit mode.

    I currently spend most of my “free” time to prepare the OCA Application Server certification, next friday (28th).

    Tonight I borrowed a game from a friend : Unreal Tournament 2004. I haven’t play such a game since a long time, and I must say, I enjoyed it very much. I am just overtired now. The graphic is fast, it is easy to master (well, I played at the novice level) and it is something I would never recommend to my kids ! Just shooting everywhere …

    Well, they are just too young now, but Dora is really mastering Memory Game and she can even write her name when she achieved a high score! Yesterday she had her fourth birthday.

    isqlplus and sqlplusw desupport

    isqlplus and sqlplusw (gui sqlplus windows) will be desupported in 2010. Read Note 359855.1 and Note 359859.1 on Metalink.

    Oracle recommends migrating to Raptor (SQLDeveloper) or to Application Express (HTMLDB) or command line sqlplus.

    Oracle Support apparently does not know that Raptor changed name before going to production, well, names change so often, it is difficult to know them all!

    dba workshop

    Last week I gave an internal 2-days dba course. All three students had good experience of filesystem backup and unix, but hardly any oracle experience.

    I figured out recovery is more complex than I thought! By explaining to other, how often do you realise do you do not know the answer yourself? It has been very educative to me too…

    First inconsistency I discovered :
    I tried to simulate a disaster by removing all controlfiles, redologs, datafiles. I expected the students to notice “something”. But, O Surprise, the db kept running, nothing written in alert logs, it was possible to create and drop tables, nothing went wrong (but they were no files). Probably the OS has not notified the file were no more there, and Oracle opened the files already. Strange. Even SHUTDOWN has been successful! But of course STARTUP did not work.

    Next time I prepare a B&R workshop, I must invest time to check that a disaster is effective!

    Second inconsistency:

    Report unrecoverable datafiles and validate backups. I wrote this in the rman technology forum this morning: REPORT NEED BACKUP is not suffisant to have consistent backups, nor RESTORE VALIDATE DATABASE!

    More inconsistencies :
    somehow, when users are doing backups with nocatalog and try to restore with the recovery manager catalog, it is not working. Well, it is not that surprising, but it really increases the difficulty of recovery procedures!

    However, those “inconsitencies” were good in this informal training, feedback from students was positive, we did “survive” all those problems and they realised RMAN recovery is not as simple as RMAN backup…

    oracle days

    I was yesterday at Oracle Days. I have met a dozen of people I know, and was pleased to see Troy, who gave me one more tip for installing EM grid on a high availability cluster. I also saw Lutz, which announced me the workshop with Jonathan Lewis in a few weeks www.digicomp.ch/jlewis

    I took two photos with my new handy, one with my collegue Zarko and one with the Oracle Partner Chocolate I gave to my daughter :-)

    OCM 10g

    The 10g OCM practicum, expected for 2004, is now officially launched.

    Oracle Database 10g Certified Master Exam

    Well, depending on your country, you will get a
    ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275. In this case, just change your country to United States. Then you will see the details.

    If you want to register, you probably need to wait a little bit more :

    A public event is not currently available for this offering. To express your interest in a future publicly scheduled event, or, if you would like Oracle University to contact you regarding other options, please Not finding the course or date that you’re looking for? Let us know!

    In addition to 10g New features like bigfile tablespaces, ASM, Flashback DB and Datapump, the 10g OCM includes RAC, logical standby, Grid Control and Performance Tuning assistants. Quite promising…

    The 10g OCM upgrade is currently in developement. But I do not expect it to be available in my country or neighbour country in 2006.

    to_char(interval)

    There is no to_char function available for intervals.

    Or at least it does not work as expected

    
    SQL> select to_char(interval '1234' second, 'HH24:MM') from dual;
    TO_CHAR(INTERVAL'12
    -------------------
    +00 00:20:34.000000
    

    I just write my own one, with some new format elements

    For interval day to second, I have DDD number of days, HH number of hours (0-24), HHH total number of hours (0-99999999999999), etc

    here it is

    
    SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 17:15:59 2006
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> 
    SQL> create or replace function tochards(f_int interval day to second,f_fmt varchar2) return varchar2 is
      2  -- valid formats are DDD, HHH, HH, MMM, MM, SSS, SS, FF
      3      ret varchar2(4000);
      4      f varchar2(4000);
      5      i interval day(9) to second(9);
      6  begin
      7      if (f_fmt is null or f_int is null) then
      8          return null;
      9      end if;
     10      f := upper(f_fmt);
     11      if (translate(f,'XDHMSF,.:;/- ','X') is not null) then
     12          raise_application_error(-20001,'Invalid format');
     13      end if;
     14      if (extract(day  from i)<0) then
     15          ret:='-';
     16          i:=f_int*(-1);
     17      else
     18          ret:='';
     19          i:=f_int;
     20      end if;
     21      while (f is not null) loop
     22          if (f like 'DDD%') then
     23              ret:=ret||to_char(extract(day from i),'FM999999999999999999');
     24              f:=substr(f,4);
     25          elsif (f like 'HHH%') then
     26              ret:=ret||to_char(extract(day from i)*24+extract(hour from i),'FM999999999999999999');
     27              f:=substr(f,4);
     28          elsif (f like 'HH%') then
     29              ret:=ret||to_char(extract(hour from i),'FM999999999999999999');
     30              f:=substr(f,3);
     31          elsif (f like 'MMM%') then
     32              ret:=ret||to_char(extract(day from i)*24*60+extract(hour from i)*60+extract(minute from i),'FM999999999999999999');
     33              f:=substr(f,4);
     34          elsif (f like 'MM%') then
     35              ret:=ret||to_char(extract(minute from i),'FM999999999999999999');
     36              f:=substr(f,3);
     37          elsif (f like 'SSS%') then
     38              ret:=ret||to_char(extract(day from i)*24*60*60+extract(hour from i)*60*60+extract(minute from i)*60+trunc(extract(second from i)),'FM999999999999999999');
     39              f:=substr(f,4);
     40          elsif (f like 'SS%') then
     41              ret:=ret||to_char(trunc(extract(second from i)),'FM999999999999999999');
     42              f:=substr(f,3);
     43          elsif (f like 'FF%') then
     44              ret:=ret||to_char(mod(extract(second from i),1),'FM999999999999999999');
     45              f:=substr(f,3);
     46          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
     47              ret:=ret||substr(f,1,1);
     48              f:=substr(f,2);
     49          else
     50              raise_application_error(-20001,'Invalid format : '||f_fmt);
     51          end if;
     52      end loop;
     53      return ret;
     54  end;
     55  /
    
    Function created.
    
    SQL> 
    SQL> sho err
    No errors.
    SQL> 
    SQL> create or replace function tocharym(f_int interval year to month,f_fmt varchar2) return varchar2 is
      2  -- valid formats are YYY, MMM, MM
      3      ret varchar2(4000);
      4      f varchar2(4000);
      5      i interval year to month;
      6  begin
      7      if (f_fmt is null or f_int is null) then
      8          return null;
      9      end if;
     10      f := upper(f_fmt);
     11      if (translate(f,'XYM,.:;/- ','X') is not null) then
     12          raise_application_error(-20001,'Invalid format');
     13      end if;
     14      if (extract(year  from i)<0) then
     15          ret:='-';
     16          i:=f_int*(-1);
     17      else
     18          ret:='';
     19          i:=f_int;
     20      end if;
     21      while (f is not null) loop
     22          if (f like 'YYY%') then
     23              ret:=ret||to_char(extract(year from i),'FM999999999999999999');
     24              f:=substr(f,4);
     25          elsif (f like 'MMM%') then
     26              ret:=ret||to_char(extract(year from i)*12+extract(month from i),'FM999999999999999999');
     27              f:=substr(f,4);
     28          elsif (f like 'MM%') then
     29              ret:=ret||to_char(extract(month from i),'FM999999999999999999');
     30              f:=substr(f,3);
     31          elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
     32              ret:=ret||substr(f,1,1);
     33              f:=substr(f,2);
     34          else
     35              raise_application_error(-20001,'Invalid format : '||f_fmt);
     36          end if;
     37      end loop;
     38      return ret;
     39  end;
     40  /
    
    Function created.
    
    SQL> 
    SQL> sho err
    No errors.
    SQL> 
    SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'HHH:MM') from dual;
    
    TOCHARDS(CURRENT_TIMESTAMP-TIM
    ------------------------------
    53561:15
    
    SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'DDD HH:MM:SS') from dual;
    
    TOCHARDS(CURRENT_TIMESTAMP-TIM
    ------------------------------
    2231 17:15:59
    
    SQL> select tocharym(interval '25' month, 'YYY:MM') from dual;
    
    TOCHARYM(INTERVAL'25'MONTH,'YY
    ------------------------------
    2:1
    
    SQL> select tocharym(interval '-25' month, 'MMM') from dual;
    
    TOCHARYM(INTERVAL'-25'MONTH,'M
    ------------------------------
    -25
    
    SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    

    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

    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

    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 !

    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(
    (
    (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 !

    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
    

    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?

    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 !
    

    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)