Forum Stats

  • 3,769,804 Users
  • 2,253,023 Discussions
  • 7,875,210 Comments

Discussions

OEM Corrective Action - Metric Extension Association with Incident Manager Notification

Here is my requirement

I have a working PL/SQL block (with hard coded tablespace name) which would pick the tablespace and create a datafile in that tablespace when checked manually for space utilization.

However, when the same incident is raised from OEM how can i extract the tablespace name for which the alert has been raised and pass the tablespace name to PL/SQL block to perform the corrective actions.

All that i need to know is how to pass the tablespace name for which the incident has been raised to the PL/SQL block which is used in metric extension?

When executing below code from database, it works, since the tablespace name is hardcoded.

DECLARE

 v_stmt    VARCHAR2(100);

 v_tbsusedgb number;

 v_tablespace VARCHAR2(100) := 'TEST' ; <<<<<<<<<<<<<<< TBS Name Hardcoded.

BEGIN  

 SELECT ceil(sum(bytes/1024/1024)) INTO v_tbsusedgb from dba_data_files where tablespace_name=v_tablespace;

  dbms_output.put_line('Size of the tablespace is: ' || v_tbsusedgb );

IF ( v_tbsusedgb < 100 ) THEN

v_stmt := 'ALTER TABLESPACE ' || v_tablespace || ' ADD DATAFILE SIZE 10m AUTOEXTEND ON NEXT 10m MAXSIZE 50M; ';

EXECUTE IMMEDIATE v_stmt;

dbms_output.put_line('Successfully added datafile to: ' || v_tablespace );

ELSE

v_stmt := 'ALTER TABLESPACE ' || v_tablespace || ' ADD DATAFILE SIZE 10m AUTOEXTEND ON NEXT 10m MAXSIZE 100M; ';

dbms_output.put_line('Alter STMT Is: ' || v_stmt );

dbms_output.put_line('NOT ADDING TBS to: ' || v_tablespace );

  END IF;

END;

/


Want to execute the same piece of code using Metric Extension which should grab the tablepace name from the incident notification alert. Tried below option but didn't help.


v_tablespace := '%key_value_name%' ;

Answers