Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trying TEMP file shrink method locks PLSQL session

Fred1018Jul 8 2011 — edited Jul 8 2011
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
This post has been answered by clcarter on Jul 8 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 5 2011
Added on Jul 8 2011
3 comments
330 views