sql

Syntax

Purpose

To execute a SQL statement from within Recovery Manager. For valid SQL syntax, see the Oracle8i SQL Reference.

Requirements

• 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 parameter must be enclosed in double quotes. For example, use the following syntax:

       sql "CREATE TABLESPACE temp1 DATAFILE ' '$ORACLE_HOME/dbs/temp1.f' ' "
  
  

  If you attempt to use single quotes for the string following the SQL parameter or single quotes for the filename, then the command fails.

• You cannot execute SELECT statements.

Keywords and Parameters

'command'

specifies a SQL statement for execution. For example, issuing the following: sql 'ALTER SYSTEM ARCHIVE LOG ALL'; at the RMAN prompt archives the online redo logs.

Examples

Making an Operating System Copy of an Online Tablespace

This example hosts out to the operating system to make an operating system copy of online tablespace TBS_1 and then catalogs it:

sql 'ALTER TABLESPACE tbs_1 BEGIN BACKUP';
host 'cp $ORACLE_HOME/dbs/tbs_1.f/dbs/tbs_1.f $ORACLE_HOME/copy/temp3.f';
sql 'ALTER TABLESPACE tbs_1 END BACKUP';
catalog datafilecopy '$ORACLE_HOME/copy/temp3.f';
sql 'ALTER SYSTEM ARCHIVE LOG ALL';

Specifying a Filename within a Quoted String

This example specifies a filename 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;"

Opening with the RESETLOGS Option

This example performs incomplete recovery and opens the database with the RESETLOGS option:

run {
     set until scn 1000;
     allocate channel c1 type 'sbt_tape';
     restore database;
     recover database;
     sql 'ALTER DATABASE OPEN RESETLOGS';
     reset database;
}