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

advertisements

_____________________________________________________________________________________________________________________

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;

TABLESPACE_NAME                ALLOCATEDGB     FREEGB TABLESPACEGB

------------------------------ ----------- ---------- ------------

TEMP                                    14 13.9980469           14

2. SQL Command to switch off the autoextend option for tempfile

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

3. SQL command to resize a tempfile

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

4. SQL Command to shrink temporary tablespace with minimum size

Alter tablespace temp shrink space keep 1g;

5. SQL Command to shrink tempfile

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

6. SQL Command to shrink temporary tablespace without minimum limit.

Alter tablespace temp shrink space;

_____________________________________________________________________________________________________________________

Live Traffic Feed

A visitor from Chennai viewed 'Shell Script to Run RMAN Backup in Background with' 12 mins ago

A visitor from Qostanay viewed 'How to Find out / Check SGA and PGA size of Oracle' 47 mins ago

A visitor from Toronto viewed 'Purpose of ORAENV_ASK=NO / YES variable in Oracle' 51 mins ago

A visitor from Brisbane viewed 'Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPL' 3 hrs 6 mins ago

A visitor from Roebling viewed 'Oracle relink all command failed with ld: 0706-006' 3 hrs 58 mins ago

Real-time | Get Script | More Info