RMAN 12c – More SQL without ‘sql’

Up until version 12c, the RMAN command line parser was capable of parsing certain DML and DDL statements.  To do this, we use the “sql” command from the RMAN command line, like this.

11gHost> rman

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Aug 7 15:29:19 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target

connected to target database: TEST2 (DBID=1111111111)

RMAN> sql 'create table altdotoracle (col1 number)';

sql statement: create table altdotoracle (col1 number)

RMAN> sql 'insert into altdotoracle values (1)';

sql statement: insert into altdotoracle values (1)

However, one operation we could not do was SELECT.  The RMAN parser lacked the facility to return arrays of data to the screen.  This shouldn’t be surprising, considering that RMAN is intended to be used to run backup and restore operations.

RMAN> sql 'select * from altdotoracle';

sql statement: select * from altdotoracle

The statement does not error, but no data is returned.  Now, in version 12c, we get two new benefits in this area.  First, we no longer need to use the ‘sql’ command.  For example…

12cHost> rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Aug 7 15:35:27 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target

connected to target database: TEST1 (DBID=1212121212)

RMAN> create table altdotoracle (col1 number);

Statement processed

RMAN> insert into altdotoracle values (1);

Statement processed

Additionally, the RMAN parser in 12c allows us to run SELECT commands, as well as DESCRIBE commands.

RMAN> select * from altdotoracle;

      COL1

----------

         1

RMAN> describe altdotoracle

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 COL1                                               NUMBER

The ability to use the ‘sql’ RMAN command is still supported for backward compatibility.  A simple thing, but useful!