LLI Wiki — My Digital Knowledge Base

Running SQL commands from the RMAN prompt

How to run SQL statements and PL/SQL blocks from RMAN, including quoting rules and the fact that no result is returned.

RMAN can run SQL statements directly from its own prompt. This is useful to switch a log file, archive the online redo logs or take a tablespace offline or online without opening a separate SQL*Plus session.

Syntax

Write SQL followed by the statement in single quotes, then a semicolon:

RMAN> SQL 'alter system archive log current';

sql statement: alter system archive log current

An invalid statement returns an error.

Queries

A SELECT statement runs, but its result is not displayed at the RMAN prompt:

RMAN> SQL 'select * from v$database';

sql statement: select * from v$database

Use SQL*Plus to see query results.

Statements that contain single quotes

Enclose the whole statement in double quotes and keep the inner literals in single quotes:

RMAN> SQL "create tablespace demo
datafile 'C:\database\datafile\test01.dbf'
size 10m";

PL/SQL blocks and RUN blocks

PL/SQL blocks use the same form:

RMAN> SQL 'begin ... ; ... ; end;';

The same SQL commands can also be used inside a RUN block:

RMAN> RUN {
  SQL 'alter system archive log current';
}

Notes

Mastodon LLIMastodon SocialFosstodon