Printable View
I keep having to add tablespace to an application to keep it running, I keep adding in 100m lumps. I have DATA01 , 100M 99% used, DATA02 100M 92% used, DATA03 100M 70% used. When I added DATA03 it seemed to start using DATA03 immediately rather than fill up DATA02 to 99%.
I will have to add DATA04 shortly so will I have to wait until DATA03 IS UP TO 99% before adding DATA04??
Hope this makes sense, thanx in advance.
Mmmm
It would be better to resize datafiles rather than add them if you have the space
What puzzles me is why is it filling up so quick
Is the application that heavy, are there a high number of users
Where are you getting the 99% reading from
Cheers
Although there my be 30% free space, you don't say how it is fragmented.
For example - say you have 30Mb consisting of 25 1Mb chunks (not contiguous) and 1 5Mb chunk. If say you have a table which needs a next extent of 10Mb it will not be able to allocate it in this file so will choose another.
Try running:
col Total format 9,999.99
col Max format 9,999.99
col Min format 9,999.99
col tablespace_name format a15
select tablespace_name, sum(bytes)/1024/1024 "Total", max(bytes)/1024/1024 "Max",
min(bytes)/1024/1024 "Min", count(*) "Count"
from dba_free_space
group by tablespace_name
/
Instead of continually adding 100Mb files, would it not be better to add bigger files less frequently?
If you're not expecting it to grow, have you made sure that you don't have anyone's temporary tablespace allocated to this tablespace?
select username, temporary_tablespace from dba_users
where tempoary_tablespace <> 'NAME OF YOUR TEMP SPACE'
/
Are you only adding this space when you receive errors? If so which error?
Basically, Oracle will go back and use that space at some point if it can, so it would be bad to wait until it's at 99% before adding a file, since you run the risk of your application failing due to space.
Terry
Slaine, Its not filling up so quick, this has happened over several months, also I didnt think once you have assigned an extra data file you could to change the size of it afterwards.
TerryD, This is the result of the query you suggested, I agree I could add 200 or 300 mb or even bigger at a time,
TABLESPACE_NAME Total Max Min Count
--------------- --------- --------- --------- ---------
DATA 36.74 29.32 .08 3
INDEXES 20.00 20.00 20.00 1
ROLLBACK 38.07 38.07 38.07 1
SYSTEM 86.35 86.35 86.35 1
TEMP 49.74 21.85 6.26 3
run this - if this returns any rows for DATA tablespace, then that will be the reason that it chooses the new file - otherwise it's just Oracles space management and nothing to worry about:
ttitle 'Database Objects that will have Trouble Throwing Extents'
prompt
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
t.next_extent/1024/1024
FROM sys.dba_segments seg,
sys.dba_tables t
WHERE (seg.segment_type = 'TABLE'
AND seg.segment_name = t.table_name
AND seg.owner = t.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = t.tablespace_name
and bytes >= t.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'CLUSTER', c.next_extent)
FROM sys.dba_segments seg,
sys.dba_clusters c
WHERE (seg.segment_type = 'CLUSTER'
AND seg.segment_name = c.cluster_name
AND seg.owner = c.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = c.tablespace_name
and bytes >= c.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'INDEX', i.next_extent )
FROM sys.dba_segments seg,
sys.dba_indexes i
WHERE (seg.segment_type = 'INDEX'
AND seg.segment_name = i.index_name
AND seg.owner = i.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = i.tablespace_name
and bytes >= i.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'ROLLBACK', r.next_extent)
FROM sys.dba_segments seg,
sys.dba_rollback_segs r
where (seg.segment_type = 'ROLLBACK'
AND seg.segment_name = r.segment_name
AND seg.owner = r.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = r.tablespace_name
and bytes >= r.next_extent ))
/
what is your platform?
I believe it is only if you're running Oracle on Mainframe that you cannot resize a datafile after its creation.
Also, is there a reason why you must add at 100M intervals?
Why not 200M or 300M?
Either way, Definitely take Terry's advice.
maachan
Terry, "No rows selected" , so no need to worry. Thanx for your help.
I have never worked on a mainframe so I can't comment on whether a tablespace can be resized or not
In UNIX or NT a datafile can be resized by using the ALTER DATABASE DATAFILE ...... command
It is better in some respects to have fewer datafiles, depending upon the usage of your system
Cheers