windows2000 advanced server PII450+1Gmem
Oracle 8.1.5 EE, no patchset.
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.
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.
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'
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.
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.