—- 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;
Leave a comment
Comments feed for this article