While creating temporary tablespace one can create it by using datafile or tempfile but there is a big difference in performance using temp file will give you better performance since it is autimatically managed by oracle, once temp segment is inactive and nolonger in use oracle mark it that space available to others so it can be used by others. But when you use datafiles for temp tablespace smon has to drop inactive segments manually to release the space this is big overhead smon invokes to remove inactive temp segments periodically it may take upto 12 hrs of time so it unneccessarily raises requirement for bigger temporary tablespace. So better to always use tempfiles for temporary tablespaces

- You can find out using the following dictionray views
DBA_TABLES.temporary, DBA_SEGMENTS.segment_type,V$TEMPFILE, DBA_TEMP_FILES

- select * from dba_segments where segment_type = ‘TEMPORARY’;

- What is temporary segments or temporary tables

– other sessions can see table definition but not data
– data truncated when transaction/session (commit/rollback/exit) ends
– sessions get bound to temporary tables with the first insert into the table
– segments allocated to table at insert time not create time like normal tables

– creating
– session specific:
CREATE GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS;
– transaction specific:
CREATE GLOBAL TEMPORARY TABLE ON COMMIT DELETE ROWS;

– example:
CREATE GLOBAL TEMPORARY TABLE emp_temp
(emp_name VARCHAR(60) NOT NULL,
emp_id NUMBER(4) NOT NULL,
CONSTRAINT emp_temp_pk PRIMARY KEY (emp_id)) ON COMMIT PRESERVE ROWS;

INSERT INTO emp_temp (emp_name, emp_id) values(‘Mudhalvan’,1);

COMMIT;

SQL> create table test ( msg varchar2(4000) );

Table created.

SQL> create or replace trigger failed_to_extend_temp
after servererror on database
declare
l_sql_text ora_name_list_t;
l_n        number;
begin
if ( is_servererror(1652) )
then
insert into test values ( ‘ora_sysevent = ‘ || ora_sysevent );
insert into test values ( ‘ora_login_user = ‘ || ora_login_user );
insert into test values ( ‘ora_server_error = ‘ || ora_server_error(1) );
l_n := ora_sql_txt( l_sql_text );
for i in 1 .. l_n
loop
insert into test values ( ‘l_sql_text(‘ || i || ‘) = ‘ || l_sql_text(i) );
end loop;
end if;
end;

Trigger created.

SQL> create temporary tablespace test_temp tempfile ‘/tmp/test_temp.dbf’ size 512k reuse extent management local uniform size 64k;

Tablespace created.

SQL> create user temptest identified by temptest;

User created.

SQL> grant connect,resource to temptest;

Grant succeeded.

SQL>

SQL> alter user temptest temporary tablespace test_temp;

User altered.

SQL> conn temptest/temptest
Connected.
SQL>

SQL> select * from all_objects order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12;

SQL> select * from test;

SQL> drop table test;

SQL> ALTER DATABASE TEMPFILE ‘/tmp/test_temp.dbf’ DROP INCLUDING DATAFILES;

SQL> DROP TABLESPACE test_temp INCLUDING CONTENTS;

SQL> DROP TRIGGER failed_to_extend_temp;

—- 1.  Check the Existing Temp Tablespace Size and Data file List
col FILE_NAME       format a50
col TABLESPACE_NAME format a15
col BYTES           format 999,999,999,999
col MAXBYTES        format 999,999,999,999
set linesize 1000

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                 601,882,624   34,359,721,984

SQL>

—- 2. Reduce the Temp Tablespace Size

alter database tempfile ‘/u01/app/oracle/oradata/ORATEST/temp01.dbf’ resize 50M;

SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORATEST/temp01.dbf’ resize 50M;

Database altered.

SQL>

—- 3. Add New Data file to the Temp Tablespace

alter tablespace TEMP add tempfile ‘/u01/app/oracle/oradata/ORATEST/temp02.dbf’ size 50M;

SQL> alter tablespace TEMP add tempfile ‘/u01/app/oracle/oradata/ORATEST/temp02.dbf’ size 50M;

Tablespace altered.

SQL>

—- 4. Check the Temp Tablespace Information (Data file & Size)

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                  52,428,800   34,359,721,984
/u01/app/oracle/oradata/ORATEST/temp02.dbf         TEMP                  52,428,800                0

SQL>

—- 5. Switch off AutoExtend off on First Datafile

SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORATEST/temp01.dbf’ AUTOEXTEND OFF;

Database altered.

SQL>

—- 6. Check the Temp Tablespace Information (Data file & Size)
SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                  52,428,800                0
/u01/app/oracle/oradata/ORATEST/temp02.dbf         TEMP                  52,428,800                0

SQL>

—- 7. Check the Temp Datafile Usage

SQL 1.
SELECT   SUM (u.blocks * blk.block_size) / 1024 / 1024 “Mb. in sort segments”,
(hwm.MAX * blk.block_size) / 1024 / 1024 “Mb. High Water Mark”
FROM   v$sort_usage u,
(SELECT   block_size
FROM   dba_tablespaces
WHERE   contents = ‘TEMPORARY’) blk,
(SELECT   segblk# + blocks MAX
FROM   v$sort_usage
WHERE   segblk# = (SELECT   MAX (segblk#) FROM v$sort_usage)) hwm
GROUP BY   hwm.MAX * blk.block_size / 1024 / 1024;
SQL 2.

SQL> select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment;

SQL> SELECT tablespace_name, file_id, block_id, BYTES/1024/1024, owner FROM v$temp_extent_map

SQL> SELECT s.username, s.sid,  u.TABLESPACE, u.CONTENTS, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr;

—– 8. Reference Syntex

ALTER DATABASE DATAFILE <’data_file_name’ | data_file_number>
AUTOEXTEND <OFF | ON [NEXT SIZE <n>K|M|G|T|P|E
MAXSIZE <UNLIMITED | <n>K|M|G|T|P|E>;

ALTER DATABASE DATAFILE ‘u06/oradata/tools01.dbf’ AUTOEXTEND OFF;

ALTER DATABASE DATAFILE <datafile name with complete path> OFFLINE DROP;

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

col FILE_NAME       format a50
col TABLESPACE_NAME format a15
col BYTES           format 999,999,999,999
col MAXBYTES        format 999,999,999,999
set linesize 1000

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                 601,882,624   34,359,721,984