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
orcomposite
, 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
andDBHostCreds
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
, andhost_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"