Hi Reader,

In a posting in  the Oracle Xing Forum Sven Vetter showed a very simple way to write for example a GUID generator Statement with CONNECT BY;

just

SELECT Sys_Guid()

 FROM  Dual

CONNECT BY LEVEL <= 10

;

SQL> SELECT Sys_Guid()
  2    FROM Dual
  3  CONNECT BY LEVEL <= 10;
 
SYS_GUID()
——————————–
2EDF60AEC8A41F7AE040A98C881B4573
2EDF60AEC8A51F7AE040A98C881B4573
2EDF60AEC8A61F7AE040A98C881B4573
2EDF60AEC8A71F7AE040A98C881B4573
2EDF60AEC8A81F7AE040A98C881B4573
2EDF60AEC8A91F7AE040A98C881B4573
2EDF60AEC8AA1F7AE040A98C881B4573
2EDF60AEC8AB1F7AE040A98C881B4573
2EDF60AEC8AC1F7AE040A98C881B4573
2EDF60AEC8AD1F7AE040A98C881B4573
 
10 rows selected
 
SQL>

Impressed?
Karl Reitschuster

NOTE:

As Laurent Schneider mentioned - this is not normal CONNECT BY behavior and should not work at all - it’s a bug. You must use the PRIOR Operator to describe the parent relationship.

Hi reader,

some aspects i need to remember in future installation i want to record here. SuSE Linux 10.1 is different from the SLES/OpenSuSE  Installations. Libraries,  Development Tools, … are missing.

In order to run Oracle 10G installation on SuSE 10.1 you must

• use DVD Media - only on DVD the 64Bit packages are available. Otherwise you could do a network install.
  
• create Oracle user with dba as primary group
  
• Install the C/C++ Compiler packages - without them you cannot relink the oracle binaries during installation

  gcc-c++ - The GNU C++ Compiler
  

• Install 32bit shared and development libraries

  glibc-32bit - Standard Shared Libraries (from the GNU C Library)
  glibc-devel-32bit - Include Files and Libraries Mandatory for Development
  

• Install the orarun package
• Support for Asyncronus I/O

  libaio - Linux-Native Asynchronous I/O Access Library
  

• Do not set LD_LIBRARY_PATH - sounds interesting ;-)

  unset LD_LIBRARY_PATH
  

• starting installer with disabling the check of the Operatingsystem.

runInstaller -ignoreSysPrereqs

With these settings i could install Oracle 10.2 on SuSE Linux 10.1 on AMD 64bit.

Some additional configuration is needed if you want to setup a database

• ulimit - added by orarun tool in  /etc/security/limits.conf
  

## added by orarun ##
oracle  soft    nproc   2047
oracle  hard    nproc   16384
oracle  soft    nofile  1024
oracle  hard    nofile  65536
# End of file

• shared memory parameters - once you installed the orarun package :

/usr/sbin/rcoracle start  # sets SHM paramter
/sbin/chkconfig oracle on # sets SHM parameter during startup

• Uncomment 127.0.0.2 ip address for Hostname and enter the real address for proper access via dbconsole

#127.0.0.2       sv019291.dev.global.taxi.com sv019291
150.179.37.146  sv019291.dev.global.taxi.com sv019291

• ORA-27102: out of memory/Linux-x86_64 Error: 28: No space left on device
  you have to size shared memory properly. Shared memory must be large enough for all started instances.
  But not all physical memory must be allocated; The Value should by <= 75% of total physical memory.

(TotalAllocatedSGA(all instances) < (shmall * PageSize) )
&&
( (shmall * PageSize) <= (TotalPhysicalMemory / 100 ) * 75 )

retrieving Memory pagesize :  getconf PAGE_SIZE

retrieving shmall setting : cat /proc/sys/kernel/shmall

• The network card settings should be checked. Sometimes due to a mismatch of duplex settings for example network speed could drop down < 10%
  ;

  view the settings of your servers network card interface eth0 :
  ethtool eth0

  set network card properties (interface eth0 *only* for example!) : 
  ethtool -s eth0 speed 100 duplex full autoneg off

Will complete the list in future

Karl Reitschuster

Links …

The Installation Guide for SLES 10 : Oracle 10g R2 (10.2.0.1) on SUSE Linux Enterprise Server 10

The Installation Guide for OpenSuSE : Oracle Database10g R2 (10.2.0.1) on openSUSE 10.2

Setting Shared Memory (Puschitz) : Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

Blog of proper setting of SHMALL : It’s an SGA Explosion