Working an object that resides on multiple datafile

An object can span in multiple datafiles within a single tablespace. In this case if I make one datafile offline then the contents within that datafile will be affected, other datafile is online and can be possible to query which may return rows with errors. To illustarte this scenario I will make two datafiles inside a tablespace.

1)Create Tablespace.

SQL> CREATE TABLESPACE TEST_TBS DATAFILE '/oradata2/test_tbs01.dbf' SIZE 100K;
Tablespace created.

2)Add datafile to the Tablespace.

SQL> ALTER TABLESPACE TEST_TBS ADD DATAFILE '/oradata2/test_tbs02.dbf' SIZE 1M;
Tablespace altered.

3)Create Table inside the Tablespace.

SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_TBS AS SELECT LEVEL B1 FROM DUAL CONNECT BY LEVEL<9999;
Table created.

4)Check which datafile this table belong to.

SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_TABLE');

TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEST_TBS /oradata2/test_tbs01.dbf
TEST_TBS /oradata2/test_tbs02.dbf

5)Query the table it will be ok.Now make later datafile offline and query from table.

SQL> alter database datafile '/oradata2/test_tbs02.dbf' OFFLINE;
Database altered.

SQL> select * from TEST_TABLE;

1
.
.
3270
ERROR:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'
3270 rows selected.