Skip to main content

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g
1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

Comments

Post a Comment

Popular posts from this blog

EBS Standby Role Tranistion using standby database and standby application Tier

 Role Transitions A database can operate in either a primary or standby role - these roles are mutually exclusive. Oracle Data Guard enables you to change these roles dynamically by issuing SQL commands, and supports the following transitions: Switchover Allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Oracle Data Guard configuration with its new role. Failover Changes a standby database to the primary role in response to a primary database failure. The following role transitions are discussed: 6.1 Performing a Switchover 6.2 Performing a Failover 6.3 Performing a Switchback to the Primary Following A Switchover/Failover Each of these three transitions requires some application configuration to be performed. Most of the application configuration step...
1 comment
Read more

How To Create LVM Using vgcreate in Linux

Logical Volume Management (LVM) creates a layer of abstraction over physical storage, allowing you to create logical storage volumes. With LVM in place, you are not bothered with physical disk sizes because the hardware storage is hidden from the software so it can be resized and moved without stopping applications or unmounting file systems. You can think of LVM as dynamic partitions. For example, if you are running out of disk space on your server, you can just add another disk and extend the logical volume on the fly. Below are some advantages of using Logical volumes over using physical storage directly: •Resize storage pools: You can extend the logical space as well as reduce it without reformatting the disks. •Flexible storage capacity: You can add more space by adding more disks and adding them to the pool of physical storage, thus you have a flexible storage capacity. •Use of striped, mirrored and snapshot volumes: Striped logical volume that stripes data across two or ...
Post a Comment
Read more
TRANSPARENT APPLICATION FAILOVER FOR RAC RAC provides near-continuous availability by hiding failures from end-user clients and application server clients. This provides continuous, uninterrupted data access. Transparent Application Failover (TAF) is what applications use to sync up with Oracle RAC availability. TAF in the database reroutes application clients to an available database node in the cluster when the connected node fails. Application clients do not see error messages describing loss of service. if the users connection to Node 1 dies, their transaction is rolled back but they can continue work without having to manually reconnect. To get a good understanding of how the TAF architecture works, it is helpful to walk through a failover scenario where a user is querying the database to retrieve 1000 rows from the database. Assume that the user is connected to Node 1/ Instance 1. 1.      The heartbeat mechanism between the various nodes...
Post a Comment
Read more