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
- Statements that return rows are executed, but the rows are not echoed by RMAN.
- The tablespace name and file path in the example are placeholders.