RMAN Commands , 54 of 59

SQL

Syntax

Text description of the illustration sql.gif

Purpose

To execute a SQL statement or a PL/SQL stored procedure from within Recovery Manager.

Restrictions and Usage Notes

Execute the SQL command at the RMAN prompt or within the braces of a RUN command.
If the string that RMAN passes to PL/SQL contains a filename, then the filename must be enclosed in duplicate single quotes and the entire string following the SQL keyword must be enclosed in double quotes. For example, use the following syntax:
```
     SQL "CREATE TABLESPACE temp1 DATAFILE ''?/oradata/trgt/temp1.dbf'' 
          SIZE 10M TEMPORARY";
```
If you attempt to use single quotes for the string following the SQL keyword or use only one set of single quotes for the filename, then the command fails.
You cannot execute SELECT statements.

See Also:
For valid SQL syntax, see the Oracle9i SQL Reference

Keywords and Parameters

Syntax Element Description

Syntax Element	Description
`'``command``'`	Specifies a SQL statement for execution. For example, issue the following at the RMAN prompt to archive the online redo logs: SQL 'ALTER SYSTEM ARCHIVE LOG ALL'; Because `EXECUTE` is a SQL*Plus command, you cannot execute a PL/SQL command by specifying `EXECUTE` within the RMAN `SQL` command. Instead, you must use the `BEGIN` and `END` keywords. For example, to execute a PL/SQL procedure named `rman.rman_purge` through the RMAN `SQL` command, issue the following: SQL 'BEGIN rman.rman_purge; END;';

'command'

Specifies a SQL statement for execution. For example, issue the following at the RMAN prompt to archive the online redo logs:

SQL 'ALTER SYSTEM ARCHIVE LOG ALL';

Because EXECUTE is a SQL*Plus command, you cannot execute a PL/SQL command by specifying EXECUTE within the RMAN SQL command. Instead, you must use the BEGIN and END keywords. For example, to execute a PL/SQL procedure named rman.rman_purge through the RMAN SQL command, issue the following:

SQL 'BEGIN rman.rman_purge; END;';

Examples

Archiving the Unarchived Online Logs: Example

This example backs up a tablespace and then archives all unarchived online logs:

BACKUP TABLESPACE users;
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

Specifying a Filename within a Quoted String: Example

This example specifies a filename by using duplicate single quotes within the context of a double-quoted string:

SQL "ALTER TABLESPACE tbs_1 ADD DATAFILE ''/oracle/dbs/tbs_7.f'' NEXT 10K MAXSIZE 100k;"

Executing a PL/SQL Stored Procedure Within RMAN: Example

This example issues a PL/SQL stored procedure called scott.update_log:

RUN
{
  SQL ' BEGIN scott.update_log; END; ';
}