• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How To Shrink A Temporary Tablespace in Oracle Database

By admin

Question: How to resize the TEMPFILE(s) for a temporary tablespace after they have grown larger than needed?

Large sort operations can cause temporary tablespaces to grow very large and as such there may be a need to ‘downsize’ after such operations. Until Oracle 11g there was no SQL command to release the unused allocated temporary space. One workaround for this problem is to create a new empty temporary tablespace with a smaller size, assign this new tablespace to the users and then drop the old tablespace. The disadvantage of this procedure is that it requires that no active sort operations are happening within the old temporary tablespace while it is being dropped.

In Oracle 11g, a new feature was added that can be used to shrink temporary tablespaces. This command can be used to shrink only locally managed temporary tablespaces :

ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

The SHRINK SPACE clause allows a user to shrink a temporary tablespace, whereas SHRINK TEMPFILE allows the shrink of a temporary file.

The optional KEEP clause defines the lower bound that a tablespace can be shrunk to. It is the opposite for MAXSIZE for auto extensible tablespace. If it is not specified, the system will try to shrink as much as possible, as long as the other storage attributes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option.

Filed Under: oracle

Some more articles you might also be interested in …

  1. ASM Fast Mirror Resync Feature – Example To Simulate Transient Disk Failure And Restore Disk
  2. Oracle Automatic Storage Management (ASM) concepts
  3. Oracle 11G RMAN – Understanding UNDO backup optimisation
  4. How to relocate or move oracle database files using RMAN
  5. Oracle SQL script to Show current Users and SQL being Executed
  6. How to Identify Different File types and space used in Flash Recovery Area
  7. What is OCFS or OCFS2
  8. Oracle Database: How to catalog tape backup pieces
  9. Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes
  10. How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm

You May Also Like

Primary Sidebar

Recent Posts

  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Basics of client connectivity in Oracle Data Guard configuration
  • ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary