execute_sql

Executes a SQL command across a set of targets.

Format

emcli execute_sql
      -sql=<sql_command>
      -targets=<name1:type1;name2:type2;...>
      -credential_set_name=<name>
      [-input_file=<parameter_tag:script_file>]

[ ]  indicates that the parameter is optional

Options

  • sql

    "sql command" is a single SQL statement.

  • targets

    List of target-name, target-type pairs. The SQL command executes across this list of Enterprise Manager targets. All targets must be of the type oracle_database or composite, which represents a group of targets. If it is a group, the group expands to extract all the database targets, and the SQL command is executed across these database targets.

  • credential_set_name

    Refers to the set name of the preferred credentials stored in the Enterprise Manager repository. If this option is not present, the DBCredsNormal and DBHostCreds credential set is used for executing SQL commands. For each target type, several credential sets exist:

    • HostCredsNormal — Default unprivileged credential set for a host target

    • HostCredsPriv — Privileged credential set for a host target

    • DBHostCreds — Host credential set for an oracle_database target

    • DBCredsNormal — Default normal credential set for an oracle_database target

    • DBCredsSYSDBA — sysdba credential set for an oracle_database target

    You can only specify the credential_set_name parameter when the override credential parameters such as [db_|host_]username and [db_|host_]password are not present. If provided, the override credential parameters must be specified fully. For the SQL commands, db_username, db_password, db_role, host_username, and host_password must be present.

  • input_file

    Used in conjunction with the -sql option, this option enables you to load the contents of a SQL script. The -input_file option specifies a mapping between a tag and a local file path. The tag is specified in lieu of an actual SQL command for the -sql . The tag must not contain colons ( : ) or semi-colons ( ; ).

    For information about the input_file parameter, see -input_file Syntax Guidelines.

Examples

Example 1

This example executes the SQL command select * from sysman.mgmt_targets; against the target database:oracle_database and database targets contained in the group grp. The stored SYSDBA preferred credentials are used for all the targets. 

emcli execute_sql
      -sql="select * from sysman.mgmt_targets;"
      -credential_set_name="DBCredsSYSDBA"
      -targets="database:oracle_database;grp:composite"

Example 2

This example loads the contents of the script /scratch/dba_scripts/enterprise_schema.sql into the value of -sql, and executes it against target database:oracle_database and database targets contained in the group grp. The stored SYSDBA preferred credentials are used for all the targets. 

emcli execute_sql
      -sql="FILE"
      -input_file="FILE:/scratch/dba_scripts/enterprise_schema.sql"
      -credential_set_name="DBCredsSYSDBA"
      -targets="database:oracle_database;grp:composite"