Database Administration (MOSC)

MOSC Banner

Lock on Windows datafile after dropping TEMP tablespace

edited Feb 27, 2019 9:52AM in Database Administration (MOSC) 4 commentsAnswered ✓

Hi all,

I recreated our temporary tablespace on a Windows 2012 server with the following steps:

CREATE TEMPORARY TABLESPACE TEMP99 TEMPFILE 'D:\ORADATA\TESTDB\TEMP9901.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 16777216 MAXSIZE 8192M;

alter user testuser1 temporary tablespace temp99;

alter user testuser2 temporary tablespace temp99;

alter database default temporary tablespace temp99;

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;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\ORADATA\TESTDB\TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 16777216 MAXSIZE 8192M;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center