SQL> ALTER TABLESPACE <tablespace_name> ADD TEMPFILE '<path_and_file_name>' SIZE <n>M;
Example:
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ORCL10G/temp02.dbf' SIZE 1000M;
Tablespace altered.
SQL>
Use the query below to check the tempfile location and usage:
-- Check Temp Tablespace Usage + Exact Temp File Location
set lines 300
COLUMN mb_total HEADING 'Total|MB' FORMAT 99,999;
COLUMN mb_used HEADING 'Used|MB' FORMAT 99,999;
COLUMN mb_free HEADING 'Free|MB' FORMAT 99,999;
column FILE_NAME Heading 'File|Name' format a40;
column tablespace_name Heading 'Tablespace|Name' format a10;
column TABLESPACE Heading 'Tablespace|Name' format a10;
column MB Heading 'Allocated|MB' FORMAT 99,999;
SELECT dbatf.FILE_NAME, dbatf.BYTES/1024/1024 MB, A.tablespace_name TABLESPACE,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM dba_temp_files dbatf, v$sort_segment A,
(SELECT B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B,
v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name,
C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
D.mb_total, dbatf.FILE_NAME, dbatf.BYTES/1024/1024
/
SQL> -- Check Temp Tablespace Usage + Exact Temp File Location
set lines 300
SQL> SQL> COLUMN mb_total HEADING 'Total|MB' FORMAT 99,999;
SQL> COLUMN mb_used HEADING 'Used|MB' FORMAT 99,999;
SQL> COLUMN mb_free HEADING 'Free|MB' FORMAT 99,999;
SQL> column FILE_NAME Heading 'File|Name' format a40;
SQL> column tablespace_name Heading 'Tablespace|Name' format a10;
SQL> column TABLESPACE Heading 'Tablespace|Name' format a10;
SQL> column MB Heading 'Allocated|MB' FORMAT 99,999;
SELECT dbatf.FILE_NAME, dbatf.BYTES/1024/1024 MB, A.tablespace_name TABLESPACE,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
SQL> 2 3 4 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM dba_temp_files dbatf, v$sort_segment A,
5 6 (SELECT B.name,
7 C.block_size,
8 SUM (C.bytes) / 1024 / 1024 mb_total
9 FROM v$tablespace B,
10 v$tempfile C
11 WHERE B.ts#= C.ts#
12 GROUP BY B.name,
13 C.block_size
14 ) D
15 WHERE A.tablespace_name = D.name
16 GROUP BY A.tablespace_name,
17 D.mb_total, dbatf.FILE_NAME, dbatf.BYTES/1024/1024
18 /
File Allocated Tablespace Total Used Free
Name MB Name MB MB MB
---------------------------------------- --------- ---------- ------- ------- -------
/u01/oradata/ORCL10G/temp02.dbf 10 TEMP 39 0 39
/u01/oradata/ORCL10G/temp01.dbf 29 TEMP 39 0 39
SQL>