Any way to prevent oracle from try to allocate space from offine datafile?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Any way to prevent oracle from try to allocate space from offine datafile?

  1. #1

    Unhappy

    platform:
    windows2000 advanced server PII450+1Gmem
    database server:
    Oracle 8.1.5 EE, no patchset.

    Error :
    1. tablespace data_tbl2 has 25G bytes of datafiles.
    2. when i was away, some operator added two datafiles into this tablespace, and found it was too big, offline this datafile(IN the **** DBA studio), not resize the datafile
    3. and removed the old archivelog files in the second evening.
    4. now oracle try to allocate extent from this offline tables, and get error message.
    5. My current steps: once error message appears, i move the table or rebuild the index.
    My question:
    Is there any way to prevent oracle from allocate space from the offline datafile, and without rebuild the whole tablespace?
    rebuild it takes too much downtime, and move all the tables will also use too much time and resource.
    Thanks.
    www.cnoug.org

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    in off hours, put the datafile back online, re-size it to something minimal, like 16K, or as small as you can. Turn off autoextensibility (if it is on). Then forget about it. It will be online but with no usable free space it won't be used.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi,

    If you have only indexes in you can rebuild the online if you are running on 8i.

    You only need the extra disk space. If you hav got a mix of tables and indexes in this tablespace create two new tablespaces and and move a table to one and rebuild its indexes to the other one. And keep it like that.

    Get rid of the tablespace with the dropped datafile asap. I had a problem once couldn't upgrade because of a dropped datafile.

    Tycho

  4. #4
    Originally posted by jrpm
    in off hours, put the datafile back online, re-size it to something minimal, like 16K, or as small as you can. Turn off autoextensibility (if it is on). Then forget about it. It will be online but with no usable free space it won't be used.
    hi, because the archivelog file is missing so unable to online it.
    If i can online it, then everything is ok.
    Maybe the only choice is rebuild the whole tablespace? sigh.
    www.cnoug.org

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