• 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. How to change static parameters through SPFILE parameter file in Oracle Database
  2. How to Move OCR, Vote Disk File, ASM SPILE to new Diskgroup
  3. Oracle Database – How to recover from a lost datafile with no backup
  4. How Realms Work in Oracle Database Vault
  5. How to Switch to a New Undo Tablespace in Oracle Database
  6. Oracle SQL Script to Detect Tablespace Fragmentation
  7. Oracle Database : How to set Environment Variables Using Srvctl
  8. Oracle ASM 11gR2 instance is unable to start due to missing ASM spfile
  9. How To Add New Disk to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
  10. IPv4 Subnet Basics and Oracle Clusterware

You May Also Like

Primary Sidebar

Recent Posts

  • Failed to start LSB: Bring up/down networking – On restarting network service CentOS/RHEL (DHCP client)
  • How To Add Timestamps To dmesg Kernel Boot Log in CentOS/RHEL
  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary