CODEWITHSUNDEEP
oracle-databaseoracle9i
Matthew Watson
Matthew Watson

Reputation: 14233

drop tablespace with materialized view and missing datafiles

I have a partial recovered database ( for testing purposes ), with a couple of tablespaces missing due to their size and nature are not needed. So, I need to drop these tablespaces however some of them have materialized views in them, which puts me in a bit of a catch 22.

> drop tablespace test including contents and datafiles;
drop tablespace test including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

So, we need to drop the tablespace first.

drop materialized view test.test_table_mv;
drop materialized view test.test_table_mv
*
ERROR at line 1:
ORA-00376: file 172 cannot be read at this time
ORA-01111: name for data file 172 is unknown - rename to correct file
ORA-01110: data file 172: '/u01/software/9.2.0.5.0/dbs/MISSING00172'

Any idea how to get around this, without actually recovering the datafiles, diskspace and time will not permit that?

Upvotes: 1

Views: 4111

Answers (1)

Matthew Watson
Matthew Watson

Reputation: 14233

one solution i have found is to drop the user owning the mview, this works but is a bit brute force.

Upvotes: 2

Related Questions