Monitoring

 

Monitoring Script

Below is a PL/SQL script to monitor the percentage completion of tablespace creation by comparing the current datafile size to the target size in tablespace_control. It also includes job status and log details for a comprehensive view.

SET SERVEROUTPUT ON; DECLARE v_total_size_gb NUMBER; v_current_size_bytes NUMBER; v_percentage NUMBER; v_datafile_name VARCHAR2(512); BEGIN DBMS_OUTPUT.ENABLE(1000000); -- Print header DBMS_OUTPUT.PUT_LINE('Tablespace Creation Progress Report at ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE(RPAD('Tablespace Name', 30) || RPAD('Batch ID', 10) || RPAD('Status', 12) || RPAD('Target Size (GB)', 18) || RPAD('Current Size (GB)', 18) || RPAD('Percentage', 12) || 'Job State'); DBMS_OUTPUT.PUT_LINE(RPAD('-', 110, '-')); -- Loop through tablespace_control to check progress FOR rec IN ( SELECT tc.tablespace_name, tc.batch_id, tc.status, tc.initial_size_gb, NVL((SELECT dj.state FROM DBA_SCHEDULER_JOBS dj WHERE UPPER(dj.job_name) = UPPER(USER) || '.CREATE_TS_BATCH_' || tc.batch_id), 'NO JOB') AS job_state FROM tablespace_control tc ORDER BY tc.batch_id ) LOOP -- Get datafile size for the tablespace BEGIN SELECT NVL(SUM(bytes) / 1024 / 1024 / 1024, 0), NVL(MAX(file_name), 'NOT CREATED') INTO v_current_size_bytes, v_datafile_name FROM DBA_DATA_FILES WHERE UPPER(tablespace_name) = UPPER(rec.tablespace_name); v_total_size_gb := rec.initial_size_gb; IF v_current_size_bytes > 0 THEN v_percentage := ROUND((v_current_size_bytes / v_total_size_gb) * 100, 2); ELSE v_percentage := 0; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_current_size_bytes := 0; v_percentage := 0; v_datafile_name := 'NOT CREATED'; END; -- Output progress for each tablespace DBMS_OUTPUT.PUT_LINE( RPAD(rec.tablespace_name, 30) || RPAD(rec.batch_id, 10) || RPAD(rec.status, 12) || RPAD(TO_CHAR(rec.initial_size_gb, '999,999'), 18) || RPAD(TO_CHAR(v_current_size_bytes, '999,999.99'), 18) || RPAD(TO_CHAR(v_percentage, '999.99') || '%', 12) || rec.job_state ); END LOOP; -- Print recent log entries for additional context DBMS_OUTPUT.PUT_LINE(RPAD('-', 110, '-')); DBMS_OUTPUT.PUT_LINE('Recent Log Entries (Last 10):'); DBMS_OUTPUT.PUT_LINE(RPAD('Tablespace Name', 30) || RPAD('Batch ID', 10) || RPAD('Action', 15) || RPAD('Status', 12) || RPAD('Error Message', 30) || 'Log Time'); DBMS_OUTPUT.PUT_LINE(RPAD('-', 110, '-')); FOR log_rec IN ( SELECT tablespace_name, batch_id, action, status, NVL(error_message, 'N/A') AS error_message, log_time FROM tablespace_log WHERE log_time > SYSDATE - 1 -- Last 24 hours ORDER BY log_time DESC FETCH FIRST 10 ROWS ONLY ) LOOP DBMS_OUTPUT.PUT_LINE( RPAD(log_rec.tablespace_name, 30) || RPAD(log_rec.batch_id, 10) || RPAD(log_rec.action, 15) || RPAD(log_rec.status, 12) || RPAD(SUBSTR(log_rec.error_message, 1, 27), 30) || TO_CHAR(log_rec.log_time, 'YYYY-MM-DD HH24:MI:SS') ); END LOOP; END; /

Additional Monitoring Techniques

1. V$SESSION_LONGOPS:
   • Some tablespace creation operations may appear in V$SESSION_LONGOPS if they are long-running.
   • Query to check:
     sql

     SELECT sid, serial#, opname, target, sofar, totalwork,

     ROUND(sofar / totalwork * 100, 2) AS percent_complete,

     time_remaining, elapsed_seconds

     FROM V$SESSION_LONGOPS

     WHERE opname LIKE 'Tablespace%'

     AND totalwork > 0

     ORDER BY start_time;
   • Limitation: CREATE TABLESPACE may not always register in V$SESSION_LONGOPS, especially for bigfile tablespaces, as it’s primarily a file allocation operation.
2. OS-Level File Monitoring:
   • Identify the datafile path from DBA_DATA_FILES:
     sql

     SELECT tablespace_name, file_name

     FROM DBA_DATA_FILES

     WHERE tablespace_name IN (SELECT tablespace_name FROM tablespace_control);
   • On the OS, monitor the file size (e.g., using ls -lh on Linux or equivalent on Windows) to see growth in real-time.
   • Example (Linux):
     bash

     watch ls -lh /path/to/datafile.dbf
   • Correlate the file size with initial_size_gb to estimate progress manually.
3. Database Alerts:
   • Check the database alert log for messages related to tablespace creation:
     sql

     SELECT value FROM V$PARAMETER WHERE name = 'background_dump_dest';
   • Look for entries in the alert log file (e.g., /u01/app/oracle/diag/rdbms/dbname/trace/alert_SID.log) indicating tablespace creation start or completion.
4. Job Run Details:
   • Query DBA_SCHEDULER_JOB_RUN_DETAILS for detailed job execution history:
     sql

     SELECT job_name, status, error#, additional_info,

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

     TO_CHAR(run_duration, 'HH24:MI:SS') AS duration

     FROM DBA_SCHEDULER_JOB_RUN_DETAILS

     WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_BATCH_%'

     ORDER BY actual_start_date DESC;
   • This provides insights into job success, failures, and duration but won’t show percentage completion.

Considerations for Large Tablespaces

• Bigfile Tablespaces: Since your script uses bigfile tablespaces, each tablespace has one datafile, simplifying size monitoring (only one file to check per tablespace).
• Performance Impact: Creating 6268 GB tablespaces is I/O-intensive. Monitor system resources (CPU, disk I/O) using tools like iostat, sar, or Oracle’s V$SYSSTAT:
  sql

  SELECT name, value

  FROM V$SYSSTAT

  WHERE name LIKE 'physical write%';
• Frequency of Monitoring: Run the monitoring script periodically (e.g., every 5–10 minutes) to track progress, as large tablespaces take time to allocate.
• Job Queue Limits: Ensure JOB_QUEUE_PROCESSES supports parallel job execution:
  sql

  SHOW PARAMETER job_queue_processes;

  ALTER SYSTEM SET job_queue_processes = 10; -- Adjust as needed
• Error Handling: The main script logs errors in tablespace_log. If a tablespace creation fails (e.g., due to insufficient disk space), check tablespace_log for details:
  sql

  SELECT tablespace_name, batch_id, action, status, error_message, log_time

  FROM tablespace_log

  WHERE status = 'FAILED'

  ORDER BY log_time DESC;

Recommendations

• Run the Monitoring Script: Execute the monitoring script above periodically to get a real-time view of datafile growth and job status.
• Automate Monitoring: Schedule the monitoring script as a separate DBMS_SCHEDULER job to run every few minutes:
  plsql

  BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

  job_name => UPPER(USER) || '.MONITOR_TS_PROGRESS',

  job_type => 'PLSQL_BLOCK',

  job_action => '/* Paste the monitoring script here */',

  start_date => SYSTIMESTAMP,

  repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', -- Run every 5 minutes

  enabled => TRUE,

  auto_drop => FALSE,

  comments => 'Monitor tablespace creation progress'

  );

  END;

  /
• Alert on Failures: Add an alert mechanism (e.g., email or log entry) if a job fails or a tablespace creation stalls (e.g., percentage doesn’t increase over time).
• Disk Space: Ensure sufficient disk space for all tablespaces (e.g., 6268 GB × number of tablespaces). Check:
  sql

  SELECT file_name, bytes / 1024 / 1024 / 1024 AS size_gb

  FROM DBA_DATA_FILES

  WHERE tablespace_name IN (SELECT tablespace_name FROM tablespace_control);

Conclusion

While Oracle doesn’t provide a direct percentage completion for CREATE BIGFILE TABLESPACE, you can estimate progress by monitoring datafile growth in DBA_DATA_FILES relative to initial_size_gb in tablespace_control. The provided monitoring script combines this with job status and log entries for a comprehensive view. Run it manually or schedule it to track the creation of your large tablespaces (e.g., batch_ids 6–8) without interfering with existing jobs (1–5).