missing datafile

[adewri]

Originally posted by Thomasps
Amar, what will happen to the tablespace and the objects in it..?

Say table A got 3 extents. The latest extent is in the new data file which we are droping.? So what will happened to the table? And the tablespace..?

Regards,

Thomas

The moment you try to hit the block on the missing data file it will throw an error.

Thats one way to bring up the database, but yes if its the only datafile of that tablespace then you need to drop that tablespace as well.

If more than one datafiles belong to that tablespace then you can perform selects and inserts on those tables which do not belong to that dropped datafile. Only those blocks will not be accessible which were on that droppped datafile.

[email protected]D> create tablespace drop_me datafile 'D:\ORACLE\ORADATA\ACME\drop_me.dbf' size 2M;

Tablespace created.

[email protected]D> create table drop_me_tab (x int) tablespace drop_me;

Table created.

[email protected]D> insert into drop_me_tab values(&number);
Enter value for number: 1
old   1: insert into drop_me_tab values(&number)
new   1: insert into drop_me_tab values(1)

1 row created.

[email protected]D> /
Enter value for number: 2
old   1: insert into drop_me_tab values(&number)
new   1: insert into drop_me_tab values(2)

1 row created.

[email protected]D> /
Enter value for number: 3
old   1: insert into drop_me_tab values(&number)
new   1: insert into drop_me_tab values(3)

1 row created.

[email protected]D> /
Enter value for number: 4
old   1: insert into drop_me_tab values(&number)
new   1: insert into drop_me_tab values(4)

1 row created.

[email protected]D> /
Enter value for number: 5
old   1: insert into drop_me_tab values(&number)
new   1: insert into drop_me_tab values(5)

1 row created.

[email protected]D> /
Enter value for number: 6
old   1: insert into drop_me_tab values(&number)
new   1: insert into drop_me_tab values(6)

1 row created.

[email protected]D> commit;

Commit complete.

[email protected]D> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' size 2M;

Tablespace altered.

[email protected]D> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' offline drop;

Database altered.

[email protected]D> select * from drop_me_tab;

         X
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

[email protected]D> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' size 2M;

[email protected]D> select name,file# from v$datafile where name like '%DROP_ME%';

NAME                                          FILE#
---------------------------------------- ----------
D:\ORACLE\ORADATA\ACME\DROP_ME.DBF               30
D:\ORACLE\ORADATA\ACME\DROP_ME01.DBF             31
D:\ORACLE\ORADATA\ACME\DROP_ME02.DBF             32

[email protected]D> declare
  2  x number;
  3  begin
  4  for x in 1..10000 loop
  5  insert into drop_me_tab values (x);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

[email protected]D> commit;

Commit complete.

[email protected]D> select segment_name,tablespace_name,file_id from dba_extents where segment_name like 'DROP_ME%';

SEGMENT_NAME                             TABLESPACE_NAME                   FILE_ID
---------------------------------------- ------------------------------ ----------
DROP_ME_TAB                              DROP_ME                                30
.
.
.
DROP_ME_TAB                              DROP_ME                                30
DROP_ME_TAB                              DROP_ME                                32

17 rows selected.

[email protected]D> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' offline drop;

Database altered.

[email protected]D> select count(*) from drop_me_tab;
select count(*) from drop_me_tab
                     *
ERROR at line 1:
ORA-00376: file 32 cannot be read at this time
ORA-01110: data file 32: 'D:\ORACLE\ORADATA\ACME\DROP_ME02.DBF'

[email protected]D> select * from drop_me_tab where rownum < 20;

         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

19 rows selected.

[email protected]D> insert into drop_me_tab values(20);

1 row created.

[email protected]D> commit;

Commit complete.

[email protected]D>