Oracle Database Discussions

Hi,

Oracle 10g R2
Windows 2003 Server

I started from the following problem:

tablespace USERS had 2 datafiles which got filled up to the (i believe maximum amount of space, because it was autoextend on) it grew up to 33,554,424 KB each file.

I had many errors during night jobs (cannot extend tablespace USERS with 1024 etc.), so in the morning i added a new datafile to this tablespace users.

Problem is that, the server was still freezing and working very slow.
After one CTAS of today and then SELECT COUNT(1) FROM that CTAS table, Oracle threw this error:


ORA-01115: IO error reading block from file 7 (block # 2679725)
ORA-01110: data file 7: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\MV\USERS02.DBF'
ORA-27091: unable to queue I/O
ORA-27070: Message 27070 not found; product=RDBMS; facility=ORA
OSD-04006: Message 4006 not found; No message file for product=RDBMS, facility=SOSD
O/S-Error: (OS 2) The system cannot find the file specified.
01115. 00000 - "IO error reading block from file %s (block # %s)"
*Cause: Device on which the file resides is probably offline
*Action: Restore access to the device


I then thought that i'd better move some objects to a new tablespace with new datafile (there is plenty of space available on server).

What i did:
a) ALTER TABLESPACE users OFFLINE;

Then server worked fine, and i was able to operate on database. Then (while tablespace was still offline):
0. Created new tablespace and new datafile (set this one to be default permanent tablespace);
1. I cleaned the UNDOTBS1 (was about 32Gb), --> resized it to 6Gb
2. Cleaned the TEMPORARY tablespace (was 99,9% full about 15G) --> resized it to 3Gb.
3. Dropped purge different tables from USERS that i considered to be no longer useful, and were taking a lot of space. I also dropped some indexes.
4. then PURGE DBA_RECYCLEBIN;
5. Prepared scripts ALTER TABLE table MOVE TABLESPACE new_tablespace PARALLEL;

And i wanted to take the tablespace back online, so to execute scripts from #5.

b) ALTER TABLESPACE users ONLINE;

After executing this command:
- in sqlplus i got an error: ERROR: Line 1 [kbkq] [0] etc.. (i didn't record this error, and closed the command prompt by mistake).
- and again, if i ran the same command (after having restarted the database) it just keeps running and running, and nothing happens
- i also tried to launch the command from enterprise manager --> same issue it just runs, and runs...

There is no message in alert.log

How should i bring that tablespace back online?

Thank you very much,
Mihai