---

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 O/S Copy of an Online Tablespace

This example hosts out to the operating system to make an O/S 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;
}

---