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.

### 1. Check Session Activity

You can check the session activity to see if the refresh session is actively doing something. Run a query like the following:

SELECT sid, serial#, context, sofar, totalwork,

       ROUND((sofar/totalwork)*100,2) "%_COMPLETE"

FROM v$session_longops

WHERE opname LIKE 'REFRESH MATERIALIZED VIEW%'

AND totalwork != sofar;

This query will return details on the sessions that are currently refreshing materialized views, including an estimate of how much of the work has been completed.

### 2. Check DBA_MVIEW_REFRESH_TIMES

You can query `DBA_MVIEW_REFRESH_TIMES` to check the last refresh time for the materialized view. If it's updating, that means your refresh is working.

SELECT *

FROM dba_mview_refresh_times

WHERE name = 'YOUR_MATERIALIZED_VIEW_NAME';

### 3. Check DBA_JOBS_RUNNING

If your refresh is being handled by a job, you can check the `DBA_JOBS_RUNNING` view to see if the job is currently running:

SELECT *

FROM dba_jobs_running

WHERE job_name = 'YOUR_JOB_NAME';

Replace `'YOUR_JOB_NAME'` with the actual name of your job.

### 4. Monitor Alert Logs and Trace Files

Sometimes the session might encounter errors during the refresh, and these errors will be recorded in the alert logs and trace files. You can monitor these logs for any errors or issues.

### 5. Utilize OEM (Oracle Enterprise Manager)

If you have Oracle Enterprise Manager configured, you can use it to monitor the performance and status of materialized view refreshes in a more user-friendly manner compared to querying system views.

### Note:

- Always replace `'YOUR_MATERIALIZED_VIEW_NAME'` and `'YOUR_JOB_NAME'` with the actual names in your environment.

- Depending on your permissions, you might need to use `ALL_` views (like `ALL_MVIEW_REFRESH_TIMES`) instead of `DBA_` views.

- You might not see short-lived operations in `V$SESSION_LONGOPS` because they finish too quickly to be captured in this view. Only operations that run for at least 6 seconds appear here.

- Ensure to have necessary privileges to access these views.

Please give it a try and check if you are able to see the progress of your materialized view refresh.