What tablespace are Oracle sequences stored in?

Question

The app my colleagues and I maintain has an Oracle database at the back-end. We're looking at occasionally running the app in a 'restricted' mode with one of the database tablespaces set to read-only. We can easily move the necessary tables and indexes over to separate tablespaces which will be writable in 'restricted' mode. However, despite several Google searches I can't determine in which tablespace Oracle stores sequences.

This answer mentions that sequence values are stored in the SYSTEM.SEQ$ table. I don't have access to an Oracle DB right now, but I would guess that this table lives in one of the system tablespaces. The tablespace we are making read-only isn't a system tablespace, it's one of our own data tablespaces.

On a dev database I can successfully SELECT from a sequence with the relevant tablespace made read-only.

I suspect it won't be a problem to have this tablespace read-only, but I'd rather my suspicions were confirmed by more than just ad-hoc experimentation. Could someone please enlighten me?

Answer 1

I guess it is system, where else to store this information?

But why do you want to know this? If a tablespace is readonly you can't insert or update so you don't have to use the sequence(s) at all.

Answer 2

I don't have an Oracle DB in front of me right now, be if I recall correctly, sequences are part of the Data Dictionary, and thus cannot be updated manually, and even cannot be (simply) queried without the special syntax (this is why you cannot simply get the current value of a sequence - you must increment it). Obviously, the Data Dictionary is part of the SYSTEM tablespace, and should be touched by any means.

Answer 3

Sequences are (mostly) in SEQ$. Parts will be in OBJ$ (and grants elsewhere). But all these tables are in the SYSTEM tablespace. Some SYSTEM objects are in SYSAUX. DBA_SEGMENTS is a good view to identify which tablespaces an object (such as SEQ$) resides in.

I suspect that Oracle wouldn't let you put these tablespaces into READ ONLY mode as, to do so, it would need to flag that tablespace as read only which is recorded in a SYSTEM table in that tablespace. Sort of like locking the key in the safe which it unlocks.