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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.