DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: TABLESPACE (7.3.3)

  1. #1
    Join Date
    Oct 2000
    Posts
    57
    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.

  2. #2
    Join Date
    Aug 2000
    Location
    York - England
    Posts
    33
    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
    :p :p :p

  3. #3
    Join Date
    Sep 2000
    Posts
    128
    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

  4. #4
    Join Date
    Oct 2000
    Posts
    57

    Cool

    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

  5. #5
    Join Date
    Sep 2000
    Posts
    128
    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 ))
    /

  6. #6
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    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

  7. #7
    Join Date
    Oct 2000
    Posts
    57
    Terry, "No rows selected" , so no need to worry. Thanx for your help.

  8. #8
    Join Date
    Aug 2000
    Location
    York - England
    Posts
    33
    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
    :p :p :p

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width