While under massive write (insert and create table as) load, when Oracle RAC needs to extend tablespace, all write activity gets blocked for however long it takes it to finish extending (tens of minutes), with all sessions blocked by "data file init write". Is there way to configure Oracle to extend the file proactively so that things can keep moving while it is doing it?
Oracle tablespace extending
oracleoracle-11g-r2rac
Related Question
- Can’t drop UNDO TABLESPACE
- Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem
- Oracle selective redo log shipping
- Oracle – Restrict User to Use USERS Tablespace
- Oracle 12c Installation – Troubleshooting on Windows 8
- Oracle 11gR2 can I delete datafiles to reduce tablespace
Best Answer
30min for 1TB is quite normal. That all writing stops is also normal if your tablespace completely run out of space. When writes has nowhere to go they have to wait for the RESIZE operation to complete. If one extends datafile while there is still space in it database I/O does not stop.
Just why you extended for such huge amount? Now those +1TB will add to your RMAN backup. Of course they will compress well but still not 100% and RMAN will need time to read all those blocks.
I would setup autoextend on those tablespaces:
ALTER DATABASE DATAFILE '+DG/sid/datafile/datafile_01.dbf' AUTOEXTEND ON NEXT 10G MAXSIZE 30GThen you would not get wasted space in tablespace and also even in case your data grows fast and catches up with autoextend operation - there will be only small delay in writes because 10GB will not take a long time to format.
And Oracle will autoextend tablespace proactively. It will not wait for the last blocks to fill up.