Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Quotas and system performance

Please be informed that there are no QUOTAS specified for the users on USERS and TEMP tablespaces; therefore I would like to know the affect of this action on the system performance.

Quotas, by themselves, do not do anything to hamper or improve database performance. They just define how much space, if any, a user can obtain from a particular tablespace. Keep in mind, that if a user has a quota of zero bytes on a tablespace, that user can still obtain space if he has the UNLIMITED TABLESPACE system privilege. This privilege overrides all quotas. And one additional item to note is that the CONNECT role automatically grants the UNLIMITED TABLESPACE priv to the user. So if you granted this role to a user, you will have to revoke it.

That being said, there can be some side affects on performance if a user does not have accurate quotas. For instance, assume that a user has zero quota on the TEMP tablespace. This means that any disk sorts for that user must be performed in a different tablespace. Most times, this occurs in the SYSTEM tablespace. With sorting and other activities going on in the SYSTEM tablespace, performance can suffer. So make sure that quotas are set up appropriately.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.