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

'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; ';
}