As the name “temporary” itself confronts that its a temporary storage used for sorting,computations, processing. Late 90’s “system” tablespace is being used as temporary tablespace for all the users which is highly not recommended since it brings down the performance of the database drastcially. “System” tablespace is the storge where metadata of the database is being stored and processed. Later Oracle has made a provision to specify a tablespace as an ” Temporary tablespace” while creating a user or with an ALTER user statement. But what happens when a user has been created with out any temporary tablespace specification. Indirectly he would be using the system tablespace which is not recommended. To avoid this situation oracle has introduced “Default Temporary tablespace”. A “Default Temporary Tablespace” can be created for an entire database.  Incase if you forgot to specify the temporary tablespace for an user, storage area specified as Default temporary tablespce would be used.

Continue Reading