Topic : Temporary Tablespace

What happens when the DEFAULT and TEMP tablespace clauses are left out from CREATE USER statements?


Answer :

The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).

Show Answer Discuss

How will you know which temporaray tablepsace is allocated to which user?


Answer :

SQL> Select temporary_tablespace from dba_users where username=?SCOTT?;

Show Answer Discuss

How will you find the current users who are using temporary tablespace segments?


Answer :

SQL> V$TEMPSEG_USAGE

Show Answer Discuss

Is it possible to make tempfiles as read only?


Answer :

YES.

Show Answer Discuss


Answer :

TEMPORARY tablespace gets cleared once the transaction is done where as PERMANENT tablespace retails the data.

Show Answer Discuss