STORAGE

Storage in an oracle database goes something like this ...

An oracle database is made up of one (two from 10g) or tablespaces. Each tablespace can have one or more datafiles. A datafile can be a file or pointer to a raw device or use ASM (automatic storage management).

When creating a tablespace you can specify locally managed or dictionary managed. Local managed should mean quicker dictionary response as all the info relating to extents held in the tablespaces etc are held in the tablespace and not the data dictionary.

When creating locally managed tablespaces, you have the option of setting the extents to uniform or automatic. I always think uniform is the best then you can control what segments go into what tablespace. This is probably quite old school, but there you go.

undo created at db startup

It appears undo at database startup is dependent on

- transactions_per_rollback_segment
- max_sessions

oracle (9.2.0 at least) uses the following formula to work out how many undo segments it uses when the undo tablespace is created.

max_rollback_segments = (max_sessions * 1.1) / transactions_per_rollback_segment

oracle will always have at least this number. We had the problem that max_sessions was changed and the undo tablespace was not recreated. Consequently the database was only ever creating what the old session level was set at.

We were also getting some weird errors around ORA-1555, snapshot too old errors when we kick a number of jobs off at once and they fail immediately with ORA-1555 errors.

We are in the process of making this change to production so time will tell !!!

UNDO, Some Discussion

OK, below are issues relating to the undo problems with oracle 9.2.0.3.

Seems as though there are a few bugs. The problem we seem to have is when we do either materialized view refreshes and truncate table type commands.

The problem seems to be that oracle can not create enough undo segments. When running these again, often straight after the failure it works. This generally seems to be the case.

The way undo seems to normally work is that it creates segments when required. There are particular rules around when it grabs new extents, how much space it grabs etc. The smon process is responsible for dropping undo segments that are not required any more. It generally does this every 12 hours. There is an event to stop this from happenning.