advertisements
_____________________________________________________________________________________________________________________
The temporary tablespace will increase in size depends upon the amount of sorting happens on the database and it occupies the most of the space in the disk. Sorting might be happening occasionally and for that purpose there is no need of keeping huge amount of temporary tablespace. As and when needed we can increase the temp tablespace size.
Until Oracle 11g there was no command to unallocated or reduce the temporary tablespace size. One workaround used was create a new temporary tablespace smaller in size and allocate this new tablespace to the users and drop the old temporary tablespace. The disadvantage of this method is no sorting operations are permitted during the tablespace being dropped.
In Oracle 11g there is one SQL command to shrink the temporary tablespace which is applicable only for locally managed temporary tablespace.
Command Syntax:
ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];
Method 1: Shrink Space
select file_name, bytes/1024/1024/1024 from dba_temp_files
SQL>
SQL> /
FILE_NAME BYTES/1024/1024/1024
---------------------------------------- --------------------
/data/oracle/oradata/prod9/temp01.dbf 3.671875
1 select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
2* FROM DBA_TEMP_FREE_SPACE
SQL> /
TABLESPACE_NAME TOTAL ALLOCATED FREE
------------------------------ ---------- ---------- ----------
TEMP 3.671875 3.015625 3.67089844
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 1028M; à Keep Keyword is optional
Tablespace altered.
How to Check TEMP tablespace usage? DBA_TEMP_FREE_SPACE
SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
2 FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TOTAL ALLOCATED FREE
------------------------------ ---------- ---------- ----------
TEMP 1.00488281 .000976563 1.00390625
ls -ltr /data/oracle/oradata/prod9/temp01.dbf
-rw-r----- 1 oracle dba 1078992896 Jul 7 22:16 /data/oracle/oradata/prod9/temp01.dbf
How to resize a temp file?
SQL> alter database tempfile '/data/oracle/oradata/prod9/temp01.dbf' resize 3760M;
Database altered.
SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
2 FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TOTAL ALLOCATED FREE
------------------------------ ---------- ---------- ----------
TEMP 3.671875 .000976563 3.67089844
Method 2: Shrink Tempfile
SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
2 FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TOTAL ALLOCATED FREE
------------------------------ ---------- ---------- ----------
TEMP 3.671875 .000976563 3.67089844
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/data/oracle/oradata/prod9/temp01.dbf' KEEP 1028M;
Tablespace altered.
SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
2 FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TOTAL ALLOCATED FREE
------------------------------ ---------- ---------- ----------
TEMP 1.00487518 .000968933 1.00390625
SQL> !ls -ltr /data/oracle/oradata/prod9/temp01.dbf
-rw-r----- 1 oracle dba 1078984704 Jul 7 22:31 /data/oracle/oradata/prod9/temp01.dbf
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment