Drop temp tablespace taking long time

Drop temp tablespace taking long time

Created new temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE ‘/u01/oracle/oradata/temp02.dbf’ SIZE 2g;

Make new temporary tablespace as default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Dropping old temporary tablespace, Which is taking long time to complete:

DROP TABLESPACE TEMP INCLDING CONTENTS AND DATAFILES;

The issue because of temp is being used by oracle sessions

SOLUTION 1:

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
SQL>SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
SQL>DROP TABLEPSACE TEMP INCLUDIING CONTENTS AND DATAFILES;

SOLUTION 2:

Run below query and see any sessions using temp, Find out the all the sessions that are not active and kill those sessions.

select  tu.username,s.sid,s.serial# from v$tempseg_usage tu, v$session s
where tu.session_addr=s.saddr;

alter system kill session ‘sid,serial’;

Thank You !!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s