An Architect's vision

Use the ALTER DATABASE...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example: ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2; Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2 . If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the tablespace group. Likewise, you cannot drop a single temporary tablespace as long as it is the default temporary tablespace.

You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement. The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1 , so that group1 contains tablespaces lmtemp2 and lmtemp3 . CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M TABLESPACE GROUP group1; The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1 , it is in effect moved from group1 to group2 : ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2; Now group2 contains both lmtemp and lmtemp2 , while group1 consists of only tmtemp3 . You can remove a tablespace from a group as shown in the following statement: ALTER TABLESPACE lmtemp3 TABLESPACE GROUP ''; Tablespace lmtemp3 no lon

You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist. For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member: CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf' SIZE 50M TABLESPACE GROUP group1; ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces. A tablespace group has the following characteristics: It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group. It shares the namespace of tablespaces, so its name cannot be the same as any tablespace. You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user. You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the fir

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command. SELECT * from DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ----------------------------------- --------------- --------------- ---------- TEMP 250609664 250609664 249561088

Users who are not explicitly assigned a temporary tablespace use the database default temporary tablespace, which for new installations is TEMP . You can change the default temporary tablespace for the database with the following command: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name ; To determine the current default temporary tablespace for the database, run the following query: SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE -------------------------- ------------------------------ DEFAULT_TEMP_TABLESPACE TEMP

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts. Temporary tablespaces are used to store the following: Intermediate sort results Temporary tables and temporary indexes Temporary LOBs Temporary B-trees By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement. You can assign a temporary tablespace to each database user with the CREATE USER or ALTER USER statement. A single temporary tablespace can be shared by multiple users. You cannot explicitly create objects in a temporary tablespace.

This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”

The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace. CREATE TEMPORARY TABLESPACE tbs_t1     TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON     MAXSIZE UNLIMITED     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K; CREATE GLOBAL TEMPORARY TABLE admin_work_area         (startdate DATE,          enddate DATE,          class CHAR(20))       ON COMMIT DELETE ROWS       TABLESPACE tbs_t1; By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE .

Global Temporary Table definition is created in default tablespace (Not in Temporary Tablespace ) attached to specific schema. Actually Global Temporary Table gets created in default tablespace attached to specific schema and during runtime data been stored in Temporary Tablespace attached to specific schema/user. Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.