Oracle 12c New Features - Expert Consolidation in Oracle Database 12c - page 49

10 end loop;
11* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
At the same time the DBA wants to move the August partition from fast storage to slow storage. Here is the
outcome of that exercise in up to and including 11.2. It is also the default for 12c:
SQL> alter table part_demo move partition p_may_2013 tablespace tbs_arch_01;
alter table part_demo move partition p_may_2013 tablespace tbs_arch_01
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
The new syntax to perform the same operation online is shown here:
SQL> alter table part_demo move partition P_MAY_2013 tablespace tbs_arch_01 online;
Table altered.
The operation takes a little longer but it is an online operation! Only when all the locks on the partition are
removed will the operation complete.
Moving Data Files While They Are Accessed
Moving data files from one location to another has most often been an operation that required downtime or otherwise
impacted the availability of your application. Moving a database from one file system to another—perhaps during
a SAN replacement—commonly required the database administrator to shut the database down, using operating
system utilities to copy the data files to the new location, start the database into mount mode followed by a call to
alter tablespace rename datafile ....
This task has now been greatly simplified by the introduction of the alter database move datafile command.
Using the new command it is very simple to move data and temp files (but not online redo log files) from a file
system into ASM while they are in use. In this context it is possible to move an Oracle Managed File (OMF) to another
location managed as an Oracle Managed File, allowing you to move OMF files from a file system into ASM. Oracle will
briefly create a copy of the data file so ensure that there is enough space for the copy and the original file.
After the move is completed, the old file will automatically be removed unless you specify the keep keyword. If
your old file was not an Oracle Managed file you can specify that you want to keep the old file. Here is an example for
moving a data file into ASM which has accidentally been created on the file system for tablespace tbs_arch_01:
SQL> select file_name from dba_data_files
2 where tablespace_name = 'TBS_ARCH_01';
FILE_NAME
----------------------------------------------------------------------------
/u01/oradata/cdb1/tbs_arch_01.dbf