Home » Other » General » Reg.. Temp Table Space....
Reg.. Temp Table Space.... [message #103500] Sun, 23 November 2003 03:32 Go to next message
Anbu
Messages: 6
Registered: August 2000
Junior Member
Hi,
I faced a problem in temp tablespace. In My System,
D: Drive has 8 GB Free space where Oracle has been installed. I have stored a lot of data in my DB. With in one to two hour , the entire 8 GB space occupied by TEMP tablespace , so i need to create a new temp tablespace and to delete the existing temp tablespace.. each ane evry two houur while working ..
Is there ia any way to recreate the new temp automatucally when the temp tablespace reaches some maximum level.... (or) IS there any alternate way to manage it?
Re: Reg.. Temp Table Space.... [message #103502 is a reply to message #103500] Thu, 27 November 2003 07:31 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Although the TEMP tablespace might look 100% full it is not necessarily so. Look at the V$SORT_SEGMENT view to see how many "Free Blocks" you have in the "Sort Segment":

SQL> SELECT TABLESPACE_NAME, CURRENT_USERS, USED_BLOCKS, FREE_BLOCKS
  2  FROM   V$SORT_SEGMENT
  3  /

TABLESPACE_NAME                 CURRENT_USERS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------- ----------- -----------
TEMP                                        0           0      250880


Best regards.

Frank
Re: Reg. Temp Table Space. [message #103938 is a reply to message #103502] Thu, 27 May 2004 05:00 Go to previous message
rjh
Messages: 1
Registered: May 2004
Junior Member
-- hi, here's a script to see who is using TEMP space:

set pagesize 10000
set linesize 133
column tablespace format a15 heading 'Tablespace Name'
column segfile# format 9,999 heading 'File&#124ID'
column spid format 9,999 heading 'Unix&#124ID'
column segblk# format 999,999,999 heading 'Block&#124ID'
column size_mb format 999,999,990.00 heading "Mbytes&#124Used"
column sid format 999
column serial# format 99999
column username format a15
column program format a35

select b.tablespace,b.segfile#,b.segblk#,round(((b.blocks*p.value)/1024/1024),2) size_mb
,a.sid,a.serial#,a.username,a.program,a.status
from v$session a ,v$sort_usage b ,v$process c ,v$parameter p
where p.name='db_block_size'
and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Previous Topic: Pocket PC 2003
Next Topic: beginner
Goto Forum:
  


Current Time: Thu Mar 28 08:47:29 CDT 2024