I want to write a select SQL statement but I want it to run on all databases being monitored by Oracle Enterprise Manager.
I also would like to know how to run 'alter statements' on all database being monitored by the OEM. For eg. I would like to block/remove all users that have left the company from the database.
First you need to set the preferred credentials for the targets. After that, you can use emcli for this. In the below example I have a group called sandbox_databases containing 3 databases:
$ emcli login -username=sysman
$ emcli execute_sql -sql="select count(*) from dba_users" -targets="sandbox_databases:group"
And the output is:
*******************************************************************************
* Target: XXXXX1:oracle_database
* Execution Status: Succeeded
*******************************************************************************
COUNT(*)
----------
9
*******************************************************************************
* Target: XXXXX2:oracle_database
* Execution Status: Succeeded
*******************************************************************************
COUNT(*)
----------
12
*******************************************************************************
* Target: XXXXX3:oracle_database
* Execution Status: Succeeded
*******************************************************************************
COUNT(*)
----------
10
*******************************************************************************
* Execution Summary
* Targets Succeeded: 3
* XXXXX1:oracle_database,XXXXX2:oracle_database,XXXXX3:oracle_database
* Targets Failed: 0
*******************************************************************************
ALTER commands also work, for example:
$ emcli execute_sql -sql="alter user bp account lock" -targets="another_group:group"
*******************************************************************************
* Target: XXXXX4:rac_database
* Execution Status: Succeeded
*******************************************************************************
PL/SQL procedure successfully completed.
User altered.
*******************************************************************************
* Execution Summary
* Targets Succeeded: 1
* XXXXX4:rac_database
* Targets Failed: 0
*******************************************************************************
Depending on your version of OEM (we have 13.5 RU 3), you can use a job to execute a SQL Script against any target database you tell it to.
Go to Enterprise -> Job -> Activity
Click the Create Job button
Choose "SQL Script" under Job Type and click select
On the General tab, click Add and choose all the target DBs you want to hit
On the Parameters tab, put your script or SQL query in the SQL Script box
Fill out everything else as appropriate for your environment
Click submit to run the job now, or Save to Library to save it.
To view the results, go to Enterprise -> Job -> Activity Find your job run and click on it. On the left you'll see each target it was run against. Click the target you want to view, then on the bottom right, it will display the query results as returned by sqlplus.
I have not confirmed, but I suspect as long as your Job has the correct credentials set, you could run anything (including alter statements) that would work at a sqlplus prompt.
This documentation may be helpful: https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emadm/utilizing-job-system-and-corrective-actions.html#GUID-D88CB8E0-D2ED-44A7-83F4-DED22B1F3308
