Oracle 11G SQL Command Syntax to Check Free Space, Shrink Temporary Tablespace or Tempfile


The Eucharistic Miracles of the World
  1. SQL Command to Check Free Space in Temporary table space.

SQL> select tablespace_name ,ALLOCATED_SPACE/1024/1024/1024 allocatedGB,    FREE_SPACE/1024/1024/1024 FreeGB, TABLESPACE_SIZE/1024/1024/1024 TABLESPACEGB   from dba_temp_free_space;

------------------------------ ----------- ---------- ------------
TEMP                                    14 13.9980469           14
  1. SQL Command to switch off the autoextend option for tempfile

alter database tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf' autoextend off;

  1. SQL command to resize a tempfile

alter database tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf' resize 14g;

  1. SQL Command to shrink temporary tablespace with minimum size
Alter tablespace temp shrink space keep 1g;

  1. SQL Command to shrink tempfile
Alter tablespace temp shrink tempfile '/u01/app/oracle/oradata/testdb/temp01.dbf' keep 1g;

  1. SQL Command to shrink temporary tablespace without minimum limit.
Alter tablespace temp shrink space;


Website Stats


Post a Comment


Oracle (580) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer