Having your materialized view refresh take hours can be a substantial problem, especially if the refresh needs to be done frequently or if it blocks other operations. To address this issue, you'll want to look at optimizing the refresh process. Here are some strategies you might consider:

1. **Indexing**:

   - Ensure that the underlying tables are properly indexed.

   - Consider creating indexes on the materialized view itself to speed up refresh operations.

  CREATE INDEX index_name ON materialized_view_name(column_name);

2. **Partitioning**:

   - If the underlying tables are large, consider partitioning them to make refresh operations faster.

3. **Incremental Refresh**:

   - If possible, use an incremental refresh strategy rather than a complete refresh. In an incremental refresh, only the changes since the last refresh are applied, which can be much faster.

4. **Refresh Schedule**:

   - Schedule refresh operations during periods of low database usage to minimize the impact on other operations.

5. **Materialized View Logs**:

   - Create materialized view logs on the base tables to track changes, which can help to speed up refresh operations.

  CREATE MATERIALIZED VIEW LOG ON table_name;

6. **Database Statistics**:

   - Regularly gather statistics on the database to help the optimizer choose the best execution plan for the queries.

  EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

7. **SQL Tuning**:

   - Review and possibly tune the query defining the materialized view to make it more efficient.

8. **Hardware and Configuration**:

   - Review your database server's hardware and configuration to ensure that it is capable of handling the workload.

9. **Parallel Execution**:

   - Depending on your Oracle version and setup, you might be able to use parallel execution to speed up refresh operations.

10. **Monitoring and Tracing**:

    - Utilize Oracle's monitoring and tracing tools to identify bottlenecks and understand what is taking most of the time during the refresh.

- **Creating a Materialized View with Fast Refresh**:

  ```sql

  CREATE MATERIALIZED VIEW materialized_view_name

  BUILD IMMEDIATE

  REFRESH FAST

  AS

  SELECT column_name FROM table_name;

  ```

Make sure to replace `'SCHEMA_NAME'`, `'index_name'`, `'materialized_view_name'`, `'column_name'`, and `'table_name'` with your specific schema, index, materialized view, column, and table names.