Temporary tablespace group

What We Mean By That ?

A temporary tablespace group consists of only temporary tablespace, and has the following properties:

      • It contains one or more temporary tablespaces.
      • It contains only temporary tablespace.
      • It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group. 

Benefits :

Temporary tablespace group has the following benefits:

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

How to Know How Much Group you have And Each temp assign to them ? 

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

 How to Create One :

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
TABLESPACE GROUP groupaA_temp;
 
Note : you can create temp tablespace without assign to any group .  


Example 

     CREATE TEMPORARY TABLESPACE temp04
TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
TABLESPACE GROUP ‘’;

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G;
 
Using Alter with them 

Remove from the group : 

 ALTER TABLESPACE temp01 TABLESPACE GROUP ‘‘;

Assign to Group :

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Define Group for specific user :

     ALTER USER Osama TEMPORARY TABLESPACE tempgroup_A;

 Refecnce Link :
1-ORACLE BASE
2-DBA Kevlar

Enjoy
Osama Mustafa

Check Your Database Size

Datafiles : 

select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;

Tempfiles:

select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;

Your redo logs can also use up a large amount of disk space – especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).

Redologs:

select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database.  If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

Controlfiles:


select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.

Flash Recovery Area:


select * from v$recovery_file_dest; 
select * from v$flash_recovery_area_usage;  

These views will show sizing details and free space available.

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file


select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;

Files referenced by database directories or the utl_file_dir parameter:


Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables –

select a.owner||’.’||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;

(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;

Thank you 
osama Mustafa

How to check default temporary tablespace

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like ‘%TABLESPACE’;

short topic but hope it will be useful
Thank you
Osama Mustafa