Earlier today we had a problem logging into a database that had just been created automatically.
When on the actual box (Linux)
sqlplus schema/pw, i.e. a local connection, was fine, though, it was occasionally impossible to do anything once connected. Otherwise, whether using a session on the box or not
sqlplus schema/pw@db did not work at all.
tnsping and DNS testing revealed nothing and there was no problem in
The Listener was accepting the connection but not handing it off to the database. This was confirmed by using
tail -f on the listener log and watching it accept the connection. There is a logon trigger on the database that inserts the user details / time etc into a table, but this doesn't have any indexes and there cannot have been any locks.
We finally traced the issue. There was a conflict in the file that creates the temporary tablespaces for the database. It resulted in there only being 5MB of tempspace instead of the normal 25GB. Disabling the logon trigger and then adding the additional temporary tablespaces, sorted everything out. There were some sessions already running that would have been using some of this tablespace.
Apparently Oracle requires some temporary tablespace in order to accept an incoming connection. The specific version used on this DB was 9i, though I think this applies to all.
Why and what is it used for?