Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Donald K. Burleson

Oracle Tips  

Oracle tablespaces

* The DEFAULT USER tablespace size will depend upon the number of users you want to assign to it and the estimated size of tables they will be using. In most cases, 10 to 20 MB is sufficient. If you expect heavy usage, assign quotas to each user.

* The TEMPORARY USER tablespace should be up to twice the size of your largest table, if you use RULE-based optimization and up to four times the size of your largest table for COST-based; it is also dependent on the number of users and the size of sorts or joins they perform. An improperly designed join between large tables can quickly fill a temporary area. For example, an unrestricted outside join of 2,000 row tables will result in a 1-million-row temporary sort table. If those rows are each several hundred bytes long, there goes your temporary space. Unfortunately, there isn’t much that can be done other than to train developers or ad hoc query generators not to do unrestricted joins of large tables. If a temporary tablespace gets filled, the users who are assigned to it cannot perform operations requiring temporary space; or, worse, the temporary space may be taken from the SYSTEM area. There is a valid argument for having several temporary areas if you have a large number of users. In one instance, a 100-MB temporary tablespace was completely filled by a single multitable outside join using DECODE statements.

* The CWMLITE and DRSYS tablespaces are usually sized at around 20 megabytes by default.
If you have the disk space, placing the TEMPORARY USER tablespaces on disk assets of their own will improve query and report performance due to reduction of disk contention, especially for large reports or queries using disk sorts.

This is an excerpt by Mike Ault’s book “Oracle Administration & Management”.  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.