Oracle Error : ORA-23515 , while droping the table space

[Former Member]

Hi All,

When I am droping the table sapce then getting the following error and not able to drop the table space :

Oracle Error : ORA-23515: materialized views and/or their indices exist in the tablespace

Could you please let me know that how can I check the which materialized views those are existing in that table space so that I can delete those before deleting the table space.

Thanks in advance.

Harish

[Former Member]

Do a search on ORA-23515, it's fairly common.

[Former Member]

Hi Scott,

Thanks for the reply. I searched that error but could not get any link that like how to check the meterialized view those are there in the table sapce.

Thanks,

Harish

[former_member204746]

I have not tested the following command. run it at your own risks:

dbms_snapshot.get_mv_dependencies(

list IN VARCHAR2,

deplist OUT VARCHAR2);

[Former Member]

I don't use materialized views in my installation -so I couldn't test , but I would recommend querying DBA_SEGMENTS

select distinct(sement_name)

from dba_segments

where tablespace_name='YOUR_TS_NAME_HERE'

that should point out what is still in that tablespace

You could always force the issue with the "including contents and datafiles" drop tablespace option - use that at your own risk though.