Reg.. Temp Table Space.... [message #103500] |
Sun, 23 November 2003 03:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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|ID'
column spid format 9,999 heading 'Unix|ID'
column segblk# format 999,999,999 heading 'Block|ID'
column size_mb format 999,999,990.00 heading "Mbytes|Used"
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;
|
|
|