Toad World® Forums

Getting ORA-01652 when creating materialized view (MV)

The query fot he MV is based 6 tables ranging from sizes of 54Millions to 3 Millions. The explain plan shows that it’s using hash joins to create the MV. How can I create this MV without using up the 128G of temp space available?

is based 6 tables ranging from sizes of 54Millions to 3 Millions

How can I create this MV without using up the 128G of temp space

It’s hard to offer advice when one has so little information.

Perhaps subsets of data will suit your purposes. If there’s a logical
separation you can make in the data, then running the query multiple times to
grab each set of data will help alleviate the space issues.

Using the employee/manager structure as an example. Let’s say
there’s 6 managers. Running the query 6 times to move (I’m assuming
that’s what MV represents) all the data could be a useful strategy.

Roger S.