Trying TEMP file shrink method locks PLSQL session
Hi everyone,
I work on a limited dev server, and had the surprise of a huge TEMP.DBF file after a crampy query.
I'd like to shrink it, but did not find any dqtqbqse statement for it.
After several tries, the only method I can figure out reading from several forums is to delete/create the temp tablespace and file.
Using this script :
CREATE TEMPORARY TABLESPACE temp0
TEMPFILE 'H:\oraclexe\oradata\XE\temp0.dbf' SIZE 256M REUSE
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp0;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'H:\oraclexe\oradata\XE\temp.dbf' SIZE 256M REUSE
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp0 INCLUDING CONTENTS AND DATAFILES;
The step DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; locks my session, and I have to quit with a Ctrl-C.
Any idea how to solve this ?
Thanks.
Fred