We ran into this error: “ORA-00959 tablespace ‘_ deleted$5$0′ does not exist” when we tried to create a materialized view with the tablespace clause specified:
CREATE MATERIALIZED VIEW TABLESPACE
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
START WITH TO_DATE(’30-04-2009 01:00:00′, ‘DD-MM-YYYY HH24:MI:SS’) NEXT TRUNC(SYSDATE)+1/24+1
ENABLE QUERY REWRITE
AS
SELECT CUSTOMER_GUID, SUM(SUBTOTAL_AMT) TOTAL_AMT
FROM .
@
GROUP BY CUSTOMER_GUID
It turned out that the default tablespace for the user which tried to create the materialized view has been set to “_$deleted$5$0″ – a non-existing tablespace. Not sure what caused the default tablespace change.
After the default tablespace for the user has been changed, the materialized view can be created successfully.
