TROUBLESHOOTING

--SIMPLE

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,

       q.sql_text

FROM   v$session s

JOIN   v$sql q ON s.sql_id = q.sql_id

WHERE  s.type = 'USER'

AND    s.status = 'ACTIVE';

--FULLTEXT

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,

       q.sql_text, LISTAGG(SQL_TEXT,'') WITHIN GROUP (ORDER BY PIECE) AS FULL_SQL_TEXT

FROM   v$session s

JOIN   v$sqltext_with_newlines q ON s.sql_id = q.sql_id

WHERE  s.type = 'USER'

AND    s.status = 'ACTIVE'

GROUP BYs.sid, s.serial#, s.username, s.osuser, s.machine, s.program

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;

DECLARE

  lat  INTEGER;

  iops INTEGER;

  mbps INTEGER;

BEGIN

  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(2, 10, iops, mbps, lat);

  DBMS_OUTPUT.PUT_LINE('Max IOPS: ' || iops);

  DBMS_OUTPUT.PUT_LINE('Max MBPS: ' || mbps);

  DBMS_OUTPUT.PUT_LINE('Latency: ' || lat);

END;

SELECT

    s.sid,

    s.serial#,

    s.username,

    s.program,

    l1.id1,

    l1.id2,

    l1.lmode,

    l1.request,

    l1.type,

    s.blocking_session,

    s.event,

    s.seconds_in_wait

FROM

    v$session s

JOIN

    v$lock l1 ON s.sid = l1.sid

WHERE

    s.blocking_session IS NOT NULL

OR

    l1.request > 0;

EXEC DBMS_STATS.GATHER_TABLE_STATS(

    ownname          => 'SCHEMA_NAME',

    tabname          => 'TABLE_NAME',

  partname         => 'PARTITON_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    cascade          => TRUE,

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'

);

-- Fetch details from the alert log

SELECT 'Alert Log' AS log_source, 

       message_text AS details, 

       TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS timestamp

FROM   v$diag_alert_ext 

WHERE  originating_timestamp >= SYSDATE - 7

UNION ALL

-- Fetch recent wait events

SELECT 'Wait Events' AS log_source, 

       event_name AS details, 

       TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS timestamp

FROM   v$active_session_history 

WHERE  timestamp >= SYSDATE - 7

UNION ALL

-- Fetch recent session information

SELECT 'Sessions' AS log_source, 

       username || ' - ' || osuser || ' - ' || machine AS details, 

       TO_CHAR(logon_time, 'YYYY-MM-DD HH24:MI:SS') AS timestamp

FROM   v$session 

WHERE  logon_time >= SYSDATE - 7

UNION ALL

-- Fetch recent SQL execution details

SELECT 'SQL Executions' AS log_source, 

       sql_text AS details, 

       TO_CHAR(first_load_time, 'YYYY-MM-DD HH24:MI:SS') AS timestamp

FROM   v$sql 

WHERE  first_load_time >= TO_CHAR(SYSDATE - 7, 'YYYY-MM-DD HH24:MI:SS');

Encountering long-running script issues while attempting to perform a seemingly simple task such as dropping a materialized view can be frustrating. Here are a few possible reasons why this is happening, along with potential solutions:

### **Possible Reasons**

1. **Locked Objects**: Other sessions may be locking the objects associated with the materialized view, causing the DROP operation to wait.

2. **High System Load**: The database server might be experiencing a high system load, which is delaying the processing of your command.

3. **Resource Contention**: There could be resource contention in the database, with many processes competing for CPU, memory, or I/O resources.

4. **Stale Statistics**: The database optimizer may have stale statistics, which can sometimes slow down operations.

### **Potential Solutions**

1. **Check for Locks**

    Before attempting to drop the materialized view, check for any locks on the materialized view

   SELECT a.session_id, a.lock_type, a.mode_held, b.object_name

   FROM dba_locks a

   JOIN dba_objects b ON a.locked_object_id = b.object_id

   WHERE b.object_name = 'YOUR_MVIEW_NAME';

  If there are locks, you might need to identify the sessions holding the locks and terminate them appropriately.

2. **Check System Load** 

   Check the current system load using database views like `v$sysmetric` and `v$system_event`.

3. **Check Resource Utilization**

      Use scripts to check the resource utilization (like CPU, memory) on your database server.

4. **Gather Fresh Statistics**

      Before dropping the materialized view, gather fresh statistics using the following command:

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('YOUR_SCHEMA_NAME');

5. **Session Tracing**

      Enable session tracing to find out what is happening during the execution of the drop command:

   EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE;

   After running the `DROP MATERIALIZED VIEW` command, disable tracing:

   EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;

6. **Restart the Database**

      As a last resort, if you find no issues with locks or resources, consider restarting the Oracle database to clear any potential issues.

7. **Consult the Database Logs**

      Consult the database logs for any errors or warnings that might provide clues as to why the operation is taking a long time.

### **Note**

- Replace `'YOUR_MVIEW_NAME'` and `'YOUR_SCHEMA_NAME'` with your materialized view name and schema name respectively.

- Ensure you have the necessary privileges to run these commands and consult the database logs.

- Always exercise caution when terminating sessions, as this can cause transactions to be rolled back and can potentially lead to data corruption.

I recommend starting with the first solution and working your way down the list until you find a solution that works.

The PGA_AGGREGATE_TARGET parameter specifies the target aggregate PGA memory available to all server processes attached to the instance. To check its value, you can use the following SQL query:

SELECT name, value

FROM v$parameter

WHERE name = 'pga_aggregate_target';

This query returns the current setting of the PGA_AGGREGATE_TARGET parameter. Oracle automatically adjusts the sizes of the individual PGA memory areas to meet this target as closely as possible.

Temporary tablespaces in Oracle are used for storing data temporarily required during SQL operations, such as sorting large datasets or for hash joins that exceed available PGA memory. To check the configuration of temporary tablespaces, you can query the DBA_TEMP_FILES and DBA_TABLESPACES views:

SELECT dt.tablespace_name, dt.contents, dt.status, dt.extent_management, dt.allocation_type,

       SUM(dtf.bytes) / 1024 / 1024 AS temp_size_MB

FROM dba_temp_files dtf

JOIN dba_tablespaces dt ON dtf.tablespace_name = dt.tablespace_name

WHERE dt.contents = 'TEMPORARY'

GROUP BY dt.tablespace_name, dt.contents, dt.status, dt.extent_management, dt.allocation_type;