Home » RDBMS Server » Performance Tuning » issue in temporary tablespace
icon5.gif  issue in temporary tablespace [message #144744] Fri, 28 October 2005 00:29 Go to next message
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
Re: issue in temporary tablespace [message #144790 is a reply to message #144744] Fri, 28 October 2005 05:11 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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
Re: issue in temporary tablespace [message #144796 is a reply to message #144790] Fri, 28 October 2005 06:48 Go to previous messageGo to next message
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
Re: issue in temporary tablespace [message #144829 is a reply to message #144796] Fri, 28 October 2005 09:53 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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
Re: issue in temporary tablespace [message #144926 is a reply to message #144829] Fri, 28 October 2005 23:02 Go to previous message
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
Previous Topic: can i create a materialized with column aliases.
Next Topic: why AWR don't collect statistics information?
Goto Forum:
  


Current Time: Fri Mar 29 02:53:50 CDT 2024