Diagnosing ORA-00600 and ORA-07445 errors

When I'm unlucky enough to find an ORA-600/ORA-7445 error in one database I'm tasked to manage, I like a lot using Google to find practical, real solutions to this kind of problems, and almost all the time I got a plain explanation of the problem and a precise solution or document to look for the solution. The problem is, if you have to justify your solution you cannot simply say "This is the way because I say so", or "I think the guy of that forum is correct because I feel so".

Therefore, if you need an official document (Oracle) to back your diagnostic or you enjoy a lot using heavy Flash pages then you could try to find information about these errors in Metalink. But first, you will have to gather information about the error in the alert log file and trace files generated:

$ more alert_mydb.log
 ...skipping...
Wed Oct 13 11:56:33 2008
Errors in file /oracle/admin/mydb/udump/mydb_ora_11180.trc:
ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []
Wed Oct 13 11:56:35 2008
Errors in file /oracle/admin/mydb/udump/mydb_ora_11180.trc:
ORA-07445: exception encountered: core dump [kxsbndinf()+604] [SIGSEGV] [unknown code] [0x100010001] [] []
ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []
Wed Oct 13 11:56:36 2008
Errors in file /oracle/admin/mydb/udump/mydb_ora_11180.trc:
ORA-07445: exception encountered: core dump [kxsbndinf()+604] [SIGSEGV] [unknown code] [0x100010001] [] []
ORA-07445: exception encountered: core dump [kxsbndinf()+604] [SIGSEGV] [unknown code] [0x100010001] [] []
ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []

$ more /oracle/admin/mydb/udump/mydb_ora_11180.trc
/oracle/admin/mydb/udump/mydb_ora_11180.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle/product/9.2
System name:    HP-UX
Node name:      myserver
Release:        B.11.11
Version:        U
Machine:        9000/800
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 11180, image: oracle@myserver (TNS V1-V3)

*** SESSION ID:(31.12323) 2008-10-13 11:56:33.939
*** 2008-10-13 11:56:33.939
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []
Current SQL statement for this session:
INSERT INTO MYTABLE (FIELD1, FIELD2, FIELD3) VALUES (SYSDATE, :B1, :B2)
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c0000000aecb8208        14  function MYUSER.MYFUNCTION1
c0000000ae910e40        58  function MYUSER.MYFUNCTION2
c0000000ae91a908         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp()+184         ?        ksedst()             4000000002D91857 ?
                                                   000000004 ?
                                                   C0000000ADD000E8 ?
                                                   000000000 ?
ksfdmp()+32          ?        ksedmp()             C0000000B162AD60 ?
                                                   800003FA00054A68 ?
                                                   000000007 ?
                                                   C0000000B1628A40 ?
kgeriv()+152         ?        ksfdmp()             000000007 ?
                                                   C0000000B1628A40 ?
                                                   40000000011979C3 ?
                                                   800000010000B980 ?
kgeasi()+172         ?        kgeriv()             800000010000B980 ?
                                                   800000010000B8A8 ?
                                                   800000010000B3D8 ?
                                                   80000001000DA060 ?
kxsrbb()+1972        ?        kgeasi()             C0000000B162AD60 ?
                                                   800003FFC0001D20 ?
                                                   000115464 ? 000000030 ?
kxsxsi()+668         ?        kxsrbb()             800000000000000 ?
                                                   4000000000844158 ?
                                                   000008108 ?
                                                   40000000004E55F0 ?
opitca()+5140        ?        kxsxsi()             800000000000000 ?
                                                   80000001000DB8B0 ?
                                                   800003FFC00010C8 ?
                                                   C0000000B162A99C ?
kkslod()+2932        ?        opitca()             80000001000C10B0 ?
                                                   800003F980068828 ?
                                                   C0000000AC3B3D50 ?
                                                   000000000 ?
kglobld()+1516       ?        kkslod()             80000001000D90B8 ?
                                                   400000000071C2A0 ?
                                                   DD40F440010385D0 ?
                                                   000000000 ?
kglobpn()+1156       ?        kglobld()            00000009C ? 000000000 ?
                                                   800003FFBFFFD4C0 ?

If you haven't done so, read the Master Note for Diagnosing ORA-600 Metalink document to understand the basics of diagnosing errors, and if you read carefully you will notice a nice tool called ORA-600/ORA-7445 Error Look-up; it would be better to use the Flash version of this document in order to have full features available.

With this tool you will have two options to look for errors, the first one I recommend to try is Look-up By Error or Error Code's First Argument. In this box you have to select the error code (ORA-600/ORA-7445), enter the error code arguments in order (like [736], [7], [0], [128]), and select your database version if apply. The more arguments you input the more precise diagnostic you might get, but if you get nothing then try to enter less arguments.

If you are not sure about this result, try the Search by Stack Trace option. There you have to enter the stack trace below the dashed line; again, the more stack trace you input the more precise diagnostic you might get, but if you get nothing then try to enter less stack trace.

That's it! This way you can search for information about Oracle bugs in Metalink less painfully; hope you don't have to use this tool too often.

Getting partition's size

Getting the size of a partition in Linux is very simple if you're logged in as root:

olimpo:~# fdisk -l /dev/sda

Disk /dev/sda: 160.0 GB, 160000000000 bytes
255 heads, 63 sectors/track, 19452 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xabd592d5

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1567    12586896    7  HPFS/NTFS
/dev/sda2            1568        2546     7863817+  17  Hidden HPFS/NTFS
/dev/sda3            2547        2796     2008125   82  Linux swap / Solaris
/dev/sda4            2797       19452   133789320   83  Linux

But it might be that you're just a DBA with access only to some partitions instead of the whole disk, and you got this server with no idea about partition sizes. If this is the case, then you can still know the size of your partitions with fdisk:

oracle@olimpo:/myASM$ /sbin/fdisk -l /dev/sda
Cannot open /dev/sda
oracle@olimpo:/myASM$ ls -la
total 8
drwxr-xr-x   2 oracle dba  4096 Oct 25 12:15 .
drwxr-xr-x 113 root   root 4096 Oct 25 12:15 ..
brw-rw----   1 oracle dba  8, 4 Oct  3 08:44 sda4
oracle@olimpo:/myASM$ /sbin/fdisk -s sda4
133789320

In Solaris is a bit different as the -s flag is not the same as in Linux:

oracle@myserver:/myASM$ ls -la
total 4
drwxr-xr-x   2 oracle   dba          512 Oct 22  2008 .
drwxr-xr-x  38 root     root        1024 Oct 18 16:57 ..
crw-r--r--   1 oracle   dba       32, 24 Oct 22  2008 disk1
crw-r--r--   1 oracle   dba       32, 25 Oct 25 09:31 disk2
crw-r--r--   1 oracle   dba       32, 26 Oct 22  2008 disk3

oracle@myserver:/myASM$ /sbin/fdisk -Rg disk2
* Label geometry for device disk2
* PCYL     NCYL     ACYL     BCYL     NHEAD NSECT SECSIZ
  35135    35135    2        0        64    255   512   

oracle@myserver:/myASM$ echo "(35135*64*255*512)/1024/1024"|bc
279982

With the last command you calculate the partition size in megabytes.

Playing with ssh, xauth and DISPLAY

If you need to connect by ssh to an UNIX server and later change user (by means of su or otherwise), but you want to keep X11 forwarding to your screen, then look at this example using xauth and DISPLAY. The key is getting cookie information with xauth list and identifying the screen opened by ssh (usually screen 10 of the server), and after changing user pasting the whole line after the xauth add command:

myuser@olimpo:~$ ssh -X -l myuser myserver1
Password: 
Last login: Mon Oct 17 18:00:46 2011 from olimpo
$ xclock
$ xauth list
myserver1.mynet.net:10  MIT-MAGIC-COOKIE-1  eb3713bfc73799efede775bf1a04cb19
$ su - oracle
Password: 
myserver1:/home/oracle> xclock
Error: Can't open display: 
myserver1:/home/oracle> xauth add myserver1.mynet.net:10  MIT-MAGIC-COOKIE-1  eb3713bfc73799efede775bf1a04cb19
myserver1:/home/oracle> export DISPLAY=myserver1.mynet.net:10
myserver1:/home/oracle> xclock
myserver1:/home/oracle> uname -n
myserver1
myserver1:/home/oracle>

The uname -n command from above example was an extra for the following case: this time you try to login to other server but you get a xauth error message and are unable to forward any X11 window:

myuser@olimpo:~$ ssh -X -l oracle myserver2
Password: 
/usr/openwin/bin/xauth: (stdin):1:  bad display name "unix:10.0" in "add" command

:/home/oracle> xclock
X11 connection rejected because of wrong authentication.
X connection to localhost:10.0 broken (explicit kill or server shutdown).
:/home/oracle> echo $DISPLAY
localhost:10.0
:/home/oracle> uname -n

:/home/oracle> 

As you might have noticed the DISPLAY variable seems correct, but there is a problem with the host name: there is no host name set; that's why xauth fails at login time. You have to set properly the host name according to your UNIX version in order to correct this problem.

More information:

Getting X11 forwarding through ssh working after running su

Getting HP-UX system information

If you need to check information about your HP-UX server like hardware and filesystems, or even information about a high availability cluster (having root access), you can use the lanscan, ioscan, bdf and cmviewcl commands. Or if you're looking for information about memory usage you can check this post.

$ /usr/sbin/lanscan
Hardware Station        Crd  Hdw   Net-Interface    NM   MAC       HP-DLPI DLPI
Path     Address        In#  State NamePPA          ID   Type      Support Mjr#
0/1/2/0  0x001635700001 0    UP    lan0 snap0       1    ETHER       Yes   119
0/3/1/0/6/0 0x001635300002 1    UP    lan1 snap1       2    ETHER       Yes   119
0/4/1/0/6/0 0x001635300003 2    UP    lan2 snap2       3    ETHER       Yes   119
$ /usr/sbin/ioscan -fnk
Class       I  H/W Path       Driver    S/W State   H/W Type     Description
=============================================================================
root        0                 root      CLAIMED     BUS_NEXUS    
ioa         0  0              sba       CLAIMED     BUS_NEXUS    System Bus Adapter (880)
ba          0  0/0            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
usb         0  0/0/1/0        hcd       CLAIMED     INTERFACE    NEC USB Interface
usbhub      0  0/0/1/0.1      hub       CLAIMED     DEVICE       USB Root Hub
usb         1  0/0/1/1        hcd       CLAIMED     INTERFACE    NEC USB Interface
usbhub      1  0/0/1/1.1      hub       CLAIMED     DEVICE       USB Root Hub
                             /dev/hub
usb         2  0/0/1/2        ehci      CLAIMED     INTERFACE    NEC USB Interface
sideba      0  0/0/2/0        side_ba   CLAIMED     INTERFACE    CMD IDE controller
ext_bus     0  0/0/2/0.0      side      CLAIMED     INTERFACE    IDE Primary Channel
target      0  0/0/2/0.0.0    tgt       CLAIMED     DEVICE       
disk        0  0/0/2/0.0.0.0  sdisk     CLAIMED     DEVICE       TEAC    DV-28E-N
                             /dev/dsk/c0t0d0   /dev/rdsk/c0t0d0
target      1  0/0/2/0.0.7    tgt       CLAIMED     DEVICE       
ctl         0  0/0/2/0.0.7.0  sctl      CLAIMED     DEVICE       Initiator
                             /dev/rscsi/c0t7d0
ext_bus     1  0/0/2/0.1      side      CLAIMED     INTERFACE    IDE Secondary Channel
target      2  0/0/2/0.1.7    tgt       CLAIMED     DEVICE       
ctl         1  0/0/2/0.1.7.0  sctl      CLAIMED     DEVICE       Initiator
                             /dev/rscsi/c1t7d0
ba          1  0/1            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
ext_bus     2  0/1/1/0        c8xx      CLAIMED     INTERFACE    SCSI C1010 Ultra160 Wide LVD
target      3  0/1/1/0.0      tgt       CLAIMED     DEVICE       
disk        2  0/1/1/0.0.0    sdisk     CLAIMED     DEVICE       HP 73.4GMAX3073NC
                             /dev/dsk/c2t0d0   /dev/rdsk/c2t0d0
target      4  0/1/1/0.1      tgt       CLAIMED     DEVICE       
disk        1  0/1/1/0.1.0    sdisk     CLAIMED     DEVICE       HP 73.4GMAX3073NC
                             /dev/dsk/c2t1d0   /dev/rdsk/c2t1d0
target      5  0/1/1/0.7      tgt       CLAIMED     DEVICE       
ctl         2  0/1/1/0.7.0    sctl      CLAIMED     DEVICE       Initiator
                             /dev/rscsi/c2t7d0
ext_bus     3  0/1/1/1        c8xx      CLAIMED     INTERFACE    SCSI C1010 Ultra160 Wide LVD
target      6  0/1/1/1.7      tgt       CLAIMED     DEVICE       
ctl         3  0/1/1/1.7.0    sctl      CLAIMED     DEVICE       Initiator
                             /dev/rscsi/c3t7d0
lan         0  0/1/2/0        igelan    CLAIMED     INTERFACE    HP PCI 1000Base-T Core
ba          2  0/2            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
ba          3  0/3            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
ba          4  0/3/1/0        PCItoPCI  CLAIMED     BUS_NEXUS    PCItoPCI Bridge
fc          0  0/3/1/0/4/0    fcd       CLAIMED     INTERFACE    HP 2Gb PCI/PCI-X Fibre Channel FC/GigE Dual Port Combo Adapter
                             /dev/fcd0
fcp         0  0/3/1/0/4/0.8  fcd_fcp   CLAIMED     INTERFACE    FCP Protocol Adapter
ext_bus     7  0/3/1/0/4/0.8.0.2.0        fcd_vbus  CLAIMED     INTERFACE    FCP Array Interface
target      7  0/3/1/0/4/0.8.0.2.0.0      tgt       CLAIMED     DEVICE       
disk       89  0/3/1/0/4/0.8.0.2.0.0.0    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c7t0d0   /dev/rdsk/c7t0d0
disk        8  0/3/1/0/4/0.8.0.2.0.0.1    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c7t0d1   /dev/rdsk/c7t0d1
disk       23  0/3/1/0/4/0.8.0.2.0.0.2    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c7t0d2   /dev/rdsk/c7t0d2
disk       24  0/3/1/0/4/0.8.0.2.0.0.3    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c7t0d3   /dev/rdsk/c7t0d3
ext_bus     4  0/3/1/0/4/0.8.0.255.0      fcd_vbus  CLAIMED     INTERFACE    FCP Device Interface
target      8  0/3/1/0/4/0.8.0.255.0.2    tgt       CLAIMED     DEVICE       
ctl        23  0/3/1/0/4/0.8.0.255.0.2.0  sctl      CLAIMED     DEVICE       COMPAQ  MSA1000
                             /dev/rscsi/c4t2d0
lan         1  0/3/1/0/6/0    igelan    CLAIMED     INTERFACE    HP A9784-60002 PCI/PCI-X 1000Base-T FC/GigE Combo Adapter
ba          5  0/4            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
ba          6  0/4/1/0        PCItoPCI  CLAIMED     BUS_NEXUS    PCItoPCI Bridge
fc          1  0/4/1/0/4/0    fcd       CLAIMED     INTERFACE    HP 2Gb PCI/PCI-X Fibre Channel FC/GigE Dual Port Combo Adapter
                             /dev/fcd1
fcp         1  0/4/1/0/4/0.8  fcd_fcp   CLAIMED     INTERFACE    FCP Protocol Adapter
ext_bus     6  0/4/1/0/4/0.8.0.1.0        fcd_vbus  CLAIMED     INTERFACE    FCP Array Interface
target      9  0/4/1/0/4/0.8.0.1.0.0      tgt       CLAIMED     DEVICE       
disk       19  0/4/1/0/4/0.8.0.1.0.0.0    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c6t0d0   /dev/rdsk/c6t0d0
disk       20  0/4/1/0/4/0.8.0.1.0.0.1    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c6t0d1   /dev/rdsk/c6t0d1
disk       21  0/4/1/0/4/0.8.0.1.0.0.2    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c6t0d2   /dev/rdsk/c6t0d2
disk       22  0/4/1/0/4/0.8.0.1.0.0.3    sdisk     CLAIMED     DEVICE       COMPAQ  MSA1000 VOLUME
                             /dev/dsk/c6t0d3   /dev/rdsk/c6t0d3
ext_bus     5  0/4/1/0/4/0.8.0.255.0      fcd_vbus  CLAIMED     INTERFACE    FCP Device Interface
target     10  0/4/1/0/4/0.8.0.255.0.1    tgt       CLAIMED     DEVICE       
ctl         6  0/4/1/0/4/0.8.0.255.0.1.0  sctl      CLAIMED     DEVICE       COMPAQ  MSA1000
                             /dev/rscsi/c5t1d0
lan         2  0/4/1/0/6/0    igelan    CLAIMED     INTERFACE    HP A9784-60002 PCI/PCI-X 1000Base-T FC/GigE Combo Adapter
ba          7  0/6            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
ba          8  0/7            lba       CLAIMED     BUS_NEXUS    Local PCI-X Bus Adapter (783)
tty         0  0/7/1/0        asio0     CLAIMED     INTERFACE    PCI SimpleComm (103c1290)
                             /dev/diag/mux0  /dev/mux0       /dev/tty0p0   
tty         1  0/7/1/1        asio0     CLAIMED     INTERFACE    PCI Serial (103c1048)
                             /dev/GSPdiag1   /dev/diag/mux1  /dev/mux1       /dev/tty1p0     /dev/tty1p2     /dev/tty1p4   
unknown    -1  0/7/2/0                  UNCLAIMED   UNKNOWN      PCI Display (10025159)
memory      0  8              memory    CLAIMED     MEMORY       Memory
ipmi        0  16             ipmi      CLAIMED     INTERFACE    IPMI Controller
                             /dev/ipmi
processor   0  128            processor CLAIMED     PROCESSOR    Processor
processor   1  129            processor CLAIMED     PROCESSOR    Processor
$ bdf -i
Filesystem          kbytes    used   avail %used  iused  ifree %iuse Mounted on
/dev/vg00/lvol3    1048576  799824  246928   76%   2888   7768   27% /
/dev/vg00/lvol1     511672   54328  406176   12%     65  50111    0% /stand
/dev/vg00/lvol8    8388608 1890448 6447536   23%  23785 203063   10% /var
/dev/vg00/lvol7    2097152 1575968  517184   75%  31502  16274   66% /usr
/dev/vg00/lvol4    1048576  768632  278256   73%    357   8731    4% /tmp
/dev/vg00/lvol6    4194304 2195952 1982784   53%  32112  62448   34% /opt
/dev/vg00/lvol5    2097152  669912 1416104   32%   1512  44600    3% /home
# cmviewcl -v

CLUSTER           STATUS       
my-cluster        up           

  NODE         STATUS       STATE        
  node-01      up           running      

    Network_Parameters:
    INTERFACE    STATUS       PATH                NAME         
    PRIMARY      up           0/1/2/0             lan0         
    PRIMARY      up           0/3/1/0/6/0         lan1         
    STANDBY      up           0/4/1/0/6/0         lan2         

    PACKAGE      STATUS       STATE        AUTO_RUN     NODE         
    mypkg        up           running      enabled      node-01      

      Policy_Parameters:
      POLICY_NAME     CONFIGURED_VALUE
      Failover        configured_node
      Failback        manual

      Script_Parameters:
      ITEM       STATUS   MAX_RESTARTS  RESTARTS   NAME
      Service    up                  3         0   MYMONITOR 
      Subnet     up                                10.0.1.0 

      Node_Switching_Parameters:
      NODE_TYPE    STATUS       SWITCHING    NAME                      
      Primary      up           enabled      node-01      (current)    
      Alternate    up           enabled      node-02                   

  NODE         STATUS       STATE        
  node-02      up           running      

    Network_Parameters:
    INTERFACE    STATUS       PATH                NAME         
    PRIMARY      up           0/1/2/0             lan0         
    PRIMARY      up           0/3/1/0/6/0         lan1         
    STANDBY      up           0/4/1/0/6/0         lan2         

Dropping old partitions with a script

Lets say that you created a partitioned table some time ago to hold a lot of data generated by day:

CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

This data is needed to generate some reports, but after some time it is not necessary anymore so you have to drop old partitions in order to not waste disk space. What a simple task, isn't it? You just have to look for information in USER_TAB_PARTITIONS and drop the oldest partitions:

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P193448                    TO_DATE(' 2011-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193449                    TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193450                    TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE;
select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE
                       *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';
select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0'
                              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

What a problem; there is a lot of restrictions manipulating LONG datatypes so you cannot just get the date from the HIGH_VALUE column. For this task you'll need a PL/SQL script:

SQL> DECLARE
  v_sql             varchar2(500);
  v_date            date;
  v_partition_name  user_tab_partitions.partition_name%TYPE;
  v_high_value      user_tab_partitions.high_value%TYPE;
  CURSOR c1 IS select PARTITION_NAME, HIGH_VALUE
               from user_tab_partitions where table_name='MY_TABLE' and PARTITION_NAME!='P0';
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_partition_name, v_high_value;
    EXIT WHEN c1%NOTFOUND;
    v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
    IF v_date < (sysdate - 2) then
      v_sql := 'alter table MY_TABLE drop partition ' || v_partition_name;
      execute immediate v_sql;
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

PL/SQL procedure successfully completed.

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P193449                    TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193450                    TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

With this script you can drop partitions from 2 days ago and older, and put it as a daily job in order to automate this task.

Connect from Linux to an X server

This trick is a very simple but useful one: if you want to open an X session in your Linux computer from other UNIX computer, like if you were in front of its console, then you can run as root a command like this:

X -query unixserver :1

This way you're telling X to connect to the greeter running in unixserver and display it in graphic console number one (you might be running an X session in :0 already). In order to work this the greeter in the UNIX computer must be running and listening for connections; for example, it would be xdm, kdm or gdm in Linux, dtlogin in HP-UX and so on.

Installing TimesTen 11g

In this post we will learn how to install Oracle TimesTen 11g, an in-memory database for applications that need very short response times.

First you have to download TimesTen according to your platform; in this post we will install TimesTen in a Linux Red Hat 64 bit server.

After downloading it you have to create (as root) an user and a group for TimesTen, and also create the /etc/TimesTen directory to hold some configuration files needed:

[root@myserver /]# groupadd timesten
[root@myserver /]# useradd -c "TimesTen" -d /TimesTen -m -g timesten timesten
[root@myserver /]# passwd timesten
Changing password for user timesten.
New UNIX password: 
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.
[root@myserver /]# mkdir /etc/TimesTen
[root@myserver /]# chown timesten:timesten /etc/TimesTen

Next you have to place the donwloaded file in the server, login as timesten and uncompress the installation file:

[timesten@myserver ~]$ tar -zxvf timesten112180.linux8664.tar.gz 
linux8664/
linux8664/doc/
linux8664/doc/doc.zip
linux8664/3rdparty/
linux8664/3rdparty/ant-1.6.2-bin.tar.bz2
linux8664/3rdparty/jms-1_1-fr-apidocs.tar.bz2
linux8664/install.pl
linux8664/setup.sh
linux8664/uninst.sh
linux8664/README.html
linux8664/LINUX8664/
linux8664/LINUX8664/manifest
linux8664/LINUX8664/perl
linux8664/LINUX8664/bzip2
linux8664/LINUX8664/unzip
linux8664/LINUX8664/ttpatchinst
linux8664/LINUX8664/common.tar.bz2
linux8664/LINUX8664/ttserver.tar.bz2
linux8664/LINUX8664/timesten.tar.bz2
linux8664/LINUX8664/ttclient.tar.bz2
[timesten@myserver ~]$ cd linux8664/
[timesten@myserver linux8664]$ ls -la
total 404
drwxrwxr-x 5 timesten timesten   4096 Feb  2  2011 .
drwxr-xr-x 3 timesten timesten   4096 Oct  3 12:37 ..
drwxr-xr-x 2 timesten timesten   4096 Feb  2  2011 3rdparty
drwxrwxr-x 2 timesten timesten   4096 Feb  2  2011 doc
-rwxr-xr-x 1 timesten timesten 241188 Feb  2  2011 install.pl
drwxr-xr-x 2 timesten timesten   4096 Feb  2  2011 LINUX8664
-rwxr--r-- 1 timesten timesten  95771 Feb  2  2011 README.html
-rwxr-xr-x 1 timesten timesten   5623 Feb  2  2011 setup.sh
-rwxr-xr-x 1 timesten timesten  34313 Feb  2  2011 uninst.sh

You can safely install TimesTen running the setup.sh script with just the default answers, as you can see here:

[timesten@myserver linux8664]$ ./setup.sh 

NOTE: Each TimesTen installation is identified by a unique instance name.
      The instance name must be a non-null alphanumeric string, not longer
      than 255 characters.

Please choose an instance name for this installation? [ tt1121 ] 
Instance name will be 'tt1121'.
Is this correct? [ yes ] 

Of the three components:

  [1] Client/Server and Data Manager
  [2] Data Manager Only
  [3] Client Only

Which would you like to install? [ 1 ] 

Of the following options :

  [1] /TimesTen
  [2] /TimesTen/inst
  [3] Specify a location
  [q] Quit the installation

Where would you like to install the tt1121 instance of TimesTen? [ 1 ] 
Where would you like to create the daemon home directory? [ /TimesTen/tt1121/info ] 

The daemon logs will be located in /TimesTen/tt1121/info
Would you like to specify a different location for the daemon logs? [ no ] 
Installing into /TimesTen/tt1121 ...
Uncompressing ...

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
      daemon port number must be the same across all TimesTen installations
      managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
      port number that is set at installation time. The daemon port number can
      be verified by running 'ttVersion'.

The default port number is 53388.

Do you want to use the default port number for the TimesTen daemon? [ yes ] 
The daemon will run on the default port number (53388).

NOTE: For security, we recommend that you restrict access to the
      TimesTen installation to members of a single OS group. Only members of
      that OS group will be allowed to perform direct mode connections to
      TimesTen, and only members of that OS group will be allowed to perform
      operations that access TimesTen data stores, TimesTen files and shared
      memory. The OS group defaults to the primary group of the instance
      administrator. You can default to this group, choose another OS group
      or you can make this instance world-accessible. If you choose to make
      this instance world-accessible, all database files and shared memory
      are readable and writable by all users.

Restrict access to the the TimesTen installation to the group 'timesten'? [ yes ] 

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ] 

In order to use the 'In-Memory Database Cache' feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using (install_dir)/bin/ttModInstall.

Please enter a value for TNS_ADMIN (s=skip)? [  ] s


NOTE: It appears that you are running version 4.1 of the g++
      compiler. TimesTen ships with multiple sets of client libraries and server
      binaries : one built for compatibility with g++ 3.4.6 and one with
      g++ 4.1.0. The installer has created links to the 4.1.0 library in the
      (install_dir)/lib directory and to the 4.1.0 server binary in the
      (install_dir)/bin directory. If you want to use a different compiler,
      please modify the links to point to the desired library and server binary.

Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53389 ] 

At this point you can choose to install the TimesTen documentation and example database; if you want to use this installation as a learning environment or to check if your installation was fine, it would be better to install the documentation and example database.

Do you want to install QuickStart and the TimesTen Documentation? [ no ] yes
Where would you like to install the quickstart and doc directories (s=skip)? [ /TimesTen/tt1121 ] 

The TimesTen Quickstart applications can take up to 64 Mbytes of disk space.
Depending on how your system is configured, you may not want to create the
QuickStart DemoDataStore directory in the default location,
/TimesTen/tt1121/info/DemoDataStore

Where would you like to create the DemoDataStore directory? [ /TimesTen/tt1121/info ] 
Creating /TimesTen/tt1121/info/DemoDataStore ...

Installing client components ...

At this point the installation is almost finished:

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ] 

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the 'setuproot' script :
 cd /TimesTen/tt1121/bin
 ./setuproot -install
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
  '/TimesTen/tt1121/startup/tt_tt1121'.

The Quickstart home page can be accessed here :
  '/TimesTen/tt1121/quickstart/index.html'

The 11.2.1.8 Release Notes are located here :
  '/TimesTen/tt1121/README.html'

Starting the daemon ...
TimesTen Daemon startup OK.
End of TimesTen installation.

After the setup script finishes you will find that the TimesTen daemons are up and running:

[timesten@myserver linux8664]$ ps ax|grep TimesTen
14186 ?        Sl     0:00 /TimesTen/tt1121/bin/timestend -initfd 13
14189 ?        Sl     0:00 /TimesTen/tt1121/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000000 -facility user
14190 ?        Sl     0:00 /TimesTen/tt1121/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000001 -facility user
14191 ?        Sl     0:00 /TimesTen/tt1121/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000002 -facility user
14192 ?        Sl     0:00 /TimesTen/tt1121/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000003 -facility user
14201 ?        Sl     0:00 /TimesTen/tt1121/bin/ttcserver -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1000004 -p 53389 -facility user -group timesten
14692 pts/1    S+     0:00 grep TimesTen

And if you installed the database examples then you could check your installation loggin into an example database:

[timesten@myserver linux8664]$ export PATH=$PATH:/TimesTen/tt1121/bin
[timesten@myserver linux8664]$ ttIsql -connStr "DSN=sampledb_1121"

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=sampledb_1121";
Connection successful: DSN=sampledb_1121;UID=timesten;DataStore=/TimesTen/tt1121/info/DemoDataStore/sampledb_1121;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/TimesTen/tt1121/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> exit
Disconnecting...
Done.

Finally, if you want to start TimesTen services on server startup you can run as root the setuproot script:

[root@myserver /]# cd /TimesTen/tt1121/bin/
[root@myserver bin]# ./setuproot -install
Would you like to install the TimesTen daemon startup scripts into /etc/init.d? [ yes ] 
Copying /TimesTen/tt1121/startup/tt_tt1121 to /etc/init.d

Successfully installed the following scripts :
/etc/init.d/tt_tt1121
/etc/rc.d/rc0.d/K45tt_tt1121
/etc/rc.d/rc1.d/K45tt_tt1121
/etc/rc.d/rc2.d/S90tt_tt1121
/etc/rc.d/rc3.d/S90tt_tt1121
/etc/rc.d/rc5.d/S90tt_tt1121
/etc/rc.d/rc6.d/K45tt_tt1121

That's it! You have a brand-new Oracle TimesTen installation ready to use; also, you might like to add the TimesTen bin path to your PATH environment variable.

More information:

TimesTen In-Memory Database Documentation