2

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.

7

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
*******************************************************************************
1
  • I need further explanation regarding the group and preferred credentials part. I have created a group and added preferred credentials to couple of databases and I tried running simple sql statement using emcli and I get a message :Preferred credentials don't exist for some targets. PS. I removed all targets from the group and added only one database and made sure to login to it using preferred credentials. @Balaz Papp – Mohamed Raafat Dec 22 '16 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.