Temporary Tablespace Infomation using Query

1) To know how much space is used in temporary segments:

SQL>Select extent_size*8192*used_extents/1024/1024/1024"space used in GigaByte" from v$sort_segment;

Space used in GigaByte
----------------------
196.5847621

SQl>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_blocks, total_blocks from v$sort_segment; 

Tablespace_name Extent_size Total_extents Used_extents Free_extents Max_used_blocks Total_blocks
--------------- ----------- ------------- ------------- ----------- ---------------- ------------
Test        128         11896          4526           7370      1449216          1522688

2) To know SQL ID and Which type of Sort Segment issue:

SQL>select username,sqladdr, sql_id, tablespace, segtype, extents,blocks from v$tempseg_usage;

3) To know which SQL is using Temporary segments query:

SQL>select s.sql_text, t.username, t.tablespace, t.segtype, t.blocs, t.extents 

from v$sql s, v$tempseg_usage t

where t.sql_id=s.sql_id;

4) Information about tablespace containing sort segments:

SQL>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size from v$sort_segmrnt;

5)The users who is performng sort operation in Temp Segments:

SQL>select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid, a.serial#,a.username, a.osuser, a.status from v$session a,v$sort_usage b
where a.saddr=b.session_addr
order by b.tablespace, b.segfile#, b.segblk#,b.blocks;

Read More

Free space in Temporary Tablespace

A default temporary tablespace is specified in a database at the time of the database creation,By using DEFAULT TEMPORARY TABLESPACE in the create database statement.You can drop the default temporary tablespace,then system tablespace will be used as default temporary tablespace.Default temporary tablespace cannot make permanent or take it offline.The DBA_FREE_SPACE view allows you to show about free space in a tablespace but it shows only about permanent tablespace. You have to query V$TEMP_SPACE_HEADER view to know about the free space of temporary tablespace.You can see the use of DBA_FREE_SPACE and V$TEMP_SPACE_HEADER view as follows:

1) If you have TEST as permanent tablespace and TEMP as temporary tablespace and you run query DBA_FREE_SPACE then it will show TEST in list the list but TEMP is not listed.
 

SQL>select tablespace_name from dba_free_space;

TABLESPACE_NAME
----------------
UNDO1
SYSAUX
SYSTEM
TEST

4 rows selected.

2) To know information about free space of temporary tablespace

SQL>select tablespace_name.file_id,bytes_used,bytes_free from v$temp_space_header;

TABLESPACE_NAME   FILE_ID  BYTES_USED BYTES_FREE
----------------- -------- --------- ------------
TEMP              5894714  19625687
 

Read More

Using tablespace with a temporary tablespace group

A tablespace group enables a user to consume temporary space from multiple tablespaces.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 do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.
The following statement adds a tablespace to an existing group. It creates and adds tablespace temp to group1, so that group1 contains tablespaces temp2 and temp3.

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE '/opt/oracle/data/temp301.dbf' SIZE 25M
     TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case because tablespace temp2 already belongs to group1, it is in effect moved from group1 to group2:

ALTER TABLESPACE temp2 TABLESPACE GROUP group2;
Now group2 contains both temp and temp2, while group1 consists of only temp3.

You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE temp3 TABLESPACE GROUP '';

Below syntax assigns a user to a default temporary tablespace group.

ALTER USER USER TEMPORARY TABLESPACE group1; 

Read More

ORA-12906 cannot drop default temporary tablespace

If you try to drop default temporary tablespace of database as it will result in error as:-

SQL>SELECT TABLESPACE_NAME FROM DBA_TEMP_FILES;
TABLESPACE_NAME
---------------
TEMP

SQL>alter tablespace temp offline;
alter tablespace temp offline
*
Error at line 1;
ORA-12905:default temporary tablepspace cannot be offline

SQL>drop tablespace temp including contents and datafiles;
drop tablespace TEMP including contents and datafiles
*
Error at line2:
ORA-12906:cannot drop default Temporary tablespace

In order to solve this problem you must assign database default tenporary tablespace to a new one. To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.

To create a new one,

SQL>create temporary tablespace temp1 tempfile'/opt/nit/temp01/dbf' size 10M;
 Tablespace created.

Make this tablespace to database default temporary tablespace,
SQL>alter database default temporary tablespace temp1;
Database altered.

SQL>drop tablespace temp including contents and datafiles;
Tablespace dropped. 

Read More

Recover or Recreate TEMPORARY Tablespace in Oracle

In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.

Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users.

In order to do that follow the steps here:

1)Find out the temporary datafiles.
 

SQL> select file_name,file_id, tablespace_name from dba_temp_files;

 
FILE_NAME                              FILE_ID     TABLESPACE_NAME
--------------------------------------------- ------------ ------------------------------
/oradata2/temp2.dbf                          1            TEMP01
/oradata2/temp.dbf                            2            TEMP02
/oradata2/temp3.dbf                          4            TEMP03

2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.

3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp tempfile '/opt/nit/temp.dbf' size 10M;
Tablespace created.

SQL> alter database default temporary tablespace temp;
Database altered.

4)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also. 

SQL>select temporary_tablespace,username from dba_users
          where temporary_tablespace<>'TEMP';

TEMPORARY_TABLESPACE           USERNAME
---------------------------------------------  -------------------------
TEMP                                                     US1
TEMP2                                                   US2

5)Explicitly assign temporary tablespace for users US1 and US2.
SQL> alter user us1 temporary tablespace temp;
User altered.

SQL> alter user us2 temporary tablespace temp;
User altered.
6)Drop the old temporary tablespace.

SQL> drop tablespace temp01;
Tablespace dropped.

SQL> drop tablespace temp02;
Tablespace dropped.

SQL> drop tablespace temp03;
Tablespace dropped.

Read More

Space Allocation in a TEMPORARY Tablespace

Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

 SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users.To see the default temporary tablespace for a database:

SQL>SELECT * FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; 

Space Allocation in a TEMPORARY Tablespace

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$SORT_USAGE view identifies the current sort users in those segments.

When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace

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

Benefits Of TEMPORARY Tablespace:

Temporary tablespace group has the following benefits:

• A temporary tablespace allows multiple default temporary tablespaces to be specified at the database level.
• A temporary tablespace allows the user to use multiple temporary tablespaces in different sessions at the same time.
• A temporary tablespace allows a single SQL operation to use multiple temporary tablespaces for sorting.

Read More

TEMPORARY Tablespace And TEMPFILE

TEMPORARY TABLESPACE

 

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.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.If you join two large tables and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT etc.
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.

SQL>CREATE USER NITIN DEFAULT TABLESPACE tbs TEMPORARY TABLESPACE temp;
SQL>ALTER USER NITIN TEMPORARY TABLESPACE TEMP;

Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up:

TEMPFILE

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:

SQL>ALTER TABLESPACE temp ADD TEMPFILE '/opt/nitin/temp.dbf' SIZE 100M;

Read More