OraFAQ Forum: Performance Tuning » issue in temporary tablespace

Home » RDBMS Server » Performance Tuning » issue in temporary tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

issue in temporary tablespace [message #144744]

Fri, 28 October 2005 00:29

deeptimadan
Messages: 17
Registered: October 2005

Junior Member

We have Oracle 8.1.7.0...as our database...we wereusing dictionary managed temporary tablespace ..now i have created a new locally managed temporary tablespace and asigned that tablespace to all of the users...The space usage in DMT should now be 0..but it is showing 157 Mb since the day when i migrated the users from DMT to LMT..why this is so?

I fear that if i try to drop this tablespace ..this will hang..
Please tell me solution for this..

thanks
Deepti

Report message to a moderator

Re: issue in temporary tablespace [message #144790 is a reply to message #144744]

Fri, 28 October 2005 05:11

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Query the dictionary to see what objects are in that tablespace:

SELECT segment_type, owner, segment_name FROM dba_extents
WHERE  tablespace_name = 'OLD_TEMP_TS'

Best regards.

Frank

Report message to a moderator

Re: issue in temporary tablespace [message #144796 is a reply to message #144790]

Fri, 28 October 2005 06:48

deeptimadan
Messages: 17
Registered: October 2005

Junior Member

HI

The result which i got by running this query is:-

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

SEGMENT_TYPE OWNER
------------------ --------
SEGMENT_NAME
---------------------------
TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

TEMPORARY SYS
73.2

Report message to a moderator

Re: issue in temporary tablespace [message #144829 is a reply to message #144796]

Fri, 28 October 2005 09:53

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Some users are still using that tablespace for sorting. Easiest is to restart the DB (to get rid of the users) and drop the old temp tablespace.

Best regards.

Frank

Report message to a moderator

Re: issue in temporary tablespace [message #144926 is a reply to message #144829]

Fri, 28 October 2005 23:02

deeptimadan
Messages: 17
Registered: October 2005

Junior Member

THANK YOU VERY MUCH FOR YOUR HELP...
AS I RESTARTED MY DB IS THE TBS WAS CLEANED UP...

BEST REGARDS
DEEPTI

Report message to a moderator

Previous Topic:	can i create a materialized with column aliases.
Next Topic:	why AWR don't collect statistics information?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 17:48:48 CST 2024