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

The Geek Diary

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

How to Drop Existing Temporary Tablespace and create new in Oracle 11g

by admin

A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because the temporary tablespace doesn’t have any permanent objects stored within it, there is no change in the system change number (SCN) from the checkpoint process in the control file or file header. The database will continue to function normally, with the exception of creating temporary segments, which occurs when creating indexes or performing certain select statements. Because the temporary tablespace only has temporary data stored inside, this tablespace can be re-created and reassigned in the event of data file loss.

In this post, we will see how to create a new TEMP tablespace and drop the existing temporary tablespace.

While performing this activity, the existing temporary tablespace may have some existing live sessions. Due to this, oracle may not let us drop the existing temporary tablespace. Thus, first, we need to kill existing sessions before dropping temporary tablespace.

1. Following query will give you the tablespace name and datafile name along with the path of that data file.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

2. Following query will create temp tablespace named: TEMP_NEW with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

3. Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

4. Retrieve ‘SID_NUMBER’ and ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

5. Provide above inputs to following query, and kill session’s.

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';

For example:

SQL> alter system kill session '59,57391';

6. Now, we can drop old temporary tablespace without any trouble with following:

SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;

Contents and datafiles are deleted successfully. If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow the same step mentioned above to recreate the temp tablespace with old name.

Filed Under: oracle, Oracle 11g

Some more articles you might also be interested in …

  1. Oracle OS watcher (OSWatcher) – Understanding oswiostat
  2. Oracle Dataguard 12c: How to perform Switchover using DGMGRL
  3. Oracle Database 12c New Feature – RMAN “RECOVER TABLE”
  4. How to set custom device names using udev in CentOS/RHEL 7
  5. Understanding Real-Time SQL Monitoring in Oracle 11g
  6. Oracle Interview Questions : Using srvctl V/s sqlplus and pfile V/s spfile in RAC
  7. How to find daily and hourly archive log generation in Oracle Database
  8. Oracle Database : Performing Incomplete Recovery from a missing archivelog file (Change-Based, Cancel-Based, Time-Based)
  9. Oracle Database 18c New Feature – Memoptimized Rowstore
  10. Auto Space Management for Flashback Logs in the Fast Recovery Area – Oracle 19c New Feature

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright