OraFAQ Forum: General » Reg.. Temp Table Space....

Home » Other » General » Reg.. Temp Table Space....

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

Reg.. Temp Table Space.... [message #103500]

Sun, 23 November 2003 03:32

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?

Report message to a moderator

Re: Reg.. Temp Table Space.... [message #103502 is a reply to message #103500]

Thu, 27 November 2003 07:31

Frank Naude
Messages: 4581
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

Report message to a moderator

Re: Reg. Temp Table Space. [message #103938 is a reply to message #103502]

Thu, 27 May 2004 05:00

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;

Report message to a moderator

Previous Topic:	Pocket PC 2003
Next Topic:	beginner

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 06:30:24 CST 2024