ALTER SESSION SET CURRENT_SCHEMA = <schema name>;
The approach detailed below uses RMAN image copy to migrate a copy of the database to ASM – then uses RMAN “switch” to start using the ASM based copy as the live database.
Note: This requires about 2X the space of the original database. Also note done on 12c where it is no longer required to prefix SQL ‘startup mount’ – etc. sql commands – or switch back and forth between sqlplus and rman when issuing sql and rman commands.
This also assumes you have installed an ASM instance – and provisioned data groups like +DATA for data and +FRA for Fast Recovery Area
Here is the process:
make a backup of your spfile – create pfile from spfile maybe
alter your control_file parameter in the spfile/pfile to point at new storage (e.g. +DATA)
rman target /
backup as copy database format ‘+DATA’;
restore controlfile from ‘original location’;
alter database mount;
switch database to copy;
alter database open;
— logically now – move your redo to the new storage by creating new in +DATA and dropping the old file based – can be done while the DB is open – carefully
— you will also need to change your FRA to ASM and then take a full backup