Why does connecting to a database use tempspace?

Question

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 listener.ora.

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?

Answer 1

During the connect process Oracle builds information on what access you have. This remains static for the length of the connection. (If you need to access newly granted privileges, you need to reconnect.) Creating this data as temporary tables would seem the appropriate thing to do. This would require temporary tablespace and could trigger the problems you were encountering.

The lack of locks, does not mean there isn't in-flight information floating around. If the transaction inserting the data doesn't commit, then the database will need to track the block containing the row until the transaction is committed. You can check for this by opening two connections and seeing if both can see the information from the login trigger before doing a commit.

There are views which will show what locks exist, and a wide variety of other information which is useful in managing the database.