Database

Oracle Scripts

Find system constraints

select constraint_name, table_name from user_constraints where constraint_name='SYS_.....';  

Oracle XE not starting on Ubuntu

My oracle instance stopped working one day. Tried reconfiguring, reinstalling etc. The problem turned out to be that I had changed the uid of the oracle user to lower than 1000 so it did not appear in the gdm logon screen. There is a hidden temp folder that then was no longer writable.

The issue:

root@jezm-m1340:/home/jezm/Downloads# ll /var/tmp/.oracle/
total 8
drwxrwxrwt 2 root root 4096 2010-12-17 11:51 ./
drwxrwxrwt 4 root root 4096 2011-03-02 09:18 ../
srwxrwxrwx 1 1002 dba     0 2010-10-04 17:32 s#20596.1=
srwxrwxrwx 1 1002 dba     0 2010-10-04 17:32 s#20596.2=
srwxrwxrwx 1 1002 dba     0 2010-12-17 11:48 s#22146.1=
srwxrwxrwx 1 1002 dba     0 2010-12-17 11:48 s#22146.2=
srwxrwxrwx 1 1002 dba     0 2010-12-17 11:48 sEXTPROC_FOR_XE=

The solution:

root@jezm-m1340:/home/jezm/Downloads# chown oracle /var/tmp/.oracle -R

root@jezm-m1340:/home/jezm/Downloads# ll /var/tmp/.oracle/
total 8
drwxrwxrwt 2 oracle root 4096 2010-12-17 11:51 ./
drwxrwxrwt 4 root   root 4096 2011-03-02 09:18 ../
srwxrwxrwx 1 oracle dba     0 2010-10-04 17:32 s#20596.1=
srwxrwxrwx 1 oracle dba     0 2010-10-04 17:32 s#20596.2=
srwxrwxrwx 1 oracle dba     0 2010-12-17 11:48 s#22146.1=
srwxrwxrwx 1 oracle dba     0 2010-12-17 11:48 s#22146.2=
srwxrwxrwx 1 oracle dba     0 2010-12-17 11:48 sEXTPROC_FOR_XE=

root@jezm-m1340:/home/jezm/Downloads# /etc/init.d/oracle-xe restart
Shutting down Oracle Database 10g Express Edition Instance.
Stopping Oracle Net Listener.

Starting Oracle Net Listener.
Starting Oracle Database 10g Express Edition Instance.